如何在实体框架中参数化Linq查询或Lambda查询? [英] How to Parameterize Linq Query or Lambda Query in Entity Framework?

问题描述

我写了一些参数化Lambda查询

        //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

方法1和3 :效率非常低 方法4 :错误

方法2 :需要通过Lambda构建表达式.我觉得这很困难，因为我将使用许多"if，else".创建函数更容易. 正确的方法是什么?

解决方案

变化

方法1 :由于您将Func传递给Where子句，因此EF从数据库中读取了所有记录，这不是正确的选择:EF无法从中提取所需的信息要构建查询，它只能在内存集合中使用该功能.

方法2 :这是进行EF查询的正确方法，因为EF会根据Expression tree建立实际的查询.编写.Where时，它看起来与方法1相同，但这是不同的.

IQueryable 扩展方法正在使用表达式树，因此您(或EF可以)在运行时评估该信息./p>

方法3 :这与方法1基本相同，因为您可以编译表达式.这是使用它们时的主要区别:表达式包含构建实际操作的信息，但不是操作本身.您需要先对其进行编译(例如，您可以基于它们构建SQL查询，这就是EF的工作原理).

方法4 :EF无法将您的func01()调用转换为任何SQL函数.它无法翻译任何类型的代码，因为它需要等效的SQL操作.您可以尝试使用通用方法，但您会得到相同的结果，这与Func无关.

这里会发生什么?

如果我们简化基本流程，那么上面的答案可能会更加清楚.

//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可以(通过表达式)读取以下内容:

• 用户要使用Where
进行过滤
• 这是一个表达式，让我们获取一些信息
• 好吧，它需要SalesOrderLineEntity并且我有一个针对该类型的映射
• 表达式表明属性SOLNumber必须等于"123456"
• 好的，我有一个SOLNumber的映射，所以很好
• 我可以将equal运算符转换为等效的SQL运算符
• 一切正常，因此我们可以构建SQL查询

当然，您不能使用Func来执行此操作，因为该对象不包含这些信息.

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?

解决方案

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.

这篇关于如何在实体框架中参数化Linq查询或Lambda查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！