EF其中(x => x.ColumnVal == 1)vs FirstOrDefault(x => x.Column == 1) [英] EF Where(x => x.ColumnVal == 1) vs FirstOrDefault(x => x.Column == 1)

问题描述

我有一个LINQ查询，该查询加载了如下所示的对象层次结构.

I had a LINQ query that loads a hierarchy of objects like the following.

查询#1

var result = db.Orders
               .Include("Customer")
               // many other .Include() here
               .FirstOrDefault(x => x.Customer.CustomerId == 1 &&
                                    x.OrderId == orderId);

我遇到了主要性能问题.
CPU使用率接近100％，内存使用率很高.

I was having MAJOR performance problem with it.
The CPU usage was near 100% and memory usage was very high.

我将其调整为以下内容，从而解决了性能问题.

And I tweaked it to the following and the performance problem was fixed.

查询#2

var result = db.Orders
               .Include("Customer")
               // many other .Include() here
               .Where(x => x.Customer.CustomerId == 1 &&
                           x.OrderId == orderId)
               .FirstOrDefault();

我只是想证实自己的怀疑.
查询#1可能正在内存中遍历我的所有记录，以寻找匹配的记录
与
查询#2筛选数据库上的记录，然后仅获取第一条记录.

I just want to confirm my suspicion.
Query #1 is probably looping through all my records in memory looking for a matching record
vs
Query #2 filters the records on the Database and then getting the first record only.

这就是为什么查询#1 出现性能问题吗?

Is that why the Query #1 has performance problems?

为了安全起见，我需要在.FirstOrDefault()之前使用.Select(x => x)吗?

Just to be safe, do I need to use the .Select(x => x) before the .FirstOrDefault()?

查询#3

var result = db.Orders
               .Include("Customer")
               // many other .Include() here
               .Where(x => x.Customer.CustomerId == 1 &&
                           x.OrderId == orderId)
               .Select(x => x)
               .FirstOrDefault();

推荐答案

否，它们在执行时都应导致相同的SQL查询.您可以通过查看 SQL Profiler 来证明这一点，并查看在两种情况下从EF提交的确切SQL是什么.您的性能优化应该是由其他一些因素引起的.原因如下:

Include 方法返回 ObjectQuery< T> :

No, they both should result in a same SQL query when being executed. You can prove it by looking into SQL Profiler and see what is the exact SQL being submitted from EF in both cases. Your performance optimization should have been caused by some other factors. Here is why:

Include method returns an ObjectQuery<T>:

public class ObjectQuery<T> : ObjectQuery, IOrderedQueryable<T>, 
                              IQueryable<T>, IEnumerable<T>, 
                              IOrderedQueryable, IQueryable, 
                              IEnumerable, IListSource

这意味着其 FirstOrDefault 方法带有2个重载:

Which means its FirstOrDefault method comes with 2 overloads:

// Defined by Enumerable:
FirstOrDefault(Func<T, Boolean>)

// Defined by Queryable:
FirstOrDefault(Expression<Func<T, Boolean>>)

在编写代码.FirstOrDefault(x => x.Customer.CustomerId == 1时，编译器将进入名为 重载分辨率 的进程，以推断lambda表达式x => x.Customer.CustomerId == 1的类型，因为它可以转换为该类型两种重载的参数类型.
编译器将使用一种算法(我仍在尝试在C#语言规范中找到该算法！)，发现将lambda转换为Expression<Func<T, Boolean>的转换比 更好的转换 到Func<T, Boolean>，因此选择 IQueryable 重载.
因此，在SQL事件探查器中观察谓词时，您会在生成的SQL中看到谓词.

When you code .FirstOrDefault(x => x.Customer.CustomerId == 1 compiler will go into a process called Overload Resolution to infer the type of the lambda expression x => x.Customer.CustomerId == 1 since it is convertible to the type of both overload's parameter types.
Compiler will use an algorithm (that I am still trying to find in C# Language Specification!), figure out that converting the lambda to the Expression<Func<T, Boolean> is a better conversion than to Func<T, Boolean> so pick the IQueryable overload.
Therefore, you'll see the predicate in the generated SQL when observing it in the SQL Profiler.

这篇关于EF其中(x =&gt; x.ColumnVal == 1)vs FirstOrDefault(x =&gt; x.Column == 1)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！