Search code examples
c#linqentity-framework-corefunc

.FirstOrdefault(lambda) is not added to SQL query created by EF. Lambda is a function argument

I am currently using entity framework to execute queries onto a sql database. To avoid having to create the database directly into my main code I wrote a class that does all the database work. Because there are many different ways we look up data from a certain table, and I did not want to write a function for every possible column or combination of columns we can use to look up the table I wrote following function:

Public Foobar GetFoobarWithDetail (Func<Foobar,bool> lambda)
{
return context.Foobar.include(fb=>fb.Detail).FirstOrDefault(lambda);
}

However, when looking at the created sql statements, it does not include the FirstOrDefault() and it does not include the conditions in my lambda. This is not a big problem on test servers, but that first part of the query is about 20k records and it takes too much time to convert them all to objects, especially since I only need one. Is there a way to get this to work, or will it only evaluate the lambda after creating the Iqueryable with objects?

I want to be able to "dynamically" change the selection criteria of my data by using a lambda as an argument for a function, but I want that lambda to be added to the SQL statement, instead of being executed 'in memory'

return context.Foobar.include(fb=>fb.Detail).FirstOrDefault(lambda);

will create a select * without a where clause

return context.Foobar.include(fb=\>fb.Detail).FirstOrDefault(fb => fb.id = 'id');

Will create a select TOP (1) with a where clause Changes are high I am just doing something astronomically stupid, but at least I would like to know why one works and the other doesn't. Especially since we are using the version of entity framework where statements that cannot be resolved into SQL will generate errors.

Solution

  • Change the method signature to accept Expression<Func<...>>:

    public Foobar GetFoobarWithDetail (Expression<Func<Foobar,bool>> lambda)
{
    return context.Foobar.include(fb=>fb.Detail).FirstOrDefault(lambda);
}

    EF works with IQueryable<T> which uses expression trees so the translation is possible, using "just" Func will result in working with IEnumerable<T> which leads to query materialization.

    Read more: