How to Parameterize Linq Query or Lambda Query in Entity Framework?

Question

I write some Parameterize Lambda Queries

        //Method 1:
        Func<SalesOrderLineEntity, bool> func01 = (o => o.SOLNumber == "123456");
        var q01 = _context.SalesOrderLineEntities.Where(func01).ToList();
        //Got the result, but SQLServer Read All Records to memory before "where"

        //Method 2:
        Expression<Func<SalesOrderLineEntity, bool>> exp02 = (o => o.SOLNumber == "123456");
        var q02 = _context.SalesOrderLineEntities.Where(exp02).ToList();
        //Got the result,Exec "Where" in SQLServer

        //Method 3:
        Expression<Func<SalesOrderLineEntity, bool>> exp03 = (o => func01(o));
        var q03 = _context.SalesOrderLineEntities.Where(exp03.Compile()).ToList();
        //Same to Method 1,because Compile() result is Func<SalesOrderLineEntity, bool>

        //Method 4:
        var q04 = _context.SalesOrderLineEntities.Where(exp03).ToList();
        //Error:The LINQ expression node type 'Invoke' is not supported in LINQ to Entities

Method 1 and 3：Efficiency is very low Method 4:Error

Method 2:Need I Build a Expression through the Lambda. I feel it is very difficult, because i will use many "if,else".it easier to create a function. What is the correct way to do that?

you need the Expression variation (without compile), because EF builds the actual SQL query based on the expression tree. — Peter Porfy
– Peter Porfy, Commented Jan 22, 2013 at 10:02
Very surprised method 1 loads all in memory before where clause, that shouldn't happen unless the property SOLNumber is not a column in the db but rather a custom property (defined in a partial class or something) — dutzu
– dutzu, Commented Jan 22, 2013 at 10:14
ooooh, it's because there are 2 implementations for Where, one for IEnumerable<> which is used in Method 1 and one for IQueryable which relies on Expression trees -> method 2. I'm right right? — dutzu
– dutzu, Commented Jan 22, 2013 at 10:15
So you want to be able to do Method 2 with an arbitrary string instead of "123456", is that right? — AakashM
– AakashM, Commented Jan 22, 2013 at 11:18

Peter Porfy · Accepted Answer · 2013-01-22 13:54:58Z

Variations

Method 1: EF reads all the records from the DB because you pass a Func into the Where clause, which is not the right candidate: EF cannot extract the needed information from it to build the query, it can only use that function on an in-memory collection.

Method 2: this is the correct way to do EF queries because EF builds up the actual query based on the Expression tree. It may look like the same as Method 1 when you write .Where but this is different.

IQueryable extension methods are using Expression trees, so you can (or EF can) evaluate that information at runtime.

Method 3: this is essentially the same as Method 1, because you compile the expression. This is a key difference while you use them: an expression contains the informations to build the actual operation but that's not the operation itself. You need to compile it before (or for example you can build SQL queries based on them, that's how EF works).

Method 4: EF cannot translate your func01() call to any SQL function. It cannot translate any kind of code because it needs an equivalent SQL operation. You can try to use a general method, you will get the same result, it's not about the Func.

What happens here?

If we simplify the underlying process then the answer above might by more clear.

//Method 2:
Expression<Func<SalesOrderLineEntity, bool>> exp02 = (o => o.SOLNumber == "123456");
var q02 = _context.SalesOrderLineEntities.Where(exp02).ToList();
//Got the result,Exec "Where" in SQLServer

EF can read the following (via the expressions):

the user want to filter with Where
here is an expression for that, let's get some information
well, it needs SalesOrderLineEntity and I have a mapping for that type
the expression tells that the property SOLNumber must be equal to "123456"
ok, I have a mapping for SOLNumber so it's good
and I can translate the equal operator to an equivalent SQL operator
everything okay, so we can build the SQL query

Of course, you cannot do this with a Func for example because that object doesn't contain these informations.

Schalk · Accepted Answer · 2014-08-07 17:00:18Z

0

Not sure if this is applicable but have you look at compiled queries: Compiled Queries (LINQ to Entities) which should result in a more efficient SQL statement

answered Aug 7, 2014 at 17:00

Schalk

2901 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to Parameterize Linq Query or Lambda Query in Entity Framework?

2 Answers 2

Variations

What happens here?

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Variations

What happens here?

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related