How to populate complex entity from flat table

Question

I have a table which is flat from which data is retrieved and then is packaged as json and included as part of an API request. This is straightforward.

However, I now have an api spec that is expects data like this:

{
   "first_name" : "Greg",
   "last_name" : "Gum",
   "billing_address" : {
       "address" : "123 Main St",
       "city" : "New York",
       "state" : "New York",
       "zip" : "12345"
}

This data is stored in a flat table (there is only ever one billing address, so there is no reason to have a separate address table) so it's not really a parent/child relationship.

Is it possible to map this from a flat table to a complex object using only attributes? The only way I see doing this is by hand. In other words, the Entity matches the table, and then I have yet another entity which matches the Json and the code maps it manually from one field to the other.

It just seems rather crude to be writing mapping code for this.

Answer 1

This can be done using owned entity types.

Not knowing the database schema, let's assume that these classes fit the bill:

class Person
{
    public int Id { get; set; }
    public string first_name { get; set; }
    public string last_name { get; set; }
    public Address billing_address { get; set; }
}

class Address
{
    public string address { get; set; }
    public string city { get; set; }
    public string state { get; set; }
    public string zip { get; set; }
}

In the context's OnModelCreating override you could have:

var ownedNavigationBuilder = modelBuilder.Entity<Person>().OwnsOne(p => p.billing_address);
// Just to show some custom mapping to db column names:
ownedNavigationBuilder.Property(a => a.address).HasColumnName("Address");
ownedNavigationBuilder.Property(a => a.city).HasColumnName("City");

This builds the following database table (in SQL Server and C# nullable reference types enabled):

  CREATE TABLE [People] (
      [Id] int NOT NULL IDENTITY,
      [first_name] nvarchar(max) NOT NULL,
      [last_name] nvarchar(max) NOT NULL,
      [Address] nvarchar(max) NOT NULL,
      [City] nvarchar(max) NOT NULL,
      [billing_address_state] nvarchar(max) NOT NULL,
      [billing_address_zip] nvarchar(max) NOT NULL,
      CONSTRAINT [PK_People] PRIMARY KEY ([Id])
  );

Now a new Person with an Address object will be saved as one record in this table and in queries be re-materialized as a Person with the nested Address. Note that queries don't require Include. The owned address will always be included in the result.