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!

Answer 1

Try this

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