c# - 比较 Entity Framework 内的 2 个列表 Where 子句

Question

我有 3 个 Entity Framework 实体:

public class Tag {
  public Int32 Id { get; set; }
  public String Name { get; set; }
}

public class Book {
  public Int32 Id { get; set; }
  public String Name { get; set; }
  public IList<Tag> Tags { get; set; }
}

public class Post {
  public Int32 Id { get; set; }
  public String Name { get; set; }
  public IList<Tag> Tags { get; set; }
}

给定一本书，我需要获取与该书具有完全相同标签的所有帖子。

IList<Tags> tags = new List<Tag> { new Tag { Id = 1 }, new Tag { Id = 2 } };

Book book = new Book { Tags = tags };

context.Posts.Where(x => 
  new HashSet<Int32>(x.Tags.Select(y => y.Id)).SetEquals(book.Tags.Select(y => y.Id)))
.ToList();

我收到以下错误:

An exception of type 'System.NotSupportedException' occurred in mscorlib.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'Boolean SetEquals(System.Collections.Generic.IEnumerable`1[System.Int32])' method, and this method cannot be translated into a store expression.

知道如何解决这个查询吗？

谢谢你，米格尔

Answer 1

from p in context.Posts
let tags = context.Books(b=>b.ID == bookId).Select(b=>b.Tags)//you might need .SelectMany
where p.Tags.All(t=>tags.Any(bt=>bt.Id == t.Id))
select p

如果只做 lambda 表达式，一开始获取标签可能会更好，甚至在第一个选项中可能会更好。

var tags = context.Books(b=>b.ID == bookId).Select(b=>b.Tags)//you might need .SelectMany

var result = context.Posts.Where(p=>p.Tags.All(t=>tags.Any(bt=>bt.Id == t.Id)).ToList();