Entity Framework code first - Many to Many - Inclu

Question

I have two Entities Store and Catalog, having many to many relationship using fluent Api. I want to get a Store by id with all the catalogs having status equals to "Published". Below I try to write the following query but not getting the expected results.

var store = context.Stores.Include("Catalogs").Where(s => s.StoreID == id && s.Catalogs.Any(c => c.Status == "Published")).SingleOrDefault();

Answer 1

What you're asking for there is "give me the store with this ID, but only if it has a published catalog" (the "Any" call).

The easiest way to only get published catalogs would be to project them into an anonymous type:

var result = (from s in context.Stores
             where s.StoreID == id
             select new
             {
                 Store = s,
                 Catalogs = s.Catalogs.Where(c => c.Status == "Published")
             }).SingleOrDefault();

... or, in the fluent interface:

var result = context.Stores.Where(st => st.StoreID == id)
                           .Select(s => new 
                           {
                               Store = s,
                               Catalogs = s.Catalogs.Where(c => c.Status == "Published"),
                           }).SingleOrDefault();

So result.Catalogs holds all the published catalogs that apply to result.Store.