CODEWITHSUNDEEP
asp.net-mvcentity-frameworkeager-loading
Johnny Grimes
Johnny Grimes

Reputation: 411

Multiple DB queries when Eager Loading in Entity Framework 6

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 enter image description here

Upvotes: 1

Views: 106

Answers (1)

Erik Philips
Erik Philips

Reputation: 54646

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()

Upvotes: 1

Related Questions