LINQ to Entity Framework Eager Loading issue

Question

I have the following query:

var MyQuery = from e in ContractContext.Equipments.Include("Manufacturers")
              where e.Customers.ID == customer.ID
              select e;

And everything works, I get my Equipments and it loads the Manufacturers table correctly (eagerly). But when I try to do the following many-to-many query:

var MyQuery = from e in ContractContext.Equipments.Include("Manufacturers")
              where e.Customers.ID == customer.ID
              from cce in e.ContractEquipments
              where cce.Contracts.EndedOn >= DateTime.Today
              select e;

where "ContractEquipments" is a many-to-many lookup between "Equipments" and "Contracts", but when this query runs, the Manufacturers table no longer gets easily loaded. Any idea how to fix this without doing the following:

if (MyEntity.Manufacturers.IsLoaded == false) 
   MyEntity.ManufacturersReference.Load()

This project takes hours execute and I want to keep the number of database calls down.

EDIT #1:

I also tried this without success:

var MyQuery = from e in ContractContext.Equipments.Include("Manufacturers")
              where e.Customers.ID == customer.ID
              join cce in ContractContext.ContractEquipments 
                on e.ID equals cce.Equipments.ID
              where cce.Contracts.EndedOn >= DateTime.Today
              select e;

Answer 1

Have you tried a join instead like this?

var MyQuery = from e in ContractContext.Equipments.Include("Manufacturers")
              where e.Customers.ID == customer.ID
              join cce in e.ContractEquipments on e.Id equals cce.EquipmentId
              where cce.Contracts.EndedOn >= DateTime.Today
              select e;

Answer 2

Early includes often get lost on some types of queries (i.e. with extra joins etc)

The way to get around this is to do the query, (and then so long as you are returning entities i.e. Select e rather than a projection i.e. Select new {...}) you can cast to ObjectQuery and do the include around the outside:

var MyQuery = ((from e in ContractContext.Equipments
              where e.Customers.ID == customer.ID
              from cce in e.ContractEquipments
              where cce.Contracts.EndedOn >= DateTime.Today
              select e) as ObjectQuery<Equipment>).Include("Manufacturers");

This should work.

If you are interested in more info on this, check out Tip 22 - How to make Include really Include

Alex