14

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I disagree with the conclusion. All mapping and expression information available to the CompiledQuery.Compile is available during non-compiled query translation.
@David: As outlined in my answer his code won't actually compile, you can't use an expression like this in the above code. And the conclusion is still valid: Expression.Compile=CLR, CompiledQuery.Compile und DataContext query = SQL (and until the OP proves otherwise, I proclaim this is the same). The reason he got different SQL is becuase he wasn't invoking an Expression, but a CLR delegate.
Using the Expression directly will not compile. I realise that my first example isn't clear, but I am actually using a Func. The first example uses a normal Func, which produces a query-per-row. Using the same Func that has been through CompiledQuery.Compile, we only see one query generated, without using the DataContext in the signature! This works in Where and Select from my limited testing but i'm sure it will work anywhere. I assume the parser ignores the normal Func till a later stage.
@MiG, alright, that's what I thought. Can you carefully re-read my answer and see if you get why this is the expected behavior? From my point of view this is clear, but don't hesitate to ask and I'll try to explain it differently.
Yeah, I think I understand it now, although I wish the CompiledQuery.Compile behaviour was the default behaviour. I will have to get the reflector out to see the exact science for my own eyes. Thanks for the answer.
1

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.

Improve this answer

2 Comments

I have seen those examples before and neither explain the particular questions I am asking. Why are the DataContext parameters not required as per my example? Why are the normal Func's not built into the full expression tree on the first two examaples? What is different between the two? IMO I would expect the first two examples to produce 1 query.
Ok. This seems to give a bit more insight. But I would have thought that the expression tree would be entirely built before any SQL is generated. Your answer makes sense, in that Select seems to ignore the first set of Func's until iteration. Also, This yields even different behaviour using the Func in a Where. The linq query fails to even run (I will update my original question).
0 
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.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.