Linq-to-entities - Include () method does not load

Question

If I use union, the Include () method no longer works, for example:

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 not loaded

Without combining, the Include () function works

Lee

Answer 1

UPDATE: Actually, I recently added another tip that covers this and provides an alternative, probably the best solution. The idea is to defer the use of Include () until the end of the query, see This for more information: Tip 22 - How to enable include include include

---

Known limitation in Entity Framework when using Include (). Some operations are simply not supported with Include.

It looks like you are facing one of these limitations, to get 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 return the properties, and if the relationship between the object and the properties is from one to many (but not many of many), you will find that each resulting anonymous type has the same meanings:

 anonType.Entity.Properties anonType.Properties 

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

See Tip 1 in my EF series of tips for more information.

Answer 2

Try the following:

 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") 

Answer 3

So, what is the name of the navigation property in "Entity", which refers to "Item.Member" (ie is the other end of the navigation). You should use this instead of connecting. For example, if "entity" adds a "Member" property with a power of 1, and a member has a "Elements with a power of many" property, you can do this:

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

I guess the properties of your model here, but this should give you a general idea. In most cases, using a connection in LINQ to Entities is incorrect, because it assumes that either your navigation properties are not configured correctly, or you are not using them.

Answer 4

So, I understand that I'm late for the party here, but I thought I'd add my conclusions. It really should be a comment on Alex James's post, but since I have no reputation, he will have to go here.

So my answer is: it doesn't seem to work at all as you would like. Alex James gives two interesting solutions, however, if you try them and test SQL, this is terrible.

As an example, I worked:

  var theRelease = from release in context.Releases where release.Name == "Hello World" select release; var allProductionVersions = from prodVer in context.ProductionVersions where prodVer.Status == 1 select prodVer; var combined = (from release in theRelease join p in allProductionVersions on release.Id equals p.ReleaseID select release).Include(release => release.ProductionVersions); var allProductionsForChosenRelease = combined.ToList(); 

This follows the simpler of two examples. Without inclusion, it creates a perfectly respectable sql:

 SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Releases] AS [Extent1] INNER JOIN [dbo].[ProductionVersions] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ReleaseID] WHERE ('Hello World' = [Extent1].[Name]) AND (1 = [Extent2].[Status]) 

But with, OMG:

 SELECT [Project1].[Id1] AS [Id], [Project1].[Id] AS [Id1], [Project1].[Name] AS [Name], [Project1].[C1] AS [C1], [Project1].[Id2] AS [Id2], [Project1].[Status] AS [Status], [Project1].[ReleaseID] AS [ReleaseID] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[Id] AS [Id1], [Extent3].[Id] AS [Id2], [Extent3].[Status] AS [Status], [Extent3].[ReleaseID] AS [ReleaseID], CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Releases] AS [Extent1] INNER JOIN [dbo].[ProductionVersions] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ReleaseID] LEFT OUTER JOIN [dbo].[ProductionVersions] AS [Extent3] ON [Extent1].[Id] = [Extent3].[ReleaseID] WHERE ('Hello World' = [Extent1].[Name]) AND (1 = [Extent2].[Status]) ) AS [Project1] ORDER BY [Project1].[Id1] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC 

General rubbish. The key point here is the fact that it returns an external merged version of the table that was not limited by status = 1.

This results in the return of WRONG data:

 Id Id1 Name C1 Id2 Status ReleaseID 2 1 Hello World 1 1 2 1 2 1 Hello World 1 2 1 1 

Please note that status 2 is returned there, despite our restriction. It just doesn't work. If I am mistaken somewhere, I would be glad to know, as it taunts Link. I like this idea, but execution is currently not possible.

---

Out of curiosity, I tried using the LinqToSQL dbml, not the LinqToEntities edmx, which created the mess above:

 SELECT [t0].[Id], [t0].[Name], [t2].[Id] AS [Id2], [t2].[Status], [t2].[ReleaseID], ( SELECT COUNT(*) FROM [dbo].[ProductionVersions] AS [t3] WHERE [t3].[ReleaseID] = [t0].[Id] ) AS [value] FROM [dbo].[Releases] AS [t0] INNER JOIN [dbo].[ProductionVersions] AS [t1] ON [t0].[Id] = [t1].[ReleaseID] LEFT OUTER JOIN [dbo].[ProductionVersions] AS [t2] ON [t2].[ReleaseID] = [t0].[Id] WHERE ([t0].[Name] = @p0) AND ([t1].[Status] = @p1) ORDER BY [t0].[Id], [t1].[Id], [t2].[Id] 

A little more compact is the weird count argument, but a common common FAIL.

Has anyone really used this material in a real business application? I'm really starting to wonder ... Please tell me that I missed something obvious since I really like Linq!

Answer 5

Try a more detailed way to do more or less the same thing to get 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(); } } 

Are you still getting the same (unexpected) result?

EDIT . The first sentence has been changed since it was incorrect. Thanks for the comment pointer!