mapping multiple tables to a single entity class in entity framework

Question

I am working on a legacy database that has 2 tables that have a 1:1 relationship. Currently, I have one type (1Test:1Result) for each of these tables defined I would like to merge these particular tables into a single class.

The current types look like this

public class Result 
{
    public          string      Id                  { get; set; }
    public          string      Name                { get; set; }
    public          string      Text                { get; set; }
    public          string      Units               { get; set; }
    public          bool        OutOfRange          { get; set; }
    public          string      Status              { get; set; }
    public          string      Minimum             { get; set; }
    public          string      Maximum             { get; set; }

    public virtual  Instrument  InstrumentUsed      { get; set; }

    public virtual  Test        ForTest             { get; set; }
}


public class Test
{
    public          int     Id            { get; set; }
    public          string  Status        { get; set; }
    public          string  Analysis      { get; set; }
    public          string  ComponentList { get; set; }
    public virtual  Sample  ForSample     { get; set; }
    public virtual  Result  TestResult    { get; set; }
}

I would prefer them to look like this

public class TestResult
{
    public          int        Id              { get; set; }
    public          string     Status          { get; set; }
    public          string     Analysis        { get; set; }
    public          string     ComponentList   { get; set; }
    public          string     TestName        { get; set; }
    public          string     Text            { get; set; }
    public          string     Units           { get; set; }
    public          bool       OutOfRange      { get; set; }
    public          string     Status          { get; set; }
    public          string     Minimum         { get; set; }
    public          string     Maximum         { get; set; }

    public virtual  Instrument InstrumentUsed { get; set; }
}

I am currently using the fluent API for mapping these to our legacy Oracle database.

What would be the best method of combining these into a single class? Please note that this is a legacy database. Changing the tables is not an option and creating views is not a viable solution at this point in the project.

score 42 · Accepted Answer · answered Jul 13 '11 at 00:21

42

You can use Entity Splitting to achieve this if you have the same primary key in both tables.

  modelBuilder.Entity<TestResult>()
    .Map(m =>
      {
        m.Properties(t => new { t.Name, t.Text, t.Units /*other props*/ });
        m.ToTable("Result");
      })
    .Map(m =>
      {
        m.Properties(t => new { t.Status, t.Analysis /*other props*/});
        m.ToTable("Test");
      });

Here's a useful article

answered Jul 13 '11 at 00:21

Eranga

32,181
5
97
96

1

they do have the same key so this should work in the event they didnt would it still be possible by mapping relationship seperately or is that not available in this version of the framework yet ? – Chris McGrath Jul 13 '11 at 12:09
How would you map to Instrument? – roland Mar 10 '16 at 17:25
What is the equivalent of Map() in EF Core – Karthic G Jan 11 '21 at 09:18
3

@KarthicG There isn't one. EF Core 6 still doesn't support mapping multiple tables to a single entity - but if you use database-first (instead of code-first) then you could use a `VIEW` to join the tables together and lie to EF Core and pinky-swear that the `VIEW` is really a `TABLE` - which is perfectly valid as you can define `INSERT` and `UPDATE` handlers for `VIEW` objects in SQL Server. – Dai Mar 03 '22 at 23:05
They've made it in EF Core 7: https://github.com/dotnet/efcore/issues/620 https://learn.microsoft.com/en-us/ef/core/modeling/table-splitting#entity-splitting – n0099 Jan 12 '23 at 08:44

mapping multiple tables to a single entity class in entity framework

1 Answers1

Linked