FirstOrDefault(x => x.ColumnVal == 1) versus EF Where(x => x.ColumnVal == 1)

entity-framework linq-to-entities

Question

I had a LINQ query that loaded the items in the following hierarchy.

Query #1

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

I was sleeping 17 zzz's with it.
Nearly all of the CPU was being used, and memory use was unusually high.

I made the following adjustments, and the performance issue was resolved.

Query #2

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



I'm only trying to corroborate what I think.
Most likely, Query #1 is cycling over all of my records in the range in my mind in search of a matched record.
vs
Query #2 only returns the first result after filtering the database's entries.

Is this the cause of the Query #1's performance issues?

Do I have to utilize the just to be safe.Select(x => x) prior to the.FirstOrDefault() ?

Query #3

var result = db.Orders
               .Include("Customer")
               // many other .Include() here
               .Where(x => x.Customer.CustomerId == 1 &&
                           x.OrderId == orderId)
               .Select(x => x)
               .FirstOrDefault();
1
6
10/28/2010 9:20:53 PM

Accepted Answer

No, when they are both performed, they ought to produce the same SQL query. You can demonstrate that by digging up the SQL Profiler and comparing the exact SQL that EF supplied in both instances. Your performance optimization ought to have been brought on by a few more elements. Why is this:

An ObjectQuery<T> is produced using the Include technique.

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

Consequently, its FirstOrDefault method has two overloads:

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

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

When you program.FirstOrDefault(x => x.Customer.CustomerId == 1 To determine the type of the lambda expression, the compiler will enter a procedure named Resolution of Overload.x => x.Customer.CustomerId == 1 because it may be converted to either overload's argument type.
The C# Language Specification contains an algorithm that the compiler will use to determine that transforming the lambda to theExpression<Func<T, Boolean> a improved conversion rather than toFunc<T, Boolean> choose the IQueryable overload instead.
As a result, while viewing it in the SQL Profiler, you will notice the condition in the produced SQL.

3
10/29/2010 4:12:46 AM

Popular Answer

I believe using would be better (condition). Take(1). Due to Take(1straightforward )'s conversion to a TOP clause in SQL, FirstOrDefault() is used instead. Who's with me?



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow