Same table multiple classes

[herme063]

Let me first start with: this is not table hierarchy.

I have 2 assemblies that share the same concept Person. In the 1st assembly, Person is a class with 5 properties but in the 2nd one it only has 2 properties (the bare minimum Id and Name). Each assembly has a db context. I am creating a 3rd assembly to enable a combined code first migration for both db contexts. I am using fluent api to configure both models: ToTable("People", "dbo").

When I create the migration, I get the following message: Cannot use table 'dbo.People' for entity type 'Person' since it is being used for entity type 'Person' and there is no relationship between their primary keys.

Is there a way I can make this work? If not, what is the correct way to handle this scenario.

[ajcvickers]

Note for triage: consider creating the PK to PK relationship automatically in this case?

@herme063 Something like this:

namespace One
{
    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

namespace Two
{
    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime Birthday { get; set; }
    }
}

public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<One.Person>(b =>
        {
            b.HasOne<Two.Person>().WithOne().HasForeignKey<Two.Person>(e => e.Id);
            b.ToTable("People");
            b.Property(e => e.Name).HasColumnName(nameof(One.Person.Name));
        });

        builder.Entity<Two.Person>(b =>
        {
            b.ToTable("People");
            b.Property(e => e.Name).HasColumnName(nameof(Two.Person.Name));
        });
    }
}

Couple of notes on this:

• The relationship is configured between the two PK properties with no navigations; navigations can be added if needed.
• Properties, like Name, that exist in both types will by convention get mapped to different columns, but this can be overridden as shown above by explicitly mapping them to the same column.

[herme063]

Looks good, thank you.

[ajcvickers]

Discussed in triage, and decided that at least for now the relationship needs to be configured such that the principal and dependent are know, which is important for support of option dependents with table splitting.

[leandro-cervelin]

@ajcvickers Hi, could you help me with a question, please? I have different contexts in two different domains and each domain contains an entity "country". The contexts do not know each other and I cannot configure how you have done in the above example. The issue is that I am getting the error: "There is already an object named 'Country' in the database." What is the best approach in this case, please? Thank you in advance.

[ajcvickers]

@leandrocervelin Sounds like you might be having migrations for both context types create the Country table. We have a feature (#2725) for allowing an entity type to be excluded from the migrations of one or the other context types. For now, the way to handle this is to edit the migration for one context type and remove the parts that create this table.

[leandro-cervelin]

Thank you very much for reply @ajcvickers.

Sounds like it is fixed.

[progmars]

I have a similar problem with a table which needs two representation entity classes - one with full set of fields and the other one with partial fields. While the trick with b.HasOne<Two.Person>().WithOne().HasForeignKey<Two.Person>(e => e.Id); worked for reading and update, it still failed when attempting to create the full-featured entity with an error:

The entity of type 'X' is sharing the table 'Xes' with entities of type 'SimplifiedX', but there is no entity of this type with the same key value that has been marked as 'Added'. 

I guess, I'll have to create an SQL view just to decouple that simplified entity from the 'Xes' table. I'd really like not to, because that's a legacy chaotic database and it takes lots of bureaucracy to negotiate the changes with the maintainers of the database.

Is there any way to tell EF "please ignore the fact that these two entities have the same table name; treat them as totally unrelated"?

[AndriySvyryd]

@progmars https://github.com/aspnet/EntityFrameworkCore/issues/15310 should allow your scenario to work as expected

[mhDuke]

@ajcvickers when i try to add-migration i get

'Party.Name' and 'Agent.Name' are both mapped to column 'Name' in 'Parties' but are configured with different nullability.

public class Entity
   {
       public virtual int Id { get; protected set; }
   }

public class Party : Entity
    {
        public string Name { get; private set; }
        public decimal CreditLimit { get; private set; }
    }
public class Agent : Entity
    {
        public string Name { get; private set; }
    }

public void Configure(EntityTypeBuilder<Party> b)
        {
            b.ToTable("Parties");

            b.Property(e => e.Name).HasColumnName(nameof(Party.Name))
                .HasMaxLength(100)
                .IsRequired();

            b.HasOne<Agent>().WithOne()
                .HasForeignKey<Agent>(e => e.Id)
                .OnDelete(DeleteBehavior.Cascade)
                .IsRequired();

            b.Property(e => e.CreditLimit)
                .IsRequired()
                .HasColumnType("decimal(18,2)");
        }
public void Configure(EntityTypeBuilder<Agent> b)
        {
            b.ToTable("Parties");

            b.Property(e => e.Name).HasColumnName(nameof(Agent.Name))
                .HasMaxLength(100)
                .IsRequired();
        }

[ajcvickers]

@MHDuke Please file a new issue and include a small, runnable project/solution or complete code listing.

[AndriySvyryd]

@MHDuke Try the 3.1 preview

[Savitri1403]

Hi team, I am trying to use single table and create versions of the api's

public class EntityBase { public virtual int Id { get; protected set; } }

public class PartyV1 : EntityBase { public string Name { get; private set; } public decimal CreditLimit { get; private set; } } public class PartyV2 : EntityBase { public string Name { get; private set; } public string Address { get; private set; } }

and added model binding as below example

builder.Entity(b => { b.HasOne().WithOne().HasForeignKey(e => e.Id); b.ToTable("People"); b.Property(e => e.Name).HasColumnName(nameof(One.Person.Name)); });

    builder.Entity<Two.Person>(b =>
    {
        b.ToTable("People");
        b.Property(e => e.Name).HasColumnName(nameof(Two.Person.Name));
    });

but the entity framework is failing and giving me error as "The entity of type 'V2' is sharing the table 'V1' with entities of type 'V1', but there is no entity of this type with the same key value '{Id: -2147482647}' that has been marked as 'Added'."

can anyone please help me here?

[AndriySvyryd]

@Savitri1403 If each row contains both PartyV1 and PartyV2 entities then when inserting one you need to also insert the other one with the same Id. If you have more questions open a new issue with a small runnable project that shows the exception.

[alexandis]

Following up, I still can't figure out how to resolve the following issue in the best way and avoid the error related to binding to the same table:

Shared project:

builder.Entity(b => { b.ToTable("CT_CA_COMP_LIC"); ....Simplified binding } Consuming project:

builder.Entity(b => { b.ToTable("CT_CA_COMP_LIC"); ....Advanced binding } CompanyLicence surely has the same PK in both projects - the difference is that shared project has a simplifed version of this entity. Another note: in the consuming project this entity is used for all kinds of CRUD operations, in the shared project it's only reading...

cl.HasOne<ConsumingContext.CompanyLicence>().WithOne().HasForeignKey<SharedContext.CompanyLicence>(cl => cl.Id); did not work out for me, I've got: The database operation was expected to affect 1 row(s), but actually affected 0 row(s) exception while trying to modify the consuming project's entity...

If I separate DbContexts - I'm still getting: InvalidOperationException: Cannot use table 'CT_CA_COMP_LIC' for entity type 'CompanyLicence' since it is being used for entity type 'CompanyLicence' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'CompanyLicence' on the primary key properties and pointing to the primary key on another entity type mapped to 'CT_CA_COMP_LIC'. exception

[roji]

@alexandis can you please open a new issue with a runnable, minimal code sample that shows the error happening?