Entity Framework (5) LINQ methods return entire entity collection THEN apply filter

Question

I have code like this:

        User rValue = null;
        var userByID = new Func<User, bool>(x => x.UserId.Equals(userID, StringComparison.InvariantCultureIgnoreCase));

        if (this._context.Users.Any(userByID))
        {
            var user = this._context.Users
                .Include("nested.Associations")
                .Single(userByID);

            rValue = user;
        }

        return rValue;

I started profiling the query, and noticed that EF is NOT applying the Func<> to the SQL query, but to the collection in-mem after returning the entire .Users set.

Interestingly, the following generates the query with the correct where clause filtering the UserID:

        User rValue = null;
        //var userByID = new Func<User, bool>(x => x.UserId.Equals(userID, StringComparison.InvariantCultureIgnoreCase));

        if (this._context.Users.Any(x => x.UserId.Equals(userID, StringComparison.InvariantCultureIgnoreCase)))
        {
            var user = this._context.Users
                .Include("nested.Associations")
                .Single(x => x.UserId.Equals(userID, StringComparison.InvariantCultureIgnoreCase));

            rValue = user;
        }

        return rValue;

Why will EF not build the predicate var into the generated SQL?
Is there another way to re-use the predicate so that my code can stay clean and readable, and also have EF stuff it into the generated SQL?

usr · Accepted Answer · 2013-02-12 23:37:37Z

4

EF cannot interpret compiled IL code (which your Func essentially is). It would need to guess the original code like Reflector does. That's not an attractive design choice for a framework so it doesn't work that way. When you inline the lambda it is not a Func. It is an expression which is analyzable. Search for "C# expression trees" to find out more.
Yes: Use Expression<Func<User, bool>> for your variable.

answered Feb 12, 2013 at 23:37

usr

172k38 gold badges251 silver badges380 bronze badges

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

3 Comments

JoeBrockhaus Over a year ago

Interesting. That makes sense, though it isn't obvious that would be the case. Trying as an expression now. What about the expression makes it analyzable at runtime as opposed to the Func? It was my understanding that all Lambdas are compiled into IL and optimized away (which is why you can't use them in Immediate window, etc)?

usr Over a year ago

Expressions compile into object tree instantiations. You can do that yourself. Example: Expression.Constant(1234).

usr Over a year ago

I clarify: lambdas have a C# specific type that does not exist in the CLR. They are convertible to IL Func's AND to Expression's. So they react to context in a well-defined way. Hope that helps.

Collectives™ on Stack Overflow

Entity Framework (5) LINQ methods return entire entity collection THEN apply filter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related