EF Creating business objects in Linq or foreach
I am measuring differences in query execution and stumbled upon a case that I do not explain. The request should receive 10,000 clients with their main address (a client can have many addresses). We have used two different methods with navigation properties that are very different at runtime.
The first method returns clients the way I usually write Linq queries: write the results directly to the business object and call ToList (). This method takes 25 seconds.
The second method first returns clients to the EF Entities list. EF objects are converted to business objects in a foreach loop. This method takes 2 seconds to complete.
Can someone explain the difference? And is it possible to change the first method so that the runtime is similar to the second?
private List<ICustomer> NavigationProperties_SO(int method)
{
using (Entities context = new Entities())
{
context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
context.Configuration.ProxyCreationEnabled = false;
context.Configuration.AutoDetectChangesEnabled = false;
List<ICustomer> customerList = new List<ICustomer>();
if (method == 1)
{
// Execution time: 25 seconds
customerList = (from c in context.cust
.Include(o => o.AddressList)
.Include(o => o.AddressList.Select(p => p.ADDR))
let mainAddress = c.AddressList.Where(o => o.Main_addr == "1").FirstOrDefault()
select new Customer
{
cust = c,
mainAddress = mainAddress,
addr = mainAddress == null ? null : mainAddress.ADDR
}).AsNoTracking().ToList<ICustomer>();
}
else if (method == 2)
{
// Execution time: 2 seconds
var tempList = (from c in context.cust
.Include(o => o.AddressList)
.Include(o => o.AddressList.Select(p => p.ADDR))
select c).AsNoTracking().ToList();
foreach (var c in tempList)
{
ICustomer customer = new Customer();
var mainaddress = c.AddressList.Where(o => o.Main_addr == "1").FirstOrDefault();
customer.cust = c;
customer.mainAddress = mainaddress;
customer.addr = mainaddress == null ? null : mainaddress.ADDR;
customerList.Add(customer);
}
}
return customerList;
}
}
Edit
Here are the (simplified) queries generated by the Entity Framework:
Method 1
SELECT
*
FROM [DBA].[CUST] AS [Extent1]
OUTER APPLY (SELECT TOP ( 1 )
*
FROM [DBA].[CUST_ADDR] AS [Extent2]
WHERE (([Extent1].[Id] = [Extent2].[Id]) AND (N'1' = [Extent2].[Main_addr])
ORDER BY 'a' ) AS [Limit1]
LEFT OUTER JOIN [DBA].[ADDR] AS [Extent3] ON [Limit1].[Id] = [Extent3].[Id]
Method 2
SELECT
*
FROM ( SELECT
*
FROM [DBA].[CUST] AS [Extent1]
LEFT OUTER JOIN (SELECT *
FROM [DBA].[CUST_ADDR] AS [Extent2]
LEFT OUTER JOIN [DBA].[ADDR] AS [Extent3] ON [Extent2].[Id] = [Extent3].[Id] ) AS [Join1] ON ([Extent1].[Id] = [Join1].[Id])
) AS [Project1]
The difference is that the first method does the filtering in the query ('let'), while the second method fetches all the records and filters in a loop.
source to share
I suspect
let mainAddress = c.AddressList.Where(o => o.Main_addr == "1").FirstOrDefault()
is the culprit. Some requests cause EF to request the return of all possible combinations. EF then spends a little time narrowing down the scope before it provides you with a reasonable set of results. You can use SQL Server Profiler to view the generated queries.
In any case, you can use LINQ rather than foreach at the end of the second method (it won't help performance, but readability may improve):
return tempList.Select(c => new Customer{cust=c, mainAddress = c.AddressList.FirstOrDefault(o=>o.Main_addr=="1"), ...);
source to share
Answer linked to comments ... (but two for comment)
Under "How to choose the best syntax"
I would say this is partly due to "experience" (see 9Rune5 and I suspected the same point was problematic before seeing the generated sql): but experience can sometimes give way to wrong conclusions too;)
To be a little more pragmatic, I suggest you use tools / libraries to help you view the generated sql / time on a query or page ...
ANTS Performance profiler, Miniprofiler, Sql Server profiler, etc, it may depend on your technology / needs ...
By the way, if you want to keep the "linq" syntax, you can go to
var tempList = context.cust
.Include(o => o.AddressList)
.Include(o => o.AddressList.Select(p => p.ADDR))
.AsNoTracking()
.ToList();
var result = (from c in tempList
let mainAddress = c.AddressList.Where(o => o.Main_addr == "1").FirstOrDefault()
select new Customer
{
cust = c,
mainAddress = mainAddress,
addr = mainAddress == null ? null : mainAddress.ADDR
}).ToList<ICustomer>();
But no less verbose than the foreach syntax ...
source to share