Multiple DB queries when Eager Loading in Entity Framework 6

Question

My issue is that every time I load this view my application sends 249 identical queries to the database. Originally I was using lazy loading and the number of queries was double.

The 249 number above represents the number of rows this query returns.

My understanding is that .Include creates a join which should eliminate this behavior?

Can anyone tell me how to eliminate this duplication of queries?

Cheers!

The code below is pseudo code and is not intended to compile.

Controller:

 var apples = _unitOfWork.Context.Apples
    .Include(x=> x.AppleTypes)
    .OrderByDescending(x => x.Id)
    .Where(x => x.Status == (int)AppleStatusConstants.New 
          && x.IsRejected != true && x.AppleManId != null);

 return View(apples);

View:

@model IEnumerable<Apple>

@Html.DisplayNameFor(model => model.AppleTypes.TypeSeason)
@foreach (var item in Model){
         @Html.DisplayFor(modelItem => item.AppleTypes.TypeSeason)
         }

SQL Trace from Glimpse:

    SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Type] AS [Type],  
[Extent2].[Id] AS [Id1], 
[Extent2].[TypeSeason] AS [TypeSeason], 

FROM  [dbo].[Apples] AS [Extent1]
LEFT OUTER JOIN [dbo].[AppleTypes] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
WHERE (0 = [Extent1].[Status]) AND ( NOT ((1 = [Extent1].[IsRejected]) AND ([Extent1].[IsRejected] IS NOT NULL))) AND ([Extent1].[OrgUnitId] IS NOT NULL)
ORDER BY [Extent1].[Id] DESC

Glimpse Screenshot

Erik Philips · Accepted Answer · 2016-03-24 23:26:51Z

1

Multiple DB queries when Eager Loading in Entity Framework 6

You haven't told it to Eager Load the base query, only it's Includes.

This code:

var apples = _unitOfWork.Context.Apples
  .Include(x=> x.AppleTypes)
  .OrderByDescending(x => x.Id)
  .Where(x => x.Status == (int)AppleStatusConstants.New 
         && x.IsRejected != true && x.AppleManId != null);

Is creating an IQueryable< T >. The simple solution is to add .ToList() at the end.

  .Where(x => x.Status == (int)AppleStatusConstants.New 
     && x.IsRejected != true && x.AppleManId != null)
  .ToList();

So in your code, this is the culprit:

@foreach (var item in Model){

That is causes EF to query for one entity at a time.

PS: The following Statements are identical (unless you've overridden object.cshtml)

@foreach (var item in Model){
  @Html.DisplayFor(modelItem => item.AppleTypes.TypeSeason)
}

and

@Html.DisplayFor(m => m)

and

@Html.DisplayForModel()

answered Mar 24, 2016 at 23:26

Erik Philips

54.9k11 gold badges133 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johnny Grimes Over a year ago

Is .ToList() a more expensive operation? One of the senior programmers here encourages me not to user .ToList() whenever possible

Erik Philips Over a year ago

There is no good answer. It simply depends. There are times when .ToList() is the correct solution and times when it is not.

Collectives™ on Stack Overflow

Multiple DB queries when Eager Loading in Entity Framework 6

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related