Linq-to-entities - Include() method not loading

Question

Hi

If I use a join, the Include() method is no longer working, eg:

from e in dc.Entities.Include("Properties")
join i in dc.Items on e.ID equals i.Member.ID
where (i.Collection.ID == collectionID) 
select e

e.Properties is not loaded

Without the join, the Include() works

Lee

Answer 1

Try the more verbose way to do more or less the same thing obtain the same results, but with more datacalls:

var mydata = from e in dc.Entities
             join i in dc.Items 
                 on e.ID equals i.Member.ID 
             where (i.Collection.ID == collectionID) 
             select e;

foreach (Entity ent in mydata) {
    if(!ent.Properties.IsLoaded) { ent.Properties.Load(); }
}

Do you still get the same (unexpected) result?

EDIT: Changed the first sentence, as it was incorrect. Thanks for the pointer comment!

Answer 2

Once again I would recommend Linq pad. Helps track if it's really the Include that is the problems. I doubt it.

Answer 3

So what is the name of the navigation property on "Entity" which relates to "Item.Member" (i.e., is the other end of the navigation). You should be using this instead of the join. For example, if "entity" add a property called Member with the cardinality of 1 and Member had a property called Items with a cardinality of many, you could do this:

from e in dc.Entities.Include("Properties")
where e.Member.Items.Any(i => i.Collection.ID == collectionID) 
select e

I'm guessing at the properties of your model here, but this should give you the general idea. In most cases, using join in LINQ to Entities is wrong, because it suggests that either your navigational properties are not set up correctly, or you are not using them.

Answer 4

UPDATE: Actually I recently added another Tip that covers this, and provides an alternate probably better solution. The idea is to delay the use of Include() until the end of the query, see this for more information: Tip 22 - How to make include really include

---

There is known limitation in the Entity Framework when using Include(). Certain operations are just not supported with Include.

Looks like you may have run into one on those limitations, to work around this you should try something like this:

var results = 
   from e in dc.Entities //Notice no include
   join i in dc.Items on e.ID equals i.Member.ID
   where (i.Collection.ID == collectionID) 
   select new {Entity = e, Properties = e.Properties};

This will bring back the Properties, and if the relationship between entity and Properties is a one to many (but not a many to many) you will find that each resulting anonymous type has the same values in:

anonType.Entity.Properties
anonType.Properties

This is a side-effect of a feature in the Entity Framework called relationship fixup.

See this Tip 1 in my EF Tips series for more information.

Alex

Answer 5

Try this:

var query = (ObjectQuery<Entities>)(from e in dc.Entities
            join i in dc.Items on e.ID equals i.Member.ID
            where (i.Collection.ID == collectionID) 
            select e)

return query.Include("Properties")