Efficiently Filter based on List inside List c# and linq with Automapper

Question

I have the following model;

public class Category : Entity
{
    public List<CategoryTranslation> Translations { get; set; }
    public int Value { get; set; }
    public bool AutoTranslate { get; set; }
}

where

public class CategoryTranslation : Entity
{
    public string Name { get; set; }
    public Language Language { get; set; }
    public bool PrimaryTranslation { get; set; }
}

and

public class Language : Entity
{
    public string Country { get; set; }
    public string Code { get; set; }
    public bool? IsPrimary { get; set; }
    public bool IsActive { get; set; }
}

The idea being that we can store various translations of the category.

On our API I am then looking to provide an output model for a particular language;

CategoryOutputModel {
     public int Id;
     public string Name;
}

Where name would be the requested translation from the Category.Translations.Name.

Is it possible for me to select all categories with a particular translation, but then only select that translation and dispose of the unneeded elements in the Translations list

i.e.

//this would return all the categories i need but include all tranlsations and not just the 'en' ones
categories.Where(x => x.Translations.Any(y => y.Language.Code == "en"));

I'm would be then looking to use Automapper to map the returned data (from my service) to my output model and return.

As I assume I will need to do filter so Automapper know how to map to the Name field?

Answer 1

You might have to do the filtering on CategoryTranslation set instead of Category set, and then GroupBy the Category afterward.

Setup navigation properties on entities

public class Category : Entity
{
    public int Value { get; set; }
    public bool AutoTranslate { get; set; }

    public List<CategoryTranslation> Translations { get; set; }
}

public class CategoryTranslation : Entity
{
    public string Name { get; set; }
    public bool PrimaryTranslation { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }

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

public class Language : Entity
{
    public string Country { get; set; }
    public string Code { get; set; }
    public bool? IsPrimary { get; set; }
    public bool IsActive { get; set; }

    public List<CategoryTranslation> CategoryTranslations { get; set; }
}

Configure their relationships

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<Category>(b =>
        {
            b.HasKey(x => x.Id);

            b.ToTable("Category");
        });

        builder.Entity<CategoryTranslation>(b =>
        {
            b.HasKey(x => x.Id);
            b.Property(x => x.Name).IsRequired();
            b.HasOne(x => x.Category)
                .WithMany(c => c.Translations)
                .HasForeignKey(x => x.CategoryId);
            b.HasOne(x => x.Language)
                .WithMany(l => l.CategoryTranslations)
                .HasForeignKey(x => x.LanguageId);

            b.ToTable("CategoryTranslation");
        });

        builder.Entity<Language>(b =>
        {
            b.HasKey(x => x.Id);
            b.Property(x => x.Country).IsRequired();
            b.Property(x => x.Code).IsRequired();

            b.ToTable("Language");
        });
    }

    public DbSet<Category> Categories { get; set; }
    public DbSet<CategoryTranslation> CategoryTranslations { get; set; }
    public DbSet<Language> Languages { get; set; }
}

Seed tables on Startup

    public void Configure(IApplicationBuilder app, AppDbContext dbContext)
    {
        SeedData(dbContext);

        app.UseStaticFiles();
        app.UseMvcWithDefaultRoute();
    }

    private void SeedData(AppDbContext dbContext)
    {
        dbContext.Database.EnsureDeleted();
        dbContext.Database.Migrate();

        Language english = new Language
        {
            IsActive = true,
            IsPrimary = true,
            Country = "United States",
            Code = "en-US"
        };

        Language traditionalChinese = new Language
        {
            IsActive = true,
            IsPrimary = false,
            Country = "Chinese (Taiwan)",
            Code = "zh-TW"
        };

        Language simplifedChinese = new Language
        {
            IsActive = true,
            IsPrimary = false,
            Country = "Chinese (People's Republic of China)",
            Code = "zh-CN"
        };

        Language korean = new Language
        {
            IsActive = true,
            IsPrimary = false,
            Country = "Korea",
            Code = "ko-KR"
        };

        Language japanese = new Language
        {
            IsActive = true,
            IsPrimary = false,
            Country = "Japan",
            Code = "ja-JP"
        };

        Category guitar = new Category
        {
            Value = 1,
            AutoTranslate = true,
            Translations = new List<CategoryTranslation>
            {
                new CategoryTranslation
                {
                    Name = "Guitars",
                    Language = english,
                    PrimaryTranslation = true
                },
                new CategoryTranslation
                {
                    Name = "吉他",
                    Language = traditionalChinese,
                    PrimaryTranslation = false
                },
                new CategoryTranslation
                {
                    Name = "吉他",
                    Language = simplifedChinese,
                    PrimaryTranslation = false
                },
                new CategoryTranslation
                {
                    Name = "기타",
                    Language = korean,
                    PrimaryTranslation = false
                },
                new CategoryTranslation
                {
                    Name = "ギター",
                    Language = japanese,
                    PrimaryTranslation = false
                }
            }
        };

        Category bass = new Category
        {
            Value = 2,
            AutoTranslate = true,
            Translations = new List<CategoryTranslation>
            {
                new CategoryTranslation
                {
                    Name = "Bass",
                    Language = english,
                    PrimaryTranslation = true
                },
                new CategoryTranslation
                {
                    Name = "低音吉他",
                    Language = traditionalChinese,
                    PrimaryTranslation = false
                },
                new CategoryTranslation
                {
                    Name = "低音吉他",
                    Language = simplifedChinese,
                    PrimaryTranslation = false
                }
            }
        };

        dbContext.Categories.AddRange(guitar, bass);
        dbContext.Languages.AddRange(english, traditionalChinese,
            simplifedChinese, korean, japanese);
        dbContext.SaveChanges();
    }

Verify the database

Setup AutoMapper profile to map from Category to CategoryOutputModel

public class AutoMapperProfile : Profile
{
    public AutoMapperProfile()
    {
        CreateMap<Category, CategoryOutputModel>()
            .ForMember(dest => dest.Id, opt => opt.MapFrom(src => src.Id))
            .ForMember(dest => dest.Name, opt => opt.ResolveUsing(src =>
                // Because of your setup, it doesn't guarantee that
                // there is only one translation came out at the end for a 
                // language code for a category so I used FirstOrDefalt() 
                // here.
                src.Translations.FirstOrDefault()?.Name));
    }
}

Reading

public class HomeController : Controller
{
    private readonly AppDbContext _dbContext;
    private readonly IMapper _mapper;

    public HomeController(AppDbContext dbContext,
        IMapper mapper)
    {
        _dbContext = dbContext;
        _mapper = mapper;
    }

    public IActionResult Index()
    {
        var categoryTranslations = _dbContext.CategoryTranslations
            .AsNoTracking()
            .Include(ct => ct.Category)
            .Include(ct => ct.Language)
            .Where(ct => ct.Language.Code == "en-US")
            .ToList();

        var categoryOutputModels = categoryTranslations
            .GroupBy(ct => ct.Category, (key, group) => 
                // Use this map overload to map the category entity
                // to a new CategoryOutputModel object
                _mapper.Map<Category, CategoryOutputModel>(key));

        return View();
    }
}