LINQ-SQL reuse - CompiledQuery.Compile

Question

I have been playing about with LINQ-SQL, trying to get re-usable chunks of expressions that I can hot plug into other queries. So, I started with something like this:

Func<TaskFile, double> TimeSpent = (t =>
t.TimeEntries.Sum(te => (te.DateEnded - te.DateStarted).TotalHours));

Then, we can use the above in a LINQ query like the below (LINQPad example):

TaskFiles.Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent(t),
})

This produces the expected output, except, a query per row is generated for the plugged expression. This is visible within LINQPad. Not good.

Anyway, I noticed the CompiledQuery.Compile method. Although this takes a DataContext as a parameter, I thought I would include ignore it, and try the same Func. So I ended up with the following:

static Func<UserQuery, TaskFile, double> TimeSpent =
     CompiledQuery.Compile<UserQuery, TaskFile, double>(
        (UserQuery db, TaskFile t) => 
        t.TimeEntries.Sum(te => (te.DateEnded - te.DateStarted).TotalHours));

Notice here, that I am not using the db parameter. However, now when we use this updated parameter, only 1 SQL query is generated. The Expression is successfully translated to SQL and included within the original query.

So my ultimate question is, what makes CompiledQuery.Compile so special? It seems that the DataContext parameter isn't needed at all, and at this point i am thinking it is more a convenience parameter to generate full queries.

Would it be considered a good idea to use the CompiledQuery.Compile method like this? It seems like a big hack, but it seems like the only viable route for LINQ re-use.

UPDATE

Using the first Func within a Where statment, we see the following exception as below:

NotSupportedException: Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

Like the following:

.Where(t => TimeSpent(t) > 2)

However, when we use the Func generated by CompiledQuery.Compile, the query is successfully executed and the correct SQL is generated.

I know this is not the ideal way to re-use Where statements, but it shows a little how the Expression Tree is generated.

Answer 1

Exec Summary:

Expression.Compile generates a CLR method, wheras CompiledQuery.Compile generates a delegate that is a placeholder for SQL.

---

One of the reasons you did not get a correct answer until now is that some things in your sample code are incorrect. And without the database or a generic sample someone else can play with chances are further reduced (I know it's difficult to provide that, but it's usually worth it).

On to the facts:

Expression<Func<TaskFile, double>> TimeSpent = (t =>
    t.TimeEntries.Sum(te => (te.DateEnded - te.DateStarted).TotalHours));

Then, we can use the above in a LINQ query like the below:

TaskFiles.Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent(t),
})

(Note: Maybe you used a Func<> type for TimeSpent. This yields the same situation as of you're scenario was as outlined in the paragraph below. Make sure to read and understand it though).

No, this won't compile. Expressions can't be invoked (TimeSpent is an expression). They need to be compiled into a delegate first. What happens under the hood when you invoke Expression.Compile() is that the Expression Tree is compiled down to IL which is injected into a DynamicMethod, for which you get a delegate then.

The following would work:

var q = TaskFiles.Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent.Compile().DynamicInvoke()
});  

This produces the expected output, except, a query per row is generated for the plugged expression. This is visible within LINQPad. Not good.

Why does that happen? Well, Linq To Sql will need to fetch all TaskFiles, dehydrate TaskFile instances and then run your selector against it in memory. You get a query per TaskFile likely because they contains one or multiple 1:m mappings.

While LTS allows projecting in memory for selects, it does not do so for Wheres (citation needed, this is to the best of my knowledge). When you think about it, this makes perfect sense: It is likely you will transfer a lot more data by filtering the whole database in memory, then by transforming a subset of it in memory. (Though it creates query performance issues as you see, something to be aware of when using an ORM).

CompiledQuery.Compile() does something different. It compiles the query to SQL and the delegate it returns is only a placeholder Linq to SQL will use internally. You can't "invoke" this method in the CLR, it can only be used as a node in another expression tree.

So why does LTS generate an efficient query with the CompiledQuery.Compile'd expression then? Because it knows what this expression node does, because it knows the SQL behind it. In the Expression.Compile case, it's just a InvokeExpression that invokes the DynamicMethod as I explained previously.

Why does it require a DataContext Parameter? Yes, it's more convenient for creating full queries, but it's also because the Expression Tree compiler needs to know the Mapping to use for generating the SQL. Without this parameter, it would be a pain to find this mapping, so it's a very sensible requirement.

Answer 2

I'm surprised why you've got no answers on this so far. CompiledQuery.Compile compiles and caches the query. That is why you see only one query being generated.

Not only this is NOT a hack, this is the recommended way!

Check out these MSDN articles for detailed info and example:

Compiled Queries (LINQ to Entities)
How to: Store and Reuse Queries (LINQ to SQL)

Update: (exceeded the limit for comments)
I did some digging in reflector & I do see DataContext being used. In your example, you're simply not using it.

Having said that, the main difference between the two is that the former creates a delegate (for the expression tree) and the latter creates the SQL that gets cached and actually returns a function (sort of). The first two expressions produce the query when you call Invoke on them, this is why you see multiple of them.

If your query doesn't change, but only the DataContext and Parameters, and if you plan to use it repeatedly, CompiledQuery.Compile will help. It is expensive to Compile, so for one off queries, there is no benefit.

Answer 3

TaskFiles.Select(t => new {
  t.TaskId,
  TimeSpent = TimeSpent(t),
})

This isn't a LinqToSql query, as there is no DataContext instance. Most likely you are querying some EntitySet, which does not implement IQueryable.

Please post complete statements, not statement fragments. (I see invalid comma, no semicolon, no assignment).

Also, Try this:

var query = myDataContext.TaskFiles
  .Where(tf => tf.Parent.Key == myParent.Key)
  .Select(t => new {
    t.TaskId,
    TimeSpent = TimeSpent(t)
  });
// where myParent is the source of the EntitySet and Parent is a relational property.
//  and Key is the primary key property of Parent.