Entity Framework subquery
Solution 1
I haven't tested it but hopefully it will work.
entities.Books.Where(
b => entities.ReadingList.
Where(rl => rl.GroupId == groupId).
Select(rl => rl.BookId).
Contains(b.BookId)
)
Solution 2
Personally I think there is a better solution(untested of course):
First select from ReadList by GroupdID, then join in books on BookID.
IQueryable<Book> q =
from rl in entities.ReadingList
join b in entities.Books on rl.BookID equals b.BookID
where rl.GroupdID ==groupID
select b;
var books = q.ToList()
Please let me know if you have any issues.
Related videos on Youtube
Comments
-
Apollo almost 2 years
Guys I am new to Entity Framework and I'm having a bt of a problem that I have been trying to solve for quite a while. Basically I have 4 entities: users, groups, books and readingLists. A user can join a group and a group contains books - defined by readingList. I am trying to display a list of books for a specific group, the SQL looks like this:
SELECT * FROM Books b WHERE b.Id IN ( SELECT BookID FROM ReadingList rl WHERE rl.GroupID = '3')
I determine the GroupID being searched by querying the current user from a UserRepository and currently the 'get books by group' method is looking like this:
// Get books by group public IQueryable<Book> GetGroupBooks(string username) { UserRepository userRepository = new UserRepository(); int groupId = userRepository.GetUserGroupId(username); IQueryable<Book> q = from b in entities.Books where b.Id == 7 // temp - these values should be determined by // rl in entites.ReadingList select rl.BookID where r.GroupID == groupID select b; return q; }
Obviously this is a temporary measure and only returns one book, but I have included it for reference. Any help or advice here would be much appreciated.
Thanks
-
Apollo about 13 yearsThank you so much! That worked perfectly, really appreciate your time.
-
Nix about 13 yearsYou might want to explain what you are doing here, bc it isn't efficient. You are doing a sub query, but you are going to execute the subquery for every book.
-
Apollo about 13 yearsI should add ReadingList contains two FKs, one to Books and one to Groups. ReadingList contains a composite PK (BookID and GroupID) ensuring that no book can appear more than once for the same group.
-
Mayank about 13 years@Nix AFAIK, the compiler is smart enough to understand how to optimize the process. Thus the sub-query won't be executed for every book but will first check in the memory for a particular group.
-
Nix about 13 yearsCompiler isn't doing it its EF behind the scenes. First will be a pull from db, the rest will be an inmemory search. I am sure you wont have tons of data, so it wont be an issue, but I feel like it should be noted.
-
Mayank about 13 years+1 For a more verbose approach. Personally I prefer the short hand way. :) But why the
Distinct
? -
Nix about 13 yearsyou are right for some reason i was thinking about ReadingList.. don't need it.
-
Jonathan Levison over 10 yearsJust tested with EF 5, query generated was an efficient query identical to 'where in (Select..)' optimization wise.
-
Nekresh almost 9 yearsThe
Distinct
was there to de-duplicate Books, as it may be returned multiple times.