Why you're only getting sugar products.
But it is not returning all the product items instead of sugar only products.
Of course it is. Because you're asking it to only give you the sugar products:
var brands = _context.Brands
.Select(b => new
{
b,
FoodCategories = b.FoodCategories
.Where(x => x.BrandId == b.BrandId)
.Select(c => new
{
c,
Products = c.Products
.Where(y => y.FoodCategoryId == c.FoodCategoryId
&& y.Sugar) //HERE!
.Select(p => new
{
p,
File = p.FileDetail
})
})
})
.AsEnumerable()
.Select(z => z.b)
.ToList();
If you want all products; then don't filter on only the ones where Sugar is set to true.
There is a lot of redundant code here.
b.FoodCategories.Where(x => x.BrandId == b.BrandId)
b.FoodCategories already expresses the food categories of this particular brand b. You don't need the Where.
The same applies to
c.Products.Where(y => y.FoodCategoryId == c.FoodCategoryId ... )
Here's an improved version of your (second) snippet:
var brands = _context.Brands
.Select(b => new
{
b,
FoodCategories = b.FoodCategories
.Select(c => new
{
c,
Products = c.Products
.Select(p => new
{
p,
File = p.FileDetail
})
})
})
.AsEnumerable()
.Select(z => z.b)
.ToList();
This should make it clearer that the custom Select logic isn't necessary. All you're doing is loading the related entities into properties of the same name. You can simply rely on the existing entities and their relations, there's no reason to define the same relationship again.
The only reason a custom Select would be desirable here was if:
- You wanted to limit the retrieved columns in order to lower the data size (useful for large queries)
- You want to selectively load children, not just all related children. Your code suggest that you want this, but then you say "But it is not returning all the product items" so I conclude that you don't want to filter the products on their sugar content.
Why your Include didn't work.
SImply put: you cannot use Where statements in includes.
Include statements are based on the structure of the entities, whereas a Where only filters data from a set. One has nothing to do with the other.
And even though you'd think it'd be nice to do something like "include the parent only if they have an active status", that's simply not how Include was designed to work.
Include boils down to "for every [type1], also load their related [type2]". This will be done for every [type1] object that your query will instantiate and it will load every related [type2].
Taking the next step in refactoring the above snippet:
var brands = _context.Brands
.Include(b => b.FoodCategories)
.Include(b => b.FoodCategories.Select(fc => fc.Products))
.Include(b => b.FoodCategories.Select(fc => fc.Products.Select(p => p.FileDetail)))
.ToList();
The includes give Entity Framework specific instructions:
- For every loaded brand, load its related food categories.
- For every loaded food category, load its related products.
- For every loaded product, load its related file details.
Notice that it does not instruct WHICH brands should be loaded! This is an important distinction to make. The Include statements do not in any way filter the data, they only explain what additional data needs to be retrieved for every entry that will be loaded.
Which entries will be loaded has not been defined yet. By default, you get the whole dataset, but you can apply further filtering using Where statements before you load the data.
Think of it this way:
A restaurant wants every new customer's mother to give permission to serve dessert to the customer. Therefore, the restaurant drafts a rule: "every customer must bring their mother".
This is the equivalent of db.Customers.Include(c => c.Mother).
This does not state which customers are allowed to visit the restaurant. It only states that any customer that visits the restaurant must bring their mother (if they have no mother, they will bring null instead).
Notice how this rule applies regardless of which customers visit the restaurant:
- Ladies night:
db.Customers.Include(c => c.Mother).Where(c => c.IsFemale)
- Parents night:
db.Customers.Include(c => c.Mother).Where(c => c.Children.Any())
- People whose father is named Bob night:
db.Customers.Include(c => c.Mother).Where(c => c.Father.Name == "Bob")
Take note of the third example. Even though you filter on the father, you will only load the mother entity. It's perfectly possible to filter items on related entity values without actually loading the entities themselves (fathers).
You may ask yourself "why Select?". That's a good question, because it's not intuitive here.
Ideally, you'd want to do something like
context.Brand.Include(b => b.FoodCategories.Products.FileDetails)
But this is not possible because of a limitation in the language. FoodCategories is a List<FoodCategory>, which does not have a Products property.
However, FoodCategory itself does have a Products property. This is why Select is used: it allows you to access the properties of the list element's type, rather than the list itself.
Internally, EF is going to deconstruct your Select statement (which is an Expression) and it will figure out which property you want to be loaded. Don't worry too much about how EF works behind the scenes. It's not always pretty.
The Include/Select syntax is not the prettiest. Especially when you drill down multiple levels, it becomes cumbersome to write (and read).
So I suggest you invert your approach (start at the lowest child, drill up to the parent). Technically, it yields the same result, but it allows for a neater Include syntax:
var brands = context.FileDetails
.Include(fd => fd.Product)
.Include(fd => fd.Product.FoodCategory)
.Include(fd => fd.Product.FoodCategory.Brand)
.Select(fd => fd.Product.FoodCategory.Brand)
Now you don't need any nasty Select workaround in order to reference the related types.
Do note that you need to put an Include for every step! You can't just use the last Include and skip the others. EF does not infer that it needs to load multiple relations from a single Include.
Note that this trick only really works if you have a chain of one-to-many relationships. Many-to-many relationships make it harder to apply this trick. At worst, you'll have to resort to using the Select syntax from the earlier example.
While I am not a fan of the Include methods that take a string parameter (I don't like hardcoded strings that can fail on typos), I do feel it's relevant to mention here that they do not suffer from this issue. If you use the string-based includes, you can do things like:
context.Brands
.Include("FoodCategories")
.Include("FoodCategories.Products")
.Include("FoodCategories.Products.FileDetails")
The parsing logic of the string include method will automatically look for the element inside the List, thereby effectively preventing the ugly syntax.
But there are other reasons why I generally don't advise using string parameters here (doesn't update when you rename a property, no intellisense, very prone to developer error)
