In LINQ to SQL, how do you pass parts of a LINQ query into a function

Question

Is it possible to pass parts of a linq Query into a function? I want create a common interface for my DAL that always uses the same query interface. For example,

List<T> Get(Join j, Where w, Select s){    
    return currentDataContext<T>.Join(j).Where(w).Select(s).ToList();    
}

Is this sort of thing possible? I'm thinking it would be done with expression trees, but I haven't been able to find examples of it.

Answer 1

Well, the "join" is tricky, because it is very hard to express a join - but things like where / select / orderby are pretty easy...

Really, it is just a case of combining the various LINQ methods on IQueryable<T>, which generally accept Expression<Func<...>> for some combination. So a basic select with an optional predicate would be:

    public IQueryable<T> Get<T>(
        Expression<Func<T,bool>> predicate
        ) where T : class
    {
        IQueryable<T> query = (IQueryable<T>)GetTable(typeof(T));
        if (predicate != null) query = query.Where(predicate);
        return query;
    }

I would tend to return IQueryable<T> too, since that is fully composable. If the caller wants a list, they can always use ToList() on it... or (for example):

    using(var ctx = new MyDataContext(CONN))
    {
        ctx.Log = Console.Out;
        int frCount = ctx.Get<Customer>(c => c.Country == "France").Count();
    }

which (using Northwind) does the query:

SELECT COUNT(*) AS [value]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[Country] = @p0

The problem with including the "select" (projection) in the query is that you would end up with multiple generic types. Since you often want the projection the be an anonymous type, it would then be pretty impossible to specify the projection type (anonymous) and the table-type, and it would not be callable.

In reality, I wonder if there is much benefit writing such a method at all. I might just stick with a base method:

    public IQueryable<T> Get<T>() where T : class
    {
        return (IQueryable<T>)GetTable(typeof(T));
    }

And let the caller compose it in their preferred way - perhaps with query syntax:

       var list = (from cust in ctx.Get<Customer>()
                   where cust.Country == "France"
                   select cust.CompanyName).Take(10).ToList();

Which uses:

SELECT TOP (10) [t0].[CompanyName]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[Country] = @p0

---

Alternatively, if you really do want to include the order by and projection, then an extension method is the most practical approach; then you don't need to specify the original (source) T (which is what makes it uncallable when mixed with anon-types):

public static class QueryExtension
{
    public static IQueryable<TProjection>
        Get<TSource, TProjection, TOrderKey>(
            this IQueryable<TSource> source,
            Expression<Func<TSource, bool>> where, // optional
            Expression<Func<TSource, TProjection>> select,
            Expression<Func<TProjection, TOrderKey>> orderBy)
    {
        if (where != null) source = source.Where(where);
        return source.Select(select).OrderBy(orderBy);
    }
}

Then consider a DAL method such as:

    public List<string> Countries()
    {
        return Customers.Get(
            x=>x.CompanyName != "",
            x=>x.Country,
            x=>x).Distinct().ToList();
    }

Which uses (again, with Northwind):

SELECT DISTINCT [t0].[Country]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] <> @p0

Answer 2

Check this generic class: TableView.cs.

It basically uses a Func<TEntity, bool> delegate to apply the Where predicate:

//...
public TableView(DataContext dataContext, Expression<Func<TEntity, bool>> predicate)
{
    this.table = dataContext.GetTable<TEntity>();
    this.baseQuery = table.Where(predicate);
    this.predicate = predicate.Compile();
}
//...