如何在实体框架中按导航属性中的字段进行筛选

本文关键字：字段 筛选 属性 实体 框架 导航 | 更新日期: 2023-09-27 18:32:36

我有这样的类： 

public class ProductInCategory
{
    public Guid Guid { get; set; }
    public long ProductID { get; set; }
    public long ProductCategoryID { get; set; }
    public virtual Product Product { get; set; }
    public virtual ProductCategory ProductCategory { get; set; }
}
public class Product
{
    public virtual ICollection<ProductInCategory> ProductsInCategories { get; set; }
    // and other fields and navigation properties not important for this example
}

现在我想执行查询，该查询使用实体框架获取所有产品，并预先加载特定的产品类别 ID： 

using (var db = new EntityDataModel())
{
    var node = db.Tree.FirstOrDefault(x => x.Guid == editedNode);
    List<long> descentantIds = db.Tree
                              .Where(x => x.AncestorID == node.AncestorID)
                              .Select(x => x.DescendantID).ToList();
    List<Product> products = db.Products
        .Include("Details")
        .Include("Prices")
        .Include("Prices.Currency")
        .Include("Prices.Seller")
        .Include("Translations")
        .Include("Translations.Language")
        .Include("ProductsInCategories")
        .Where(x => ... )) // how to filter by ProductsInCategories.ProductCategoryID (which in my case is descentantIds) ? 
        .ToList();
}

我认为我应该输入类似于 .Where(x => descentantIds.Contains(x.ProductsInCategories.ProductCategoryID)) 的 Where 子句，但这不起作用。

这是类似的解决方案，但我不知道如何在我的情况下应用它。

谢谢你的任何建议！

如何在实体框架中按导航属性中的字段进行筛选

试试这个 

 .SelectMany(x => x.ProductsInCategories.Where(c => descentantIds.Contains(c.ProductCategoryID))).Select(c => c.Product).Distinct()

虽然@mariovalens给出了解决我的问题的工作解决方案，但我找到了另一个。我正在粘贴它们两个。这可能对其他人有帮助;)

请注意，为了正确急切加载插入。Include（） 方法过滤后的方法，如 SelectMany（）、Select（）、Where（） 等输入。在这些方法之前，Include（） 将在导航属性中返回 null 值。

using (var db = new EntityDataModel())
{
    var node = db.Tree.FirstOrDefault(x => x.Guid == editedNode);
    List<long> descentantIds = db.Tree
               .Where(x => x.AncestorID == node.AncestorID)
               .Select(x => x.DescendantID)
               .ToList();
    List<Product> method1 = db.Products
        .SelectMany(x => x.ProductsInCategories.Where(c => descentantIds.Contains(c.ProductCategoryID))).Select(c => c.Product).Distinct()
        .Include(c => c.Assets.Select(c1 => c1.Translations.Select(c2 => c2.Language)))
        .Include(c => c.Tags.Select(c1 => c1.Translations.Select(c2 => c2.Language)))
        .Include(c => c.Details)
        .Include(c => c.Prices.Select(c1 => c1.Currency))
        .Include(c => c.Prices.Select(c1 => c1.Seller))
        .Include(c => c.Translations.Select(c1 => c1.Language))
        .Include(c => c.ProductsInCategories)
        .ToList();
    var method2 = (from product in db.Products
                 join productsInCategories in db.ProductsInCategories
                 on product.ID equals productsInCategories.ProductID
                 join productsCategories in db.ProductsCategories
                 on productsInCategories.ProductCategoryID equals productsCategories.ID
                 where descentantIds.Contains(productsInCategories.ProductCategoryID)
                 select product)
                 .Include(c => c.Assets.Select(c1 => c1.Translations.Select(c2 => c2.Language)))
                 .Include(c => c.Tags.Select(c1 => c1.Translations.Select(c2 => c2.Language)))
                 .Include(c => c.Details)
                 .Include(c => c.Prices.Select(c1 => c1.Currency))
                 .Include(c => c.Prices.Select(c1 => c1.Seller))
                 .Include(c => c.Translations.Select(c1 => c1.Language))
                 .Include(c => c.ProductsInCategories);
    var result = method2.ToList<Product>();
}
相关文章：