LINQ 分组依据和不在

Question

我有一个如下所示的架构（这是一个简化的示例，因此请忽略明显的架构问题）：

table Books
{
    string bookId
}

table students_books
{
    string studentname
    string bookId
}

目的是找出阅读次数少于 500 次的书籍。不幸的是，我无法将这个数字保留在图书表中。

我正在编写这样的查询：

from book in Books
    where !(from student in students_books
        group student by student.bookId into GroupedPerStudent
        where GroupedPerStudent.Count() >= 500 
        select new { bookname = GroupedPerStudent.Key }).Contains(book.bookid)
    select book 

我在 Contains() 上收到编译错误。查询出了什么问题？

无法从用法中推断出方法“System.Linq.Enumerable.Contains(System.Collections.Generic.IEnumerable, TSource)”的类型参数。尝试显式指定类型参数。

Answer 1

您的嵌套查询正在选择匿名类型 - 您应该选择图书 ID：

from book in Books
  where !(from student in students_books
          group student by student.bookId into GroupedPerStudent
          where GroupedPerStudent.Count() >= 500 
          select GroupedPerStudent.Key).Contains(book.bookid)
select book;

但我会以更积极的方式表达它 - 实际上用两个单独的语句以使其更清晰：

var rarelyRead = from student in student_books
                 group student by student.bookId into grouped
                 where grouped.Count() < 5000
                 select grouped.Key;

var query = Books.Where(book => rarelyRead.Contains(book.book_id));

编辑：或者，根据 Snowbear 的建议，使用join，我们将把它与查询延续一起使用以获得乐趣：

var query = from student in student_books
            group student by student.bookId into grouped
            where grouped.Count() < 5000
            select grouped.Key into rarelyRead
            join book in Books on rarelyRead equals book.book_id
            select book;

Answer 2

您在嵌套查询中选择匿名类型，请将其替换为 select GroupedPerStudent.Key：

   ...
   where GroupedPerStudent.Count() >= 500 
         select GroupedPerStudent.Key).Contains(book.bookid)

尽管我会重写整个查询：

var popularBooks = students_books
                      .GroupBy(b => b.bookId)
                      .Where(g => g.Count() >= 500)
                      .Join(Books, students_book => students_book.Key, b => b.bookId,
                                  (students_books, book) => book);