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
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With