How to write more efficient linq to entities query using EF6-CodePudding

I have an one-to-many relation in my entities:

public class Header
{
   public Header()
   {
       Items = new List<Item>
   }
   public int Id {get; set;}
   public virtual ICollection<Item> Items {get; set;}
   // other properties
}

public class Item
{
   public int Id {get; set;}
   public virtual Header Header { get; set; }
   public string Title { get; set; } 
   public DateTime CreationDate { get; set; } 
   public int Weight { get; set; } 
}

I want to load Header and some of its Items, so I wrote this linq to entity query(EF6):

using(var ctx = new MyContext())
{
   var result = ctx.Headers.Where(someConditions)
      .AsNoTracking()   
      .Select(header => new {
         HeaderId = header.Id,
         //fetch other header properties here
         LastItemCreationDate = header.Items.OrderByDescending(item => item.CreationDate)
                                            .Select(item => item.Title)
                                            .FirstOrDefault(),
         LastItemTitle = header.Items.OrderByDescending(item => item.CreationDate)
                                            .Select(item => item.CreationDate)
                                            .FirstOrDefault(),
         LastItemWeight = header.Items.OrderByDescending(item => item.CreationDate)
                                            .Select(item => item.Weight)
                                            .FirstOrDefault()
      }).ToList();
}

This query generate a sql script with 3 times join Header and Item tables, is there any more efficent way to write this query to join Header and Item tables one time?

CodePudding user response：

Since you are using Select, you don't need AsNoTracking since the resulting query will not load any entities. The key performance impacts in your case would be indexes in the Header table suitability for your Where clause, then also whether there is a Descending Index available on the CreationDate in the Items table.

Another improvement would be to alter the projection slightly:

var result = ctx.Headers.Where(someConditions)
  .Select(header => new {
     HeaderId = header.Id,
     LatestItem = header.Items
         .OrderByDescending(item => item.CreatedDate)
         .Select(item => new 
         {
            Title = item.Title,
            CreationDate = item.CreationDate,
            Weight = item.Weight
         }).FirstOrDefault()
  }).ToList();

This will change the resulting anonymous type structure a bit, but should result in a nicer, single join.

You will get a result.HeaderId, then a null-able result.LastestItem containing the latest item's properties. So result.LatestItem?.Title instead of result.Title.