using System;

using System.Linq;

using Microsoft.EntityFrameworkCore;

using Microsoft.Data.SqlClient;

using System.Collections.Generic;

using Microsoft.Extensions.Logging;

​

public class Program

{

    public static void Main()

    {

        using (var ctx = new SelectListContext())

        {

            ctx.Database.EnsureCreated();

            var selectListTranslations = new List<Translation> { 

                new Translation { LanguageCode = "en", Text = "Car models", Id = 1 }, 

                new Translation { LanguageCode = "fi", Text = "Automerkkejä", Id = 1  },

                new Translation { LanguageCode = "en", Text = "Models of cars", Id = 2 }, 

                new Translation { LanguageCode = "fi", Text = "Eri automerkkejä", Id = 2 }};

            var fordItemTranslations = new List<Translation> { 

                new Translation { LanguageCode = "en", Text = "Ford", Id = 11 }, 

                new Translation { LanguageCode = "fi", Text = "Foortti", Id = 11 },

                new Translation { LanguageCode = "en", Text = "This is crap", Id = 12 }, 

                new Translation { LanguageCode = "fi", Text = "Romurautaa", Id = 12 }};

            var opelItemTranslations = new List<Translation> { 

                new Translation { LanguageCode = "en", Text = "Opel", Id = 21 }, 

                new Translation { LanguageCode = "fi", Text = "Ooppeli", Id = 21 },

                new Translation { LanguageCode = "en", Text = "What do you expect?!", Id = 22 }, 

                new Translation { LanguageCode = "fi", Text = "No, se on Ooppeli", Id = 22 }};

            var selectListItems = new List<SelectListItem> { 

                new SelectListItem { NameTranslationSetId = 11, DescriptionTranslationSetId = 12 }, 

                new SelectListItem { NameTranslationSetId = 21, DescriptionTranslationSetId = 22 } 

            };

            var slist = new SelectList() {

                NameTranslationSetId = 1,

                DescriptionTranslationSetId = 2,

                Items = selectListItems

            };

            ctx.Translations.AddRange(selectListTranslations);

            ctx.Translations.AddRange(fordItemTranslations);

            ctx.Translations.AddRange(opelItemTranslations);

            ctx.SaveChanges(); 

​

            ctx.SelectLists.Add(slist);

            ctx.SaveChanges(); 

            var language = "en";

            var selectListsDb = ctx.SelectLists

                .Include(s => s.Items)

                .ToList();

            var translations = ctx.Translations.ToList();

            var selectLists = selectListsDb.Select(s => new SelectListDTO

                    {

                        Name = translations.SingleOrDefault(t => t.LanguageCode.Equals(language) && s.NameTranslationSetId.Equals(t.Id)).Text,

                        Description = translations.SingleOrDefault(t => t.LanguageCode.Equals(language) && s.DescriptionTranslationSetId.Equals(t.Id)).Text,

                        Items = s.Items

                            .Select(i => new SelectListItemDTO

                            {

                                Name = translations.SingleOrDefault(t => t.LanguageCode.Equals(language) && i.NameTranslationSetId.Equals(t.Id)).Text,

                                Description = translations.SingleOrDefault(t => t.LanguageCode.Equals(language) && i.DescriptionTranslationSetId.Equals(t.Id)).Text

                            })

                            .ToList()

                    });

            var firstSelectListItems = selectLists.First().Items;

            FiddleHelper.WriteTable("selectLists", selectLists);

            FiddleHelper.WriteTable("selectList items", firstSelectListItems);

​

        }

    }

}

​

public class SelectListContext: DbContext 

{

​

    protected override void OnConfiguring(DbContextOptionsBuilder builder)

    {

        var lf = new LoggerFactory();

        lf.AddProvider(new MyLoggerProvider());

        var connectionString = FiddleHelper.GetConnectionStringSqlServer();

​

        builder

            .UseLoggerFactory(lf)

            .UseSqlServer(new SqlConnection(connectionString))

                .EnableDetailedErrors()

                .EnableSensitiveDataLogging();

​

        base.OnConfiguring(builder);

    }

    protected override void OnModelCreating(ModelBuilder builder)

    {

        builder.Entity<SelectList>(entity =>

            {

                entity.HasKey(selectList => selectList.Id);

                entity.Property(selectList => selectList.NameTranslationSetId).IsRequired();

                entity.Property(selectList => selectList.DescriptionTranslationSetId).IsRequired();

                entity.Property(selectList => selectList.IsDisabled).IsRequired();

                entity.Property(selectList => selectList.RowVersion).IsRowVersion();

                entity.HasMany(selectList => selectList.Items);

            });

​

        builder.Entity<SelectListItem>(entity =>

        {

            entity.HasKey(item => item.Id);

            entity.Property(item => item.NameTranslationSetId).IsRequired();

            entity.Property(item => item.DescriptionTranslationSetId).IsRequired();

            entity.Property(item => item.Order).IsRequired();

            entity.Property(item => item.IsDisabled).IsRequired();

            entity.Property(item => item.RowVersion).IsRowVersion();

​

        });

        builder.Entity<Translation>(entity =>

        {

            entity.HasKey(t => new { t.Id, t.LanguageCode });

            entity.HasOne<SelectList>()

                .WithMany()

                .HasPrincipalKey(s => s.NameTranslationSetId)

                .HasForeignKey(t => t.Id)

                .OnDelete(DeleteBehavior.Restrict);

            entity.HasOne<SelectList>()

                .WithMany()

                .HasPrincipalKey(s => s.DescriptionTranslationSetId)

                .HasForeignKey(t => t.Id)

                .OnDelete(DeleteBehavior.Restrict);

            entity.HasOne<SelectListItem>()

                .WithMany()

                .HasPrincipalKey(s => s.NameTranslationSetId)

                .HasForeignKey(t => t.Id)

                .OnDelete(DeleteBehavior.Restrict);

            entity.HasOne<SelectListItem>()

                .WithMany()

                .HasPrincipalKey(s => s.DescriptionTranslationSetId)

                .HasForeignKey(t => t.Id)

                .OnDelete(DeleteBehavior.Restrict);

        });

    }

    public DbSet<SelectList> SelectLists { get; set; }

    public DbSet<SelectListItem> SelectListItem { get; set; }

    public DbSet<Translation> Translations { get; set; }

}

​

public class SelectList

{

    public Guid Id { get; set; }

    public int NameTranslationSetId { get; set; }

    public int DescriptionTranslationSetId { get; set; }

    public string TechnicalValueType { get; set; } = default!;

    public bool IsDisabled { get; set; } = default!;

    public byte[] RowVersion { get; set; } = default!;

    public virtual List<SelectListItem> Items { get; set; }

    //public virtual List<Translation> Translations { get; set; }

}

​

public class SelectListItem

{

    public Guid Id { get; set; }

    public int NameTranslationSetId { get; set; }

    public int DescriptionTranslationSetId { get; set; }

    public string TechnicalName { get; set; } = default!;

    public string TechnicalValue { get; set; } = default!;

    public int Order { get; set; }

    public bool IsDisabled { get; set; }

    public byte[] RowVersion { get; set; } = default!;

    public Guid SelectListId { get; set; }

    //public virtual List<Translation> Translations { get; set; } = default!;

}

​

public class Translation

{

    public int Id { get; set; }

    public string LanguageCode { get; set; }

    public string Text { get; set; }

}

public class SelectListDTO

{

    public string Name { get; set; }

    public string Description { get; set; }

    public List<SelectListItemDTO> Items { get; set; }

}

​

public class SelectListItemDTO

{

    public string Name { get; set; }

    public string Description { get; set; }

}

​

public class MyLoggerProvider : ILoggerProvider

{

    public ILogger CreateLogger(string categoryName)

    {

            return new MyLogger();

    }

​

    public void Dispose()

    { }

​

    private class MyLogger : ILogger

    {

        public bool IsEnabled(LogLevel logLevel)

        {

            return true;

        }

​

        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)

        {

            Console.WriteLine(formatter(state, exception));

        }

​

        public IDisposable BeginScope<TState>(TState state)

        {

                return null;

        }

    } 

}