Can multiple compiled linq queries be chained together?

Question

I am trying to chain multiple compiled linq queries together. I have succeeded in chaining two queries together, but I cannot get a chain of three to work correctly. So here is a reduction of my code to recreate the issue. My two questions are: 'Why isn't this working?' and 'Is there a better way to keep the performance benefit of compiled queries and also avoid duplication of base query logic that is commonly used?'

Define the following two queries:

Func<DataContext, IQueryable<User>> selectUsers = 
    CompiledQuery.Compile(
        (DataContext dc)=>dc.Users.Select(x=>x)
    );
//        
Func<DataContext, string, IQueryable<User>> filterUserName = 
    CompiledQuery.Compile(
        (DataContext dc, string name) =>
            selectUsers(dc).Where(user=>user.Name == name)
    );

Calling and enumerating the chain works fine:

filterUserName(new DataContext(), "Otter").ToList();

Add a third query to the chain:

Func<DataContext, string, int, IQueryable<User>> filterUserAndGroup =     
    CompiledQuery.Compile(
        (DataContext dc, string name, int groupId) => 
            filterUserName(dc, name).Where(user=>user.GroupId == groupId)
    );

Calling the chain does not work:

filterUserAndGroup(new DataContext(), "Otter", 101);

System.InvalidOperationException: Member access 'String Name' of 'User' not legal on type 'System.Linq.IQueryable1[User].. at System.Data.Linq.SqlClient.SqlMember.set_Expression(SqlExpression value) at System.Data.Linq.SqlClient.SqlFactory.Member(SqlExpression expr, MemberInfo member) at System.Data.Linq.SqlClient.SqlBinder.Visitor.AccessMember(SqlMember m, SqlExpression expo) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitMember(SqlMember m) at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitBinaryOperator(SqlBinary bo) at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select) at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a) at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select) at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitIncludeScope(SqlIncludeScope scope) at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) at System.Data.Linq.SqlClient.SqlBinder.Bind(SqlNode node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection1 parentParameters, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Compile(Expression query) at System.Data.Linq.CompiledQuery.ExecuteQuery(DataContext context, Object[] args) at System.Data.Linq.CompiledQuery.Invoke(TArg0 arg0, TArg1 arg1) at TestMethod() in ....

Answer 1

Admittedly, I am not familiar with CompiledQuery. But, due to the deferred execution nature of LINQ, you can do something like this:

var result = dbContext.Users.Where(user => user.id == id);
result = result.Where(user => user.GroupID == groupID);
result = result.Select(user => user.username);

for(String username in result){ 
   ; // do something
}

The above is of course a simple example. But it can be quite useful when, say, combining different queries together based on a user input (such as an "advanced search" form on a website).

Answer 2

Looks like you need to convert your first compiled query to a list before executing the second one. In theory that should have caused an error with your two queried chain as well.

From [MSDN CompiledQuery][1]:

If a new query operator is applied to the result of the delegate execution, an exception is generated.

When you want to execute a query operator over the result of executing a compiled query, you must translate the result to a list before operating on it.

Perhaps this code will fix it although that could have implications for the roundtrips back to the database if you're using LINQ to SQL.

filterUserName(dc, name).ToList().Where(user=>user.GroupId == groupId)

Answer 3

Do you need to use the CompiledQuery class? Try this...

static Func<DataContext, IQueryable<User>> selectUsers =
  (dc) => dc.Users.Select(x => x);
//        
static Func<DataContext, string, IQueryable<User>> filterUserName =
    (DataContext dc, string name) =>
        selectUsers(dc).Where(user => user.Name == name);
//
static Func<DataContext, string, int, IQueryable<User>> filterUserAndGroup =
    (DataContext dc, string name, int groupId) =>
        filterUserName(dc, name).Where(u => u.GroupID == groupId);

... test code (I know my DataContext here is not LINQ2SQL but that is the fun and beauty of LINQ) ...

Also, I do use this method against my own databases so I know they build into single queries to be sent to the database. I have even used normal instance methods that return IQueryable<> instead of Func<> delegates.

public class DataContext
{
    public static Func<DataContext, IQueryable<User>> selectUsers =
      (dc) => dc.Users.Select(x => x);
    //        
    public static Func<DataContext, string, IQueryable<User>> filterUserName =
        (DataContext dc, string name) =>
            selectUsers(dc).Where(user => user.Name == name);
    //
    public static Func<DataContext, string, int, IQueryable<User>> UsrAndGrp =
        (DataContext dc, string name, int groupId) =>
            filterUserName(dc, name).Where(u => u.GroupID == groupId);

    public DataContext()
    {
        Users = new List<User>()
        {
            new User(){ Name = "Matt", GroupID = 1},
            new User(){ Name = "Matt", GroupID = 2},
            new User(){ Name = "Jim", GroupID = 2},
            new User(){ Name = "Greg", GroupID = 2}
        }.AsQueryable();
    }
    public IQueryable<User> Users { get; set; }
    public class User
    {
        public string Name { get; set; }
        public int GroupID { get; set; }
    }
}

class Program
{
    static void Main(string[] args)
    {
        var q1 = DataContext.UsrAndGrp(new DataContext(), "Matt", 1);
        Console.WriteLine(q1.Count()); // 1
        var q2 = DataContext.filterUserName(new DataContext(), "Matt");
        Console.WriteLine(q2.Count()); // 2
    }
}