实体框架子查询 [英] Entity Framework subquery

问题描述

伙计们，我是Entity Framework的新手，但有一段时间我一直想解决这个问题.基本上，我有4个实体:用户，组，书籍和readingLists.用户可以加入一个小组，并且一个小组包含书籍(由readingList定义).我正在尝试显示特定组的书籍列表，SQL看起来像这样:

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

我通过从UserRepository中查询当前用户来确定正在搜索的GroupID，并且当前按组获取图书"方法看起来像这样:

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.

谢谢

推荐答案

我尚未对其进行测试，但希望它能够正常工作.

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

这篇关于实体框架子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！