Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework and compiled expressions performance

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.

like image 659
kha Avatar asked Oct 14 '25 04:10

kha


1 Answers

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.

like image 105
Kirill Shlenskiy Avatar answered Oct 16 '25 18:10

Kirill Shlenskiy