Load navigation properties from 'flat' table (many to many)

Question

I have to integrate with an existing DB and have realized the data loading using EF (we only read, but never write data with this application). The last missing step is to load the localized data as well.

The model looks like this:

Where the UDC (UserDefinedCode) table holds all the localized values. Every other entity can have 0-n codes, which has to be resolved from the UDC table.

Here's a SQL example for a field on Sequenz:

SELECT sequenz.Zulassungsstatus,
       udc.Beschreibung1
  FROM [dbo].[Sequenz] sequenz
  LEFT JOIN [dbo].[UserDefinedCode] udc ON udc.UserDefinedCode = 'MA_STATUS' AND udc.CodeValue = sequenz.Zulassungsstatus

The relevant entities:

public class SequenzEntity : TemporalEntity
{
    public int Zulassungsnummer { get; set; }

    public int Sequenznummer { get; set; }

    public string Zulassungsstatus { get; set; }

    public DateTime WiderrufVerzichtDatum { get; set; }

    public string SequenzName { get; set; }

    public string Zulassungsart { get; set; }

    public int BasisSequenzNummer { get; set; }

    public string Anwendungsgebiet { get; set; }

    public int ChargenblockadeAktiv { get; set; }

    public ApplikationsartEntity Applikationsart { get; set; }

    public PraeparatEntity Praeparat { get; set; }

    public IEnumerable<PackungEntity> Packungen { get; set; }

    public IEnumerable<DeklarationEntity> Deklarationen { get; set; }
}

public class UserDefinedCodeEntity : TemporalEntity
{
    public string SystemCode { get; set; }

    public string UserDefinedCode { get; set; }

    public string CodeValue { get; set; }

    public string SprachCode { get; set; }

    public string Beschreibung1 { get; set; }

    public string Beschreibung2 { get; set; }

    public string BeschreibungLang { get; set; }
}

The EF model is being configured in DbContext.OnModelCreating:

    protected override void OnModelCreating(ModelBuilder builder)
    {
        // Configure our entities
        builder.Entity<SequenzEntity>(ConfigureSequenzEntity);
        builder.Entity<UserDefinedCodeEntity>(ConfigureUserDefinedCodeEntity);
        [...]

        base.OnModelCreating(builder);
    }

    private static void ConfigureSequenzEntity(EntityTypeBuilder<SequenzEntity> builder)
    {
        ConfigureTemporalEntity(builder);

        builder.ToTable("Sequenz");

        builder.HasKey(sequenzEntity => new {sequenzEntity.Zulassungsnummer, sequenzEntity.Sequenznummer});

        builder.HasMany(sequenzEntity => sequenzEntity.Deklarationen)
               .WithOne(deklarationEntity => deklarationEntity.Sequenz);

        builder.HasMany(sequenzEntity => sequenzEntity.Packungen)
               .WithOne(packungEntity => packungEntity.Sequenz)
               .HasForeignKey(packungEntity => new {packungEntity.Zulassungsnummer, packungEntity.Sequenznummer});
    }

    private static void ConfigureUserDefinedCodeEntity(EntityTypeBuilder<UserDefinedCodeEntity> builder)
    {
        ConfigureTemporalEntity(builder);

        builder.ToTable("UserDefinedCode");

        builder.HasKey(userDefinedCodeEntity => new
                                                {
                                                    userDefinedCodeEntity.SystemCode,
                                                    userDefinedCodeEntity.UserDefinedCode,
                                                    userDefinedCodeEntity.CodeValue,
                                                    userDefinedCodeEntity.SprachCode
                                                });
    }

    private static void ConfigureTemporalEntity<TEntity>(EntityTypeBuilder<TEntity> builder)
        where TEntity : TemporalEntity
    {
        ConfigureBaseEntity(builder);

        builder.HasTemporalTable();

        builder.Property(baseEntity => baseEntity.VersionValidityStart)
               .HasColumnName("SysStartTime");

        builder.Property(baseEntity => baseEntity.VersionValidityEnd)
               .HasColumnName("SysEndTime");
    }

    private static void ConfigureBaseEntity<TEntity>(EntityTypeBuilder<TEntity> builder)
        where TEntity : BaseEntity
    {
        builder.HasKey(baseEntity => baseEntity.Id);
    }

I'm having a hard time figuring out how to set up the model and configuration EF to load the navigation properties. If I add

• SequenzEntity: public IEnumerable<UserDefinedCodeEntity> ZulassungsstatusCodes { get; set; }
• UserDefinedCodeEntity: public IEnumerable<SequenzEntity> Sequenzen{ get; set; }

I obviously get a many-to-many relation I can't configure in EF.

Is it possible to use EF to JOIN onto the UDC table as I would wish?

Answer 1

As I couldn't find a clean way to do this with EF, I've chosen another approach, where the entities themselves aren't aware of the language dependent properties. It's now the job of the business layer to aggregate the fields.

Create an object (projection) containing all required properties:

    public override async Task<PackungDto> GetAsync(int id, DateTime? asOf = null)
    {
        var packungProjection = new PackungDetailProjection
                                {
                                    SprachCode = CultureInfo.CurrentUICulture.GetOneLetterLanguage(),
                                    Packung = await _packungRepository.GetAsync(id, asOf, _includePropertiesGet),
                                    ZulassungsstatusCodes = await _codeRepository.GetCodesAsync(CodeType.Zulassungsstatus, asOf: asOf)
                                };

        return Mapper.Map<PackungDto>(packungProjection);
    }

and let AutoMapper do the mappings with this profile:

internal sealed class PackungDetailProjectionToPackungDtoMappingProfile : Profile
{
    #region Public Methods

    public PackungDetailProjectionToPackungDtoMappingProfile()
    {
        CreateMap<PackungDetailProjection, PackungDto>()
            // BaseEntity
            .ForMember(dest => dest.Id, exp => exp.MapFrom(src => src.Packung.Id))

            // Temporal Entity
            .ForMember(dest => dest.VersionValidityStart, exp => exp.MapFrom(src => src.Packung.VersionValidityStart))
            .ForMember(dest => dest.VersionValidityEnd, exp => exp.MapFrom(src => src.Packung.VersionValidityEnd))

            // Packung
            .ForMember(dest => dest.Gtin, exp => exp.MapFrom(src => src.Packung.Gtin))
            .ForMember(dest => dest.Zulassungsnummer, exp => exp.MapFrom(src => src.Packung.Zulassungsnummer))
            .ForMember(dest => dest.Sequenznummer, exp => exp.MapFrom(src => src.Packung.Sequenznummer))
            .ForMember(dest => dest.Packungscode, exp => exp.MapFrom(src => src.Packung.Packungscode))
            .ForMember(dest => dest.Name, exp => exp.MapFrom(src => $"{src.Packung.Sequenz.SequenzName.Trim()} {src.Packung.Packungsgroesse.Trim()}"))

            // Codes
            .ForMember(dest => dest.ZulassungsstatusDescription,
                       exp => exp.MapFrom(src => GetCodeDescription(src.ZulassungsstatusCodes.Where(entity => entity.CodeValue == src.Packung.Zulassungsstatus).ToList(),
                                                                    src.SprachCode,
                                                                    entity => entity.Beschreibung1)));
    }

    #endregion

    #region Private Methods

    /// <summary>
    ///     Gets the code description.
    /// </summary>
    /// <param name="codes">The codes.</param>
    /// <param name="sprachCode">The sprach code.</param>
    /// <param name="propertyValueSelector">
    ///     The selector specifying which property to use.
    ///     Usually <see cref="UserDefinedCodeEntity.Beschreibung1" />, <see cref="UserDefinedCodeEntity.Beschreibung2" /> or
    ///     <see cref="UserDefinedCodeEntity.BeschreibungLang" />
    /// </param>
    /// <returns>
    ///     The code description determined by following order:<br />
    ///     1. Using the user langauge.<br />
    ///     2. Using the default language.<br />
    ///     3. <c>Null</c>.
    /// </returns>
    private static string GetCodeDescription(ICollection<UserDefinedCodeEntity> codes, string sprachCode, Func<UserDefinedCodeEntity, string> propertyValueSelector)
    {
        var codeDescription = codes.FirstOrDefault(entity => entity.SprachCode == sprachCode);

        // Fall back to default langauge if the code could not be found for the user language 
        if (codeDescription == null && sprachCode != LanguageOneLetterConstants.DefaultCode)
        {
            codeDescription = codes.FirstOrDefault(entity => entity.SprachCode == LanguageOneLetterConstants.DefaultCode);
        }

        return codeDescription == null
                   ? null
                   : propertyValueSelector(codeDescription);
    }

    #endregion