EF 4.0 / FirstOrDefault方法的奇怪行为 [英] EF 4.0 / Weird behaviour of FirstOrDefault method

问题描述

我正在开发一个使用EF 4.0和POCO的小型应用程序。

在测试我的应用程序时，我越来越关注数据访问层的性能。所以我启动了SQL Profiler来查看当尝试检索记录时：

  ctx.Orders.Include（OrderItems）。 FirstOrDefault< Order>（c => c.OrderID == id）; 
  

EF发出一个SQL语句，该语句将从服务器上的Orders表中检索所有记录，因此返回DAL，此时L2E将选择一个符合标准并返回。

可以更改此行为。

谢谢！

Zen

解决方案

请尝试这个：

  ctx.Orders.Include（OrderItems）。其中（c => c.OrderID == id）.FirstOrDefault（）; 
  

顺便说一下，您不需要查看SQL Profiler要查看生成的SQL，可以通过以下方式在代码中执行：

  IQueryable< ; Order> query = ctx.Orders.Include（OrderItems）
 .Where（c => c.OrderID == id）; 
 string sql =（（ObjectQuery< Order>）查询）.ToTraceString（）; 
  

编辑：


问题：如果我们有一个函数，如 FindOrders ，我们需要将谓词传递给这个函数？
答案：代码应该如下所示：

  public List< Order> FindOrders （表达式< Func< Order，bool >>谓词）{
 using（DBContext ctx = new DBContext（））{
 return ctx.Orders.Include（OrderItems）。Where（predicate）.ToList< ;订单>（）; 
} 
} 
 
 //调用函数：
 var order = FindOrders（c => c.OrderID == id）[0]; 
  

这一次，如果你检查你的SQL Profiler，你会看到SQL中有一个where子句已经提交给SQL Server。



说明：


这个奇怪的行为的原因是，基本上当你写入Where（c => c.OrderID == id），C＃编译器将您的lambda表达式转换为一个表达式< Func< TSource，int，bool >>和NOT到Func< TSource，int，bool>。



可查询的MSDN文档 也证实了这一点：$ b​​ $ b

 
 public static IQueryable< TSource>其中< TSource>（
这个IQueryable< TSource>源，
表达式&FunC< TSource，int，bool >>谓词
）
  

但是，如果您将Func< TSource，int，bool >>显式传递到Where方法，那么您基本上调用 Enumerable.Where：

 
 public static IEnumerable< TSource>其中< TSource>（
这个IEnumerable< TSource>源，
 Func< TSource，int，bool>谓词
）
  

我们知道IEnumerable.Where是LINQ to Objects实现而不是 LINQ to Entities，这意味着你到达IEnume在调用时，ObjectQuery运行初始查询（ctx.Orders.Include（OrderItems）），并将结果提供给IEnumerable.Where，以便它将在客户端。



另一方面，调用Queryable.Where（ctx.Orders.Include（OrderItems）。其中（c => c.OrderID == id） .FirstOrDefault（））将不会执行，直到达到我们称之为FirstOrDefault（）函数的点，这意味着Queryable。然后将其与其余查询一起转换为本机SQL，并将其传递到SQL Server，因此您将看到SQL语句中的Where子句，这绝对是所需的运行时行为。



顺便说一下，不要忘记将这个命名空间导入到你的类文件中：

 
使用System.Linq.Expressions; 
  

I am developing a small application using EF 4.0 and POCO.

While testing my application, I grew concerned about the performance of the Data Access Layer. So I fired SQL Profiler to see that when trying to retrieve a record:

ctx.Orders.Include("OrderItems").FirstOrDefault<Order>(c => c.OrderID == id);

the EF issues a SQL statement that would retrieve all records from the Orders table on the Server and as such return to DAL at which time L2E would pick one thay meet the criteria and return it.

Can this behaviour be changed.

Thanks!

Zen

解决方案

Try this one please:

ctx.Orders.Include("OrderItems").Where(c => c.OrderID == id).FirstOrDefault();

By the way you don't need to look into SQL Profiler to see the generated SQL, you can do it right inside your code by writing:

IQueryable<Order> query = ctx.Orders.Include("OrderItems")
                                    .Where(c => c.OrderID == id);
string sql = ((ObjectQuery<Order>)query).ToTraceString();

EDIT:
Question: What if we have a function like FindOrders and we need to pass the predicate to this function? Answer: The code should looks like:

public List<Order> FindOrders(Expression<Func<Order, bool>> predicate) { 
    using (DBContext ctx = new DBContext()) { 
        return ctx.Orders.Include("OrderItems").Where(predicate).ToList<Order>(); 
    } 
} 

//Calling the function:
var order = FindOrders(c => c.OrderID == id)[0];

This time, if you check your SQL Profiler you'll see there is a where clause in the SQL that's been submitted to SQL Server.

Explanation:
The reason for this "Weird behavior" is that basically when you write Where(c => c.OrderID == id), C# compiler cast your lambda expression into an Expression<Func<TSource, int, bool>> and NOT to a Func<TSource, int, bool>.

MSDN Documentation for Queryable.Where also confirms this:

public static IQueryable<TSource> Where<TSource>(
    this IQueryable<TSource> source,
    Expression<Func<TSource, int, bool>> predicate
)

However if you explicitly pass a Func<TSource, int, bool>> to the Where method then you are basically calling Enumerable.Where:

public static IEnumerable<TSource> Where<TSource>(
    this IEnumerable<TSource> source,
    Func<TSource, int, bool> predicate
)

And as we know IEnumerable.Where is "LINQ to Objects" implementation and NOT "LINQ to Entities" which means onces you reach to your IEnumerable.Where call, the ObjectQuery run the initial query (ctx.Orders.Include("OrderItems")) and gives the results to the IEnumerable.Where so that it will filter it out for you on the Client Side.

On the other hand the call with Queryable.Where (ctx.Orders.Include("OrderItems").Where(c => c.OrderID == id).FirstOrDefault()) will not be executed until it reach to the point that we call FirstOrDefault() function which means the Queryable.Where is then translated into native SQL along with the rest of the query and will be passed to the SQL Server, hence you see the Where clause on the SQL statement which definitely is the desired runtime behavior.

By the way, don't forget to import this namespace to your class file:

using System.Linq.Expressions;

这篇关于EF 4.0 / FirstOrDefault方法的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！