Filter child collection of one entity

Question

I have the following entities:

public class Product {
  public Int32 ProductId { get; set; }
  public Double Price { get; set; }
  public virtual ProductType ProductType { get; set; }
}

public class ProductType {
  public Int32 ProductTypeId { get; set; }
  public virtual ICollection<ProductTypeLocalization> ProductTypeLocalizations { get; set; }
}

public class ProductTypeLocalization {
  public Int32 ProductTypeId { get; set; }
  public String Language { get; set; }
  public String Name { get; set; }
  public String Description { get; set; }
  public virtual ProductType { get; set; }
}

Then I have a query as follows:

var models = await products.Select(product => new {
  Id = product.Id,
  Price = product.Price,
  ProductType = new {
    Id = product.ProductType.ProductTypeId,
    Name = ???,
    Description = ???
  }
}).ToListAsync()

On my query where it shows

Name = ???,
Description ???

I need to get Name and Description from ProductTypeLocalization with Language == "en".

I could use FirstOrDefault on each but I think it is not an efficient way.

What would be the best way to do this?

Answer 1

LEFT OUTER JOIN translation seems to be the best for such scenario.

In theory EF Core query translator should be able to consolidate the common FirstOrDefault() expressions to single LEFT OUTER JOIN as it does for optional reference navigation properties.

In practice (as of the latest at this time EF Core 2.2) it doesn't do that and generates separate correlated subquery for each selected field.

Assuming that each product type has 0 or 1 localizations for a specific language, the desired translation can be achieved with SelectMany like this:

var models = await products.SelectMany(
    product => product.ProductType.ProductTypeLocalizations
        .DefaultIfEmpty()
        .Where(ptl => ptl == null || ptl.Language == "en"),
    (product, ptl) => new
    {
        Id = product.ProductId,
        Price = product.Price,
        ProductType = new
        {
            Id = product.ProductType.ProductTypeId,
            Name = ptl.Name,
            Description = ptl.Description
        }
    })
    .ToListAsync();

or the equivalent and better readable version using the LINQ query syntax:

var models = await (
    from product in products
    let pt = product.ProductType
    from ptl in pt.ProductTypeLocalizations.DefaultIfEmpty()
    where ptl == null || ptl.Language == "en"
    select new
    {
        Id = product.ProductId,
        Price = product.Price,
        ProductType = new
        {
            Id = pt.ProductTypeId,
            Name = ptl.Name,
            Description = ptl.Description
        }
    }).ToListAsync();