CODEWITHSUNDEEP
c#entity-framework
Sampath
Sampath

Reputation: 65870

Entity Framework Performance issue with Include

I would like to know which one has the better performance ?

 var allocations = 
        Catalog.ResourceAllocations
               .Where(c => c.Pet.Key == petKey && c.Pet.Owner.Key == ownerKey)
               .Include(k => k.Appointment)
               .Include(k => k.Service)
               .Include(k => k.Appointment.Provider.Address)
               .ToList();

OR

var allocations = 
       Catalog.ResourceAllocations
              .Where(c => c.Pet.Key == petKey && c.Pet.Owner.Key == ownerKey)
              .Include(k => k.Appointment.Provider.Address)
              .Include(k => k.Service)
              .ToList();

Upvotes: 1

Views: 381

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

Documentation for DbQuery<T>.Include(path) states that (please read NOTES at the end - it describes how path including works):

Paths are all-inclusive. For example, if an include call indicates Include("Orders.OrderLines"), not only will OrderLines be included, but also Orders.

So k.Appointment.Provider.Address will include k.Appointment anyway. Even if there is no performance hit, second query is better, because it does not contain duplicate include definitions.

UPDATE: There will be no performance difference in database query, because both LINQ queries will result in same SQL query generated (well, order of LEFT OUTER JOINS may differ). But there will be small performance difference on query generation, because when you are including some path, new ObjectQuery will be generated (yes, each Include creates new query instead of modifying existing one).

NOTE: It was interesting to know why there is no difference - I did some investigation on Entity Framework 6 sources and found the way how EF collects paths which should be included. There is internal sealed class Span which holds collection of paths to determine which elements are included into a query. SpanPath is really simple - it's just a wrapper on list of strings, which represent navigations to be included:

internal class SpanPath
{
    // you can think naviagations as path splitted by dots
    public readonly List<string> Navigations;
    // ...
}

And Span is a class which holds all included paths:

internal sealed class Span
{
    private readonly List<SpanPath> _spanList = new List<SpanPath>();

    public void Include(string path)
    {
        Check.NotEmpty(path, "path");
        SpanPath spanPath = new SpanPath(ParsePath(path));
        this.AddSpanPath(spanPath);
    }

    internal void AddSpanPath(SpanPath spanPath)
    {
        if (this.ValidateSpanPath(spanPath))
        {
            this.RemoveExistingSubPaths(spanPath);
            this._spanList.Add(spanPath);
        }
    }

    private bool ValidateSpanPath(SpanPath spanPath)
    {
        for (int i = 0; i < this._spanList.Count; i++) 
        {           
            if (spanPath.IsSubPath(this._spanList[i]))                
               return false;    
        }

        return true;
    }
}

So, here is what happens - when you include new path, then:

  1. It is splitted into list of strings and wrapped into SpanPath
  2. If current path is sub-path of some already added path, then we just ignore it
  3. Otherwise we check if there sub-path of current path exists in our navigations. If so, we remove all sub-paths
  4. Now we can add new path.

When you are including Appointment.Provider.Address path in first case, then Appointment path will be removed on step #3, because it is sub-path of Appointment.Provider.Address.

SUMMARY:

Do not explicitly include sub-path in query - it will cause in new ObjectQuery instance creation, and it will not affect generated query. It will be either ignored, or it will be removed when you'll add path which includes this one.

Upvotes: 4

Related Questions