most efficient Entity Framework Code First method of flattening / projecting parent entity with specific child

Question

I have a parent entity Widget with core members and multiple WidgetTranslation children that have language translated members i.e. Description text available in English, French, German etc.

e.g.

public class Widget
{
  public int Id { get; set; }
  public string Code { get; set; }
  public virtual ICollection<WidgetTranslation> WidgetTranslations { get; set; }
}

public class WidgetTranslation
{
  public int WidgetId { get; set; } 
  public virtual Widget Widget { get; set; }

  public int LanguageId { get; set; }
  public virtual Language Language { get; set; }

  public string Name { get; set; }
  public string Description { get; set; }
  public string Summary { get; set; }
} 

What is the most efficient method of querying the widget collection, flattening for a given LanguageId & projecting to a TranslatedWidget DTO

public class TranslatedWidget
{
  public int Id { get; set; }
  public string Code { get; set; }
  public int LanguageId { get; set; }
  public virtual Language Language { get; set; }
  public string Name { get; set; }
  public string Description { get; set; }
  public string Summary { get; set; }
} 

Given languageId I've started with

DbSet.Select(w => new TranslatedWidget
  {
    Id = w.Id,
    Code = w.Code,
    LanguageId = w.LanguageId,
    Name = w.WidgetTranslations.First(wt=>wt.LanguageId == languageId).Name,
    Description = w.WidgetTranslations.First(wt=>wt.LanguageId == languageId).Description,
    Summary = w.WidgetTranslations.First(wt=>wt.LanguageId == languageId).Summary
  });

But I've a feeling this is inefficient and won't scale for more properties on WidgetTranslation.

Thanks

Answer 1

Use SelectMany to flatten structures via a single join:

var widgetQuery = from w in dbSet.Widgets
                  from wt in w.WidgetTranslations
                  where wt.Language == languageId
                  select new TranslatedWidget
                  {
                     Id = w.Id,
                     Code = w.Code,
                     LanguageId = w.LanguageId,
                     Name = wt.Name,
                     Description = wt.Description,
                     Summary = wt.Summary
                  });

I'm assuming here that you only have a single translation for each widget in a given language.

Answer 2

I would move Name, Description and Summary into a nested class of your DTO...

public class TranslatedWidgetTranslation
{
    public string Name { get; set; }
    public string Description { get; set; }
    public string Summary { get; set; }
}

public class TranslatedWidget
{
    public int Id { get; set; }
    public string Code { get; set; }
    public int LanguageId { get; set; }
    public TranslatedWidgetTranslation Translation { get; set; }
}

Then you can project into that class and need First only once which would result in only one TOP(1) subquery in SQL instead of three:

DbSet.Select(w => new TranslatedWidget
{
    Id = w.Id,
    Code = w.Code,
    LanguageId = languageId,
    Translation = w.WidgetTranslations
        .Where(wt => wt.LanguageId == languageId)
        .Select(wt => new TranslatedWidgetTranslation
        {
            Name = wt.Name,
            Description = wt.Description,
            Summary = wt.Summary
        })
        .FirstOrDefault()
});

You must use FirstOrDefault here, First is not supported in a LINQ-to-Entities projection.

If you don't want that nested type you can project into anonymous types first and then convert into your final class, but the code will be a bit longer:

DbSet.Select(w => new
{
    Id = w.Id,
    Code = w.Code,
    LanguageId = languageId,
    Translation = w.WidgetTranslations
        .Where(wt => wt.LanguageId == languageId)
        .Select(wt => new
        {
            Name = wt.Name,
            Description = wt.Description,
            Summary = wt.Summary
        })
        .FirstOrDefault()
})
.AsEnumerable()
.Select(x => new TranslatedWidget
{
    Id = x.Id,
    Code = x.Code,
    LanguageId = x.LanguageId,
    Name = x.Translation != null ? x.Translation.Name : null,
    Description = x.Translation != null ? x.Translation.Description : null,
    Summary = x.Translation != null ? x.Translation.Summary : null
});