How do you feed bits of a LINQ query into a function in LINQ to SQL?

c# expression-trees linq linq-to-sql


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.

7/4/2012 2:22:17 AM

Accepted Answer

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 != "",

Which uses (again, with Northwind):

SELECT DISTINCT [t0].[Country]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] <> @p0
1/12/2009 6:55:20 AM

Popular Answer

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();

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow