Get group by records from many to many relationship table

c# entity-framework-6 linq many-to-many sql-server

Question

I have 3 tables, Person | Book | PersonBook
PersonBook table is relaton table between Person and 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

I want to get the person who read book id 2, 3, 4. therefore result should be 3.

Whats have i tried

I manage to write sql query to get the result. but i want Linq query for this.
My sql query is

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

sql query if working fine. but i want to use linq query for this. Any help will be appriciated

Update

This query works for me but i don't think it is optimized. can anyone help me to optimize this query.

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;
1
0
6/2/2015 12:28:05 PM

Popular Answer

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});
4
5/22/2015 12:49:22 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow