How to map SQL database results to a domain model in EF Core

Question

I have the following domain models:

public class Country
{
    public string ISOAlpha3Code { get; set; }
    public string NameUS { get; set; }

    public string NameES { get; set; }
    public string NameFR { get; set; }

    public List<Province> Provinces { get; set; }
}

public class Province
{
    public string Code { get; set; }
    public string NameUS { get; set; }

    public string NameES { get; set; }
    public string NameFR { get; set; }
}

The database view that has to be used to populate these models returns a flat list, like this:

I was hoping to configure the entity, for the DbContext, to split out provinces for the countries.

I could create a domain model that matches the database results, though I would really like to separate the domain model from how its represented in this particular database - especially if the DB changes.

Is something like this possible? And If so, how would it be implemented?

public class CountryConfiguration : IEntityTypeConfiguration<Country>
{
    public void Configure(EntityTypeBuilder<Country> builder)
    {
        builder.ToTable("v_WS_CountriesAndProvinces");

        builder.Property(p => p.NameUS).HasColumnName("CountryNameUS");
        builder.Property(p => p.NameES).HasColumnName("CountryNameES");
        builder.Property(p => p.NameFR).HasColumnName("CountryNameFR");

        // something to split out the provinces?
        builder.Property(p => p.Provinces).HasConversion(
            v => ToDB(v), // to DB
            v => FromDB(v) // from DB
            );
    }

    private List<Province> FromDB(object v)
    {  
        throw new NotImplementedException();
    }

    private object ToDB(List<Province> v)
    {
        throw new NotImplementedException();
    }
}

Does this help? https://learn.microsoft.com/en-us/dotnet/standard/microservices-architecture/microservice-ddd-cqrs-patterns/implement-value-objects — David Browne - Microsoft, Aug 23 '18 at 19:15
@GertArnold really? I seem to be doing just that here, or I misunderstood? .https://stackoverflow.com/a/51613611/1678148 Just this one is a chunk more complicated — Darren Wainwright, Aug 24 '18 at 21:10
@Darren That's not what I'd call unflattening. OP wants to convert a 1-dimensional result set containing non-distinct data representing multiple objects into a multi-dimensional object graph. I don't see any way to do that with EF, neither when using conversions. — Gert Arnold, Aug 25 '18 at 07:25

score 0 · Answer 1 · answered Aug 23 '18 at 21:01

Could adjust your model slightly to accommodate the repeating group.

public class Country {
    public string ISOAlpha3Code { get; set; }
    public virtual ICollection<Name> Names { get; set; }
    public virtual ICollection<Province> Provinces { get; set; }
}

public class Province {
    public string Code { get; set; }
    public string ISOAlpha3Code { get; set; }  // FK to Country
    public virtual ICollection<Name> Names { get; set; }
}

public class Name {
    public string ISOAlpha3Code { get; set; }  // FK to Country
    public string Code { get; set; }           // FK to Province, null for country names
    public string Language { get; set; }       // US-EN, etc.
    public string NameField { get; set; }
}

Of course, this all assumes the data is immutable once entered, otherwise add & maintain relationships using surrogate PK/FKs if key data may change (ISOAlpha3Code, Code). And I would make sure that a NVARCHAR was generated for NameField since you will want to store Unicode strings.

How to map SQL database results to a domain model in EF Core

1 Answers1