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
– DevilSuichiro, Commented Jan 29, 2016 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<Product> — Rafal Cypcer
– Rafal Cypcer, Commented Jan 29, 2016 at 22:08
ProductionCategories is an ICollection of Products, which ProductCategoryID's do you want? — DevilSuichiro
– DevilSuichiro, Commented Jan 29, 2016 at 23:52

mariovalens · Accepted Answer · 2016-01-30 02:56:06Z

2

Try this

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

edited Jan 30, 2016 at 2:56

answered Jan 30, 2016 at 1:27

mariovalens

3651 silver badge15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rafal Cypcer · Accepted Answer · 2016-01-30 09:48:39Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related