从多对多关系表中获取分组记录

Question

我有3个表，Person | Book | PersonBook
PersonBook表为Person与Book的关系表

Person          Book                    PersonBooks
Id   Name       Id     Name             Id   PersonId   BookId
1    rick        1      SQL              1     1          1
2    phil        2      Asp.Net          2     1          3
3    scott       3      MySql            3     2          4 
                 4      C#               4     3          2
                                         5     3          3
                                         6     3          4

我想要得到阅读编号为2,3,4的书的人。因此结果应为3.

我试过什么了

我设法写sql查询得到的结果。但我想Linq查询这个。
我的sql查询是

SELECT DISTINCT(PersonId) From PersonBooks
WHERE BookId IN (2, 3, 4)
GROUP BY PersonId
HAVING COUNT (*) = 3

sql查询是否工作正常。但我想用linq查询。如有任何帮助，不胜感激

更新

这个查询适用于我，但我不认为它是优化的。谁能帮我优化一下这个查询?

from p in Context.Book where (Context.PersonBooks.Where(x => personIds.Contains(x.PersonId))
    .GroupBy(x => x.BookId)
    .Where(x=>x.Count() == personIds.Count)
    .OrderByDescending(x => x.Count())
    .Select(x => x.Key).ToList()).Contains(p.BookId) select p;

Answer 1

var person = (from p in PersonBooks
                  join b in Book on p.BookId equals b.BookId
                  where b.BookId  IN (2, 3, 4)
                  select new { p.PersonId});

Answer 2

像往常一样，人们在LINQ中跳转到连接语法，但您绝对应该使用导航属性:

var ids = new[] { 2, 3, 4 };
var persons = from p in db.Persons
              where p.PersonBooks.All(pb => ids.Contains(pb.BookId))
                 && p.PersonBooks.Count() == 3
              select p;

这将转换为EXIST子句，因此您不再需要Distinct。如您所需要，它还返回Person s，而不仅仅是id。

顺便说一下，如果您希望人们至少阅读一本(不是所有)书籍，您可以将All替换为Any。

Answer 3

试试这个…

from bp in BookPerson
join p in person on p.personId== bp.personId
join b in books on b.bookId == bp.bookId
where b.bookId = 1 && b.bookId = 2 && b.bookId = 3
group p by bp.personId into p
select new {g.personId};