当使用group by时，LINQ从一个表中获取另一个表中不存在的行? [英] LINQ get rows from a table that don't exist in another table when using group by?

问题描述

我有两本三张桌子的书，分别是作者和小组.

I have two three tables books, authors and groups.

books:bookId，名称，authorId....

books: bookId, name, authorId....

作者:authorId，firstName，LastName ....

authors: authorId, firstName, LastName....

组:groupID，名称，authorId ...

groups: groupID, name, authorId...

我需要选择所有不属于任何组并且在两个日期之间出版的书籍不少于3本书的所有 authorIds .

i need to select all authorIds that doesn't belong to any groups and doesn't have less than 3 books published between two dates.

 List<int> authorId= new List<int>(db.Books.GroupBy(x => x.authorId)
                .Where(x => x.Any(y => y.datePublished <= now && y.datePublished >= date) && x.Count() > 2)
                .Select(x => x.FirstOrDefault().authorId)).Distinct().ToList();

推荐答案

我想这会起作用:

List<int> authorId = (List<int>)db.Books
    .Where(x => x.datePublished >= InitialDate)
    .Where(x => x.datePublished <= FinalDate)
    .GroupBy(x => x.authorId)
    .Select(g => new { authorId = g.Key, count = g.Count() })
    .Where(g => g.Count >= 3)
    .Select(g.authorId);

我迈出了每一步，以便您可以更好地看到它，并根据需要随意进行优化.这里的关键是 new {authorId = g.Key，count = g.Count()} .

I make every single step so you can see it better, fell free to optimized as needed. The key thing here is the new { authorId = g.Key, count = g.Count() }.

这篇关于当使用group by时，LINQ从一个表中获取另一个表中不存在的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！