Entity Framework and compiled expressions performance

Question

We are using EF for our data access and we have a query like this:

Expression<TTable, bool> expression = CreateSomeExpression();
var filter = finalExpression.Compile();
var results = db.t_Table.Where(filter).Select(x=>...);

My question is, will EF construct the correct query given a compiled expression?

As an example, if my table is:

t_Table 
( key int,
  value_1 varchar(30),
  value_2 varchar(30)
)

And the expression to be compiled is

p => p.value_1 = 100

will this translate (in EF) to:

select * from t_Table where value_1 = 100

or will it translate to

select * from t_Table

followed by a linq query on the results?

Is there a way to check what sql query will actually be called on the DB by EF?

Many thanks in advance,

Update

Whilst the accepted answer is 100% correct (and therefore is the accepted answer), the solution to my problem was to simply remove the compilation. Removing that resulted in the correct SQL query with the correct where clause.

1) try it , 2) yes, `results.ToString()` should give you the SQL — Jcl, Jan 08 '16 at 10:34
Take a look at these links. http://stackoverflow.com/a/4703001/1977871 http://stackoverflow.com/a/1412902/1977871 Also if you want to generate run time linq queries, you should look at dynamiclinq.azurewebsites.net. This is very versatile. — VivekDev, Jan 08 '16 at 10:35
@Jcl thanks for your comment. I have tried it and it works but I'm more worried about the performance implications that the results coming back (I don't want a full table results for instance). results.ToString() returns this: `System.Linq.Enumerable+WhereSelectEnumerableIterator`2[CLASS_NAME, RESULT_CLASS_NAME]` (where class_name is the table class name and result_classname is the type of return type of the select) — kha, Jan 08 '16 at 10:46
If `ToString()` generates `System.Linq.Enumerable+WhereSelectEnumerableIterator...`, this is a clear indication EF will execute part of the query in memory. The expected output is a SQL query string (`SELECT ...`). @Kiril Shlenskiy answer explains that. Just don't do `.Compile()`, use the expression. — Ivan Stoev, Jan 08 '16 at 10:52
@IvanStoev Great! That did it. Thank you very much for your help. Removing the compile was the solution. — kha, Jan 08 '16 at 10:58

Kirill Shlenskiy · Accepted Answer · 2016-01-08T10:59:23.847

Your compiled expression (resulting in a delegate of type Func<TTable, bool>) will, in fact, cause a full load on the table, i.e.

select * from t_Table

... and then filter the entities after loading them into memory.

Entity Framework can only translate expressions (i.e. Expression<Func<TTable, bool>>) into SQL queries. It cannot disassemble a compiled delegate (Func<TTable, bool>) to subsequently translate it into an SQL query.

This is where overload resolution comes into play. DbSet<T> implements both IQueryable<T> and IEnumerable<T>.

When you use extension methods on IQueryable<T> (and most of them accept expression parameters, i.e. Where<T>(Expression<Func<T, bool>>)), you get your queries executed in the DB engine.
Whenever you switch over to IEnumerable<T> extension methods (i.e. Where<T>(Func<T, bool>)), EF has no choice but to load the full set of entities into memory and then iterate over the resulting cache as you would with any other in-memory collection.

Entity Framework and compiled expressions performance

1 Answers1