Linq-to-entities - Include（）方法未加载

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

如果我使用连接，则Include（）方法不再有效，例如：

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

e.Properties未加载

Without the join, the Include() works

没有连接，Include（）可以正常工作

Lee

背风处

5 个解决方案

#1

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

更新：实际上我最近添加了另一个提示，涵盖了这一点，并提供了另一种可能更好的解决方案。我们的想法是延迟使用Include（）直到查询结束，请参阅此内容以获取更多信息：提示22 - 如何使include真正包含

---

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

使用Include（）时，实体框架存在已知限制。 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.

有关详细信息，请参阅我的EF提示系列中的提示1。

#2

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

#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:

那么“实体”上与“Item.Member”相关的导航属性的名称是什么（即，是导航的另一端）。您应该使用此而不是连接。例如，如果“entity”添加一个名为Member的属性，其基数为1，而Member有一个名为Items的属性，其基数为多，则可以执行以下操作：

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.

我在这里猜测你的模型的属性，但这应该给你一般的想法。在大多数情况下，在LINQ to Entities中使用join是错误的，因为它表明您的导航属性未正确设置，或者您没有使用它们。

#4

So, I realise I am late to the party here, however I thought I'd add my findings. This should really be a comment on Alex James's post, but as I don't have the reputation it'll have to go here.

所以，我意识到我在这里参加派对很晚，但我想我会加上我的发现。这应该是对亚历克斯詹姆斯的帖子的评论，但由于我没有声誉，所以必须去这里。

So my answer is: it doesn't seem to work at all as you would intend. Alex James gives two interesting solutions, however if you try them and check the SQL, it's horrible.

所以我的答案是：它根本不像你想要的那样起作用。亚历克斯詹姆斯给出了两个有趣的解决方案，但是如果你尝试它们并检查SQL，那就太可怕了。

The example I was working on is:

我正在研究的例子是：

        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 the two examples. Without the include it produces the perfectly respectable sql:

这遵循两个例子中较简单的一个。没有包含它会产生完全可敬的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:

但是，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

Total garbage. The key point to note here is the fact that it returns the outer joined version of the table which has not been limited by status=1.

垃圾总量。这里要注意的关键点是它返回表的外连接版本，该版本不受status = 1的限制。

This results in the WRONG data being returned:

这会导致返回错误的数据：

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

Note that the status of 2 is being returned there, despite our restriction. It simply does not work. If I have gone wrong somewhere, I would be delighted to find out, as this is making a mockery of Linq. I love the idea, but the execution doesn't seem to be usable at the moment.

请注意，尽管我们有限制，但仍会返回2的状态。它根本不起作用。如果我在某个地方出错，我会很高兴发现，因为这是对Linq的嘲弄。我喜欢这个想法，但目前执行似乎并不可用。

---

Out of curiosity, I tried the LinqToSQL dbml rather than the LinqToEntities edmx that produced the mess above:

出于好奇，我尝试了LinqToSQL dbml而不是产生上面乱七八糟的LinqToEntities edmx：

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]

Slightly more compact - weird count clause, but overall same total FAIL.

略微更紧凑 - 怪异的计数条款，但整体相同的总失败。

Has anybody actually ever used this stuff in a real business application? I'm really starting to wonder... Please tell me I've missed something obvious, as I really want to like Linq!

有没有人在真正的商业应用程序中使用过这些东西？我真的开始怀疑......请告诉我，我错过了一些明显的东西，因为我真的很想要Linq！

#5

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!

编辑：更改了第一句，因为它不正确。感谢指针评论！