CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entitiesentity-framework-5
Benjamin
Benjamin

Reputation: 3224

Eager Loading Complex Query with Entity Framework 5

I'm loading ServiceTrips for a scheduling calendar and wondering if there is fastest approach for eagerly loading the related data from many tables.

Here is the model that needs to be loaded (the mapping is table per concrete type)

public class ServiceTrip : BaseEntity
{
    public ICollection<Employee> Crew { get; set; }
    public ICollection<ServiceAssignment> Assignments { get; set; }
}

public class ServiceAssignment : BaseEntity
{
    public ServiceOrder ServiceOrder { get; set; }
    public DeliveryOrder DeliveryOrder { get; set; }
}

public class ServiceOrder : OrderBase { }
public class DeliveryOrder : OrderBase { }

public abstract class OrderBase : BaseEntity
{
    public ICollection<ServiceAssignment> Assignments { get; set; }
    public Sale Sale { get; set; }
}

public class Sale : BaseEntity
{
    public Employee Manager { get; set; }
    public Customer Customer { get; set; }
    public ICollection<ServiceOrder> ServiceOrders { get; set; }
    public ICollection<DeliveryOrder> DeliveryOrders { get; set; }
}

public class Employee : BaseEntity { }
public class Customer : BaseEntity { }

public abstract class BaseEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I've basically tried things like this and don't know where to start.

var tripsQuery = db.ServiceTrips

.Where(x => x.StartDate >= FirstDay && x.StartDate <= LastDay)

.Include(x => x.Crew)
.Include(x => x.ServiceAssignments)

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder))

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder.Sale))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder.Sale))

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder.Sale.Customer))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder.Sale.Customer))

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder.Sale.Manager))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder.Sale.Manager))
;

The model is simplified for the question. In production I'm pulling from about 20 tables. It takes about 10-15 seconds to load. I tried loading each day asynchronously but that increased the total time to load.

Upvotes: 1

Views: 566

Answers (1)

eightyeight
eightyeight

Reputation: 498

In my opinion when you are issuing a complex query that joins across twenty tables you might want to start of with inspecting the database itself

Here are some guidelines (some points are pertinent to SQL Server, which I have taken the liberty to assume is the database you are using)

  1. Check the query execution plan on the raw SQL - If it is time consuming to reproduce the entire EF code in SQL just maybe parts of it - for instance the ServiceTrips, ServiceAssignments and DeliveryOrder tables. This will give you an idea of the bottlenecks with regards to indexes etc

  2. Inspect if, because of the size of the data, network latency is the bottleneck and not the query itself

  3. Consider using indexed views which might improve your performance

  4. Architecture - To speed up such complex queries use caching of some sort maybe to store pertinent data - in your case maybe the names of the Managers, Crew etc.

Upvotes: 1

Related Questions