Problem with sharing same table by two independent entities

[hikalkan]

Hi,

I don't know if this feature is available by EF Core. However, I need to map two independent entity to same db table. Entity A will use some columns and Entity B will use some columns. They will share same Id. Also, they both may use some colums, or Entity A may be a subset of Entity B.

To demonstrate it, I prepared an example project here: https://github.com/hikalkan/samples/tree/master/EfCoreTableSharingDemo (you can just download and try).

Entities

public class User
{
    public Guid Id { get; set; }
    public string UserName { get; set; }
}

public class DetailedUser
{
    public Guid Id { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
}

DbContext

public class MyDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<DetailedUser> DetailedUsers { get; set; }

    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>(b =>
        {
            b.ToTable("MyUsers");

            b.Property(x => x.UserName).HasColumnName(nameof(User.UserName));
        });

        modelBuilder.Entity<DetailedUser>(b =>
        {
            b.ToTable("MyUsers");

            b.Property(x => x.UserName).HasColumnName(nameof(DetailedUser.UserName));
            b.Property(x => x.Email).HasColumnName(nameof(DetailedUser.UserName));

            b.HasOne<User>()
                .WithOne()
                .HasForeignKey<User>(u => u.Id);
        });
    }
}

Migration code

It's creating the migration the way I expect:

public partial class Initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "MyUsers",
            columns: table => new
            {
                Id = table.Column<Guid>(nullable: false),
                Email = table.Column<string>(nullable: true),
                UserName = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_MyUsers", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "MyUsers");
    }
}

Test Code

//Add some users using "DetailedUsers" DbSet
using (var dbContext = new MyDbContext(CreateDbContextOptions()))
{
    dbContext.DetailedUsers.Add(new DetailedUser("john", "john@aspnetboilerplate.com"));
    dbContext.DetailedUsers.Add(new DetailedUser("neo", "neo@aspnetboilerplate.com"));
    dbContext.SaveChanges();
}

//Query users from "Users" DbSet
using (var dbContext = new MyDbContext(CreateDbContextOptions()))
{
    foreach (var user in dbContext.Users.ToList())
    {
        Console.WriteLine(user);
    }
}

Exception

I'm getting the following exception message on dbContext.SaveChanges();:

The entity of 'DetailedUser' is sharing the table 'MyUsers' with 'User', but there is no entity of this type with the same key value that has been marked as 'Added'. 

The stacktrace:

   at Microsoft.EntityFrameworkCore.Update.Internal.ModificationCommandIdentityMap.Validate(Boolean sensitiveLoggingEnabled)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.CreateModificationCommands(IReadOnlyList`1 entries, Func`1 generateParameterName)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.<BatchCommands>d__8.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

BTW, it can successfully query the existing data.

Additional Notes

I can not add navigation property from User to DetailedUser. Because they are in different modules (project/assembly). But the DetailedUser can have a reference to the assembly containing the User.

You may wonder why it's needed and suspect that I'm designing it in a wrong way. But I'm creating a modular and extensible application framework and I need such a design.

So, please answer if it's possible and how. If not possible, do you consider such a feature request?

[hikalkan]

It works if I change DetailedUser as like below:

public class DetailedUser
{
    public Guid Id { get; set; }
    public string Email { get; set; }
    public virtual User User { get; set; } //Added a navigation property!
}

And change the model building code like:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<User>(b =>
    {
        b.ToTable("MyUsers");

        b.Property(x => x.UserName).HasColumnName(nameof(User.UserName));
    });

    modelBuilder.Entity<DetailedUser>(b =>
    {
        b.ToTable("MyUsers");

        b.Property(x => x.Email).HasColumnName(nameof(DetailedUser.Email));

        b.HasOne(u => u.User)
            .WithOne()
            .HasForeignKey<User>(u => u.Id);
    });
}

However I don't want to make this since it makes DetailedUser entity unnecessarily more complicated.

[smitpatel]

Duplicate of #9005

[hikalkan]

I'm not sure if it's duplicate of #9005.

[smitpatel]

When you say User & UserDetails are independent entities, they are not. Since, it is table splitting by using same PK column you are using intrinsic FK. Further you are also defining one-to-one identifying relationship in your model building code.
Due to #9005 optional dependents are not allowed. In your case DetailedUser is principal and User is dependent end. And when saving data, you are not providing value for User associated with DetailedUser hence, optional dependent.