如何从实体框架中的导航属性中通过字段过滤?[英] How to filter by fields from navigation properties in Entity Framework?

本文是小编为大家收集整理的关于如何从实体框架中的导航属性中通过字段过滤?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有类似:

的课程
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
}

现在,我想执行查询,该查询使用具有特定ProductCategoryId的急切加载的实体框架获取所有产品:

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))的子句的位置,但这行不通.

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

谢谢您的建议!

推荐答案

尝试此

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

其他推荐答案

尽管@mariovalens提供了解决方案,从而解决了我的问题,但我找到了另一个问题.我粘贴了他们两个.这可能对他人有帮助;)

请注意,在过滤方法之后,在selectMany(),select(),where()等过滤方法之后,要插入.include()等,inputing .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>();
}

本文地址:https://www.itbaoku.cn/post/1557100.html

问题描述

I have classes like:

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
}

And now I want to execute query which gets all products using Entity Framework with eager loading with specific ProductCategoryID's:

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();
}

I think that I should to type in Where clause something similar to .Where(x => descentantIds.Contains(x.ProductsInCategories.ProductCategoryID)), but this won't work.

Here is similar solution, but I don't know how to apply it in my case.

Thank you for any advice!

推荐答案

Try this

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

其他推荐答案

Although @mariovalens gave working solution which solved my issue I found another one. I'm pasting both of them. It might be helpful for others ;)

Note that for proper eager loading insert .Include() methods after filtering methods like SelectMany(), Select(), Where() etc. Inputing .Include() before those methods will return null values in navigation properties.

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>();
}