How to filter by fields from navigation properties in Entity Framework?

Question

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!

what exactly do you want? that descentantIds contains ANY of the Ids in the ProductsInCategories? all? the other way around? — DevilSuichiro, Jan 29 '16 at 14:54
I want to achive equivalent of SQL `WHERE ProductsInCategories.ProductCategoryID IN (1, 2, 3 .. n) `, but I don't see how can I get by lambda expression value of ProductCategoryID in my navigation property ProductsInCategories. In my case Where statement is `IQueryable` — Rafal Cypcer, Jan 29 '16 at 22:08
ProductionCategories is an ICollection of Products, which ProductCategoryID's do you want? — DevilSuichiro, Jan 29 '16 at 23:52

mariovalens · Accepted Answer · 2016-01-30T02:56:06.670

2

Try this

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

edited Jan 30 '16 at 02:56

answered Jan 30 '16 at 01:27

mariovalens

365
1
13

score 0 · Answer 2 · answered Jan 30 '16 at 09:48

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

How to filter by fields from navigation properties in Entity Framework?

2 Answers2