tags:

views:

35

answers:

2

I have a "manager" class with a number of sub-classes. I find one particular method being duplicated in all or almost all of the sub-classes, so I want to generalize it. In one case, it looks like this:

                        var Results =
                        from j in Job.All( )
                        where guids.Contains( j.JobID )
                        orderby j.JobNumber
                        select j;

I am using SubSonic, so Job.All( ) is a static method that returns an IQueryable<Job>. guids is an IEnumerable<Guid> which holds a list of keys into the Job table. My final result is a SQL query with WHERE Job.JobID IN (x, y, z).

What I'm looking for is a way to call a method like this where I pass in guids, "Job", "JobID" and "JobNumber" so that I can plug them into the appropriate places.

There is a limit to the number of parameters a SQL IN clause can handle, so my general method would also check guids.Count and code a little bit different query when it exceeded a certain number (say, 2000). This code is also in all the sub-classes.

Note: I have no objection to using the lambda-style notation. This is my first cut, which has problems with "T.All( )", and item.???:

    const int MAX_ITEMS = 2000;
    public List<T> GetFromList<T>( List<Guid> _IDs ) 
        where T : class, IActiveRecord
    {
        List<T> rc;
        if ( MAX_ITEMS > _IDs.Count )
        {
            var Results =
                from item in T.All( )
                where _IDs.Contains( item.??? )
                orderby item.???
                select item;
            rc = Results.ToList<T>( );
        }
        else // too many for IN clause
        {
            var Results =
                from id in _IDs
                join item in T.All( ) on id equals item.???
                orderby item.???
                select item;
            rc = Results.ToList<T>( );
        }
        return rc;
    }
+1  A: 
const int MAX_ITEMS = 2000; 
public List<T> GetFromList<T>( List<Guid> _IDs, T _db, 
              Expression<Func<T, Guid>> GetID, 
              Expression<Func<IQueryable<T>> GetAll  )  
    where T : class, IActiveRecord 
{ 
    List<T> rc; 
    if ( MAX_ITEMS > _IDs.Count ) 
    { 
        var Results = 
            from item in GetAll( ) 
            where _IDs.Contains( GetID(item)) 
            orderby GetID(item) 
            select item; 
        rc = Results.ToList<T>( ); 
    } 
    else // too many for IN clause 
    { 
        var Results = 
            from id in _IDs 
            join item in T.All( ) on id equals GetID(item) 
            orderby GetID(item)
            select item; 
        rc = Results.ToList<T>( ); 
    } 
    return rc; 
}

called by:

     GetFromList(ids, db, item=>item.JobID, ()=> Job.All());
James Curran
Thanks, James. I added a missing closing angle bracket just before GetAll in the signature. My compiler (#net 3.5) complains that GetAll and GetID are variables, but are used like methods. I added using System.Linq.Expressions, maybe there is something else I am missing?
Kelly
James Curran
I really appreciate your insight into this one. I did remove the Expression wrappers, and the build was OK. At run-time, I get an exception, "The LINQ expression node of type Invoke is not supported", which is, I suppose, a weakness in SubSonic's implementation.
Kelly
Interestingly, the second query works (after replacing T.All with GetAll), because it actually generates a query to pull in everything from Job and does the join and orderby in memory. Of course, that full read is what I am generally trying to avoid!
Kelly
A: 

My solution was to pass in the expression that Where needed:

 public List<T> GetFromList( List<Guid> _IDs,
    Func<IQueryable<T>> GetAll,
    Expression<Func<T, bool>> _where )
        where T : class, U, IActiveRecord
{
    List<T> rc = new List<T>( );
    if ( 0 < _IDs.Count )
    {
        if ( MAX_ITEMS > _IDs.Count )
        {
            var Results = GetAll( ).Where( _where );
            rc = Results.ToList( );
        }
        else
        {
            var Results =
                from id in _IDs
                join item in GetAll( ) on id equals item.KeyValue( )
                select item;
            rc = Results.ToList( );
        }
    }
    return rc;
}

called by

  rc = GetFromList(
      IDList,
      ( ) => Job.All( ),
      ( item => ( IDList as IEnumerable<Guid> ).Contains( item.JobID ) ) );
Kelly