C# Linq to Entities 多对多选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1097992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq to Entities many to many select query
提问by Peter
I am at a loss with the following query, which is peanuts in plain T-SQL.
我对以下查询不知所措，这是普通 T-SQL 中的花生。
We have three physical tables:
我们有三个物理表：
- Band (PK=BandId)
- MusicStyle (PK=MuicStyleId)
- BandMusicStyle (PK=BandId+MusicStyleId, FK=BandId, MusicStyleId)
- 频段 (PK=BandId)
- 音乐风格 (PK=MuicStyleId)
- BandMusicStyle (PK=BandId+MusicStyleId, FK=BandId, MusicStyleId)
Now what I'm trying to do is get a list of MusicStyles that are linked to a Band which contains a certain searchstring in it's name. The bandname should be in the result aswell.
现在我要做的是获取链接到 Band 的 MusicStyles 列表，该乐队的名称中包含某个搜索字符串。乐队名也应该在结果中。
The T-SQL would be something like this:
T-SQL 将是这样的：
SELECT b.Name, m.ID, m.Name, m.Description
FROM Band b
INNER JOIN BandMusicStyle bm on b.BandId = bm.BandId
INNER JOIN MusicStyle m on bm.MusicStyleId = m.MusicStyleId
WHERE b.Name like '%@searchstring%'
How would I write this in Linq To Entities?
我将如何在 Linq To Entities 中编写此内容？
PS: StackOverflow does not allow a search on the string 'many to many' for some bizar reason...
PS：由于某些奇怪的原因，StackOverflow 不允许搜索字符串“多对多”......
采纳答案by Peter
This proved to be much simpler than it seemed. I've solved the problem using the following blogpost: http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx
事实证明，这比看起来简单得多。我已经使用以下博客文章解决了这个问题：http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx
The key to this solution is to apply the filter of the bandname on a subset of Bands of the musicstyle collection.
此解决方案的关键是将乐队名称的过滤器应用于音乐风格集合的乐队的子集。
var result=(from m in _entities.MusicStyle
from b in m.Band
where b.Name.Contains(search)
select new {
BandName = b.Name,
m.ID,
m.Name,
m.Description
});
notice the line
注意这条线
from b IN m.Band
This makes sure you are only filtering on bands that have a musicstyle.
这确保您只过滤具有音乐风格的乐队。
Thanks for your answers but none of them actually solved my problem.
感谢您的回答，但没有一个真正解决了我的问题。
回答by xandy
In Linq, actually you don't need to write anything, if you define the relation in the diagram in SQL database, and generated using the utility, the object hierarchy is built automatically. That means, if you do:
在Linq 中，实际上你不需要写任何东西，如果你在SQL 数据库中定义图表中的关系，并使用实用程序生成，对象层次结构将自动构建。这意味着，如果你这样做：
var bands = from ms in db.MusicStyle
let b = ms.Bands
where b.Name.Contains(SEARCHSTRING)
select new {
b.Name, ms.Name,
ms.ID, ms.Description};
If you look into the generated classes of entities, the BandMusicStyle should not appear as LINQ to Entities consider that Band and MusicStyle are many to many and that table is not necessary.
如果您查看生成的实体类，BandMusicStyle 不应显示为 LINQ to Entities 认为 Band 和 MusicStyle 是多对多的，并且该表不是必需的。
See if it works?
看看有没有效果？
回答by Craig Stuntz
from ms in Context.MusicStyles
where ms.Bands.Any(b => b.Name.Contains(search))
select ms;
This just returns the style, which is what your question asks for. Your sample SQL, on the other hand, returns the style and the bands. For that, I'd do:
这只是返回样式，这就是您的问题所要求的。另一方面，您的示例 SQL 返回样式和带区。为此，我会这样做：
from b in Context.Bands
where b.Name.Contains(search)
group b by band.MusicStyle into g
select new {
Style = g.Key,
Bands = g
}
from b in Context.Bands
where b.Name.Contains(search)
select new {
BandName = b.Name,
MusicStyleId = b.MusicStyle.Id,
MusicStyleName = b.MusicStyle.Name,
// etc.
}
回答by Ciaran O'Neill
You could do the above, but that will bring back all your results once you start to iterate over them and the filtering is done in-memory rather than on the db.
您可以执行上述操作，但是一旦您开始迭代它们并且过滤是在内存中而不是在数据库上完成，这将带回所有结果。
I think what you're looking for is just a few joins?
我想你要找的只是几个连接？
var q = from b in db.Bands
join bm in db.BandMusicStyle on on b.BandId equals bm.BandId
join ms in db.MusicStyle on bm.MusicStyleId equals m.MusicStyleId
where b.Name.Contains(searchString)
select new { b.Name, ms.ID, ms.Name, ms.Description };
or something to this effect anyway
或无论如何
