Map multiple C# entities to a single database table in EF Core 6

Question

I'm trying to figure out how to map multiple C# entities - Opportunity (all attributes) & OpportunitySummary (subset of attributes) - to a single database table Opportunity in EF Core 6.

The Opportunity entity has about 60 attributes, and there are times we need only about 10 of them. There are overlapping attributes, meaning some attributes are in both entities.

I've seen a solution in which the Opportunity class has a member named OpportnitySummary. So if you have an instance of the Opportunity class, but you want to get to a member that's part of the OpportunitySummary class, you would need to do Opportunity.OpportunitySummary.PropertyA. I'm not happy with this solution ... I'd like to be able to do both Opportunity.PropertyA and OpportunitySummary.PropertyA.

After all the reading I've done, this is the code I have in the OnModelCreating() method of the DbContext:

// map both classes to the same DB table
modelBuilder.Entity<Opportunity>().ToTable("Opportunity");
modelBuilder.Entity<OpportunitySummary>().ToTable("Opportunity");

// Configure Opportunity -> OpportunitySummary 1:1 relationship
modelBuilder.Entity<OpportunitySummary>()
            .HasOne<Opportunity>()
            .WithMany()
            .HasForeignKey(os => os.ID)
            .HasPrincipalKey(o => o.ID);

But I get an error saying that the 'Opportunity.ID2' property is not found. There is no such property in my code anywhere.

Could someone help me with how to configure this scenario using EF Core's Fluent API ?

I've looked at this question: How do you map multiple objects to a single table in EF Core using Table Splitting? but it doesn't have an answer.

Answer 1

EF Core supports this through owned entities which embed details in a single table. The caveat is that an owned entity cannot be treated as an aggregate root, so in your case you would not be able to have a DbSet to "Opportunity", or references directly to it, only via OpportunitySummary. So in the context of an OpportunitySummary -> Opportunity, it would likely make more sense to think of it as Opportunity being the summarized basic info, with an OpportunityDetails being the extended, owned details of the opportunity.

Another option is to leverage bounded contexts where the summarized entity is declared and that DbContext serves controllers etc. that interact with the summarized details, while detailed controllers use a DbContext that references the more complete representation. Common entities can be registered with both of the two bounded contexts. This requires some attention to ensure that references aren't passed between bounded DbContexts without explicit detaching/attaching, which should be kept to a minimum or avoided as much as possible.

Ultimately this is a pattern I used to use in NHibernate and was initially looking for in EF, but it turns out that this honestly isn't needed when working with EF. Much of the argument for having things like "summary" or lightweight entities comes from situations where those entities are used for more than strictly representing the data domain. Summarizing data can be done through projection where the entities reflect the complete data model, but then are projected down into view models or DTOs that can represent the summary/lightweight vs. full representation for consumers based on what is needed. If your use case for a summary entity is because you want to send a cut down version of the entity somewhere (view, or returned from an API) consider that Entities are not suited for this role and it is better to transport this data via a POCO object. Using projection with Select or a mapper supporting IQueryable projection will result in queries against your table that are optimized just as if you had a cut-down entity defined, and mean you don't introduce complexity trying to juggle entities representing the same raw data.

Edit:

For example, if we have an Opportunity table with 30 columns and we want to also have an OpportunitySummary rendition that has the ID, business name, and date:

public class OpportunitySummaryViewModel
{
    public int Id { get; set; }
    public string BusinessName { get; set; }
    public string StartDate { get; set; }
}

Then when reading, we can project the Opportunity summary from the Opportunity:

var opportunities = Context.Opportunities
    .Where(x => /* conditions */)
    .Select(x => new OpportunitySummaryViewModel
    {
        Id = x.Id,
        BusinessName = x.Company.Name,
        StartDate = x.StartDate
    }).ToList();

Projection down to view models or DTOs allows you to flatten or summarize data. It doesn't load the opportunity record or other related entities into the DbContext/memory, but rather builds an SQL statement joining tables as necessary to pull just the columns needed to populate what you want. If you just want to query data to use within the scope of the method, defining a view model / dto is optional, you can populate an anonymous type and it will query the data the same way. It is an extremely efficient way to access the data rather than passing entities or graphs of entities around where much of the data is not needed.