How to apply where clause on ICollection LINQ result?

Question

I have three class in my project:

public partial class Product
{
 public string? Title { get; set; }
 public string? UrlAddress { get; set; }
 public virtual ICollection<ProductSize> ProductSizes { get; set; }
 public virtual Picture? Picture { get; set; }
 
}


public partial class ProductSize
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int SizeId { get; set; }

    public virtual Product Product { get; set; } = null!;
    public virtual Size Size { get; set; } = null!;
}


public partial class Size
{
  public int Id { get; set; }
  public int? ProductTypeId { get; set; }
  public string Name { get; set; } = null!;
}

And try to get something result with this linq query:

var result =   _tebpooshContext.Products
   .Where(p => p.ProductCategoryId == 1
            && p.ProductTypeId == 2
   )
   
   .Include(p => p.Picture)
   .Include(p => p.ProductSizes)
   .ThenInclude(p => p.Size)
   
   .Select(p => new TileProductDto
   {
       Image = p.Picture.Url.Replace("SYSTEM_TYPE_SYNCSERVER", "URL"),
       Name = p.Name.Trim(),
       Price = p.Price.ToString(),
       ProductSizes = p.ProductSizes,
       ProdutId = p.Id.ToString(),


   })

   .Take(9).ToList();

Now try to write this where clause before Take(9) with this syntax:

.Where(p => p.ProductSizes.Where(p => p.Size.Name == "42"))

but get this error :

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<TebWebApp.models.entities.ProductSize>' to 'bool'    

Try to fix that error with this way :

.Where(p => p.ProductSizes.Any(p => p.Size.Name == "42"))

But that where clause return all product size and size "42" dont effect on where clause.

How can i apply my where clause before Take(9) ?

Answer 1

First of all, Includes are ignored when the query ends in a projection (i.e. a Select(x => new ...) statement. They should be removed (code should not contain redundancies).

What you want can be implemented by adding a Where in the projection:

var result = _tebpooshContext.Products
   .Where(p => p.ProductCategoryId == 1
            && p.ProductTypeId == 2)
   .Select(p => new TileProductDto
   {
       Image = p.Picture.Url.Replace("SYSTEM_TYPE_SYNCSERVER", "URL"),
       Name = p.Name.Trim(),
       Price = p.Price.ToString(),
       ProductSizes = p.ProductSizes.Where(p => p.Size.Name == "42"), // here
       ProdutId = p.Id.ToString(),
   })
   .Take(9).ToList();

As for the filter...

_tebpooshContext.Products.Where(p => p.ProductSizes.Any(p => p.Size.Name == "42"))

That only filters the products in the end result, but when you only have ProductSizes = p.ProductSizes in the projection, you still get all ProductSizes of these products. This is similar to the difference between Filtered Include vs filtering the query.