- I try to implement hierarchy in database with ef core. I have Retailer and Admin classes that inherits from User class. I try to create migration and use TPT approach.It seems like tables are created for all three classes, but I cannot link the tables with each other with id property. I tried to implement insert sql with MSSQL, it says
"Table 'dbo.Admin' does not have the identity property. Cannot perform SET operation.". Here are my table views for : Admin and Retailer tables User table
- My classes are below:
namespace ECommerce.Data.Domain;
[Table("User", Schema = "dbo")]
public class User : BaseModel
{
public string Name { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string UserName { get; set; }
public int RoleId { get; set; }
public virtual Role Role { get; set; }
public virtual List<Address> Addresses { get; set; }
public DateTime LastActivityDate { get; set; }
public int PasswordRetryCount { get; set; }
}
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.Property(x => x.InsertUserId).IsRequired();
builder.Property(x => x.UpdateUserId).IsRequired().HasDefaultValue(0);
builder.Property(x => x.InsertDate).IsRequired();
builder.Property(x => x.UpdateDate).IsRequired(false);
builder.Property(x => x.IsActive).IsRequired().HasDefaultValue(true);
builder.Property(x => x.Id).IsRequired(true);
builder.Property(x => x.Email).IsRequired(true).HasMaxLength(50);
builder.Property(x => x.Password).IsRequired(true).HasMaxLength(50);
builder.Property(x => x.Name).IsRequired().HasMaxLength(50);
builder.Property(x => x.LastName).IsRequired().HasMaxLength(50);
builder.Property(x => x.UserName).IsRequired(true).HasMaxLength(10);
builder.Property(x => x.RoleId).IsRequired(true);
builder.Property(x => x.LastActivityDate).IsRequired();
builder.Property(x => x.PasswordRetryCount).IsRequired().HasDefaultValue(0);
builder.HasIndex(x => x.Id).IsUnique(true);
builder.HasIndex(x => x.Email).IsUnique(true);
builder.HasIndex(x => x.UserName).IsUnique(true);
builder.HasMany(x => x.Addresses)
.WithOne(x => x.User)
.HasForeignKey(x => x.UserId)
.IsRequired(true);
}
}
[Table("Admin", Schema = "dbo")]
public class Admin : User
{
}
public class AdminConfiguration : IEntityTypeConfiguration<Admin>
{
public void Configure(EntityTypeBuilder<Admin> builder)
{
builder.Property(x => x.InsertUserId).IsRequired();
builder.Property(x => x.UpdateUserId).IsRequired().HasDefaultValue(0);
builder.Property(x => x.InsertDate).IsRequired();
builder.Property(x => x.UpdateDate).IsRequired(false);
builder.Property(x => x.IsActive).IsRequired().HasDefaultValue(true);
builder.Property(x => x.Id).IsRequired(true);
builder.Property(x => x.Name).IsRequired().HasMaxLength(20);
}
}
namespace ECommerce.Data.Domain;
[Table("Retailer", Schema = "dbo")]
public class Retailer : User
{
//retailer may have multiple receipt infos and choose between them in order process.
public virtual List<ReceiptInfo> ReceiptInfos { get; set; } = new List<ReceiptInfo>();
public virtual List<Order> Orders { get; set; } = new List<Order>();
}
public class RetailerConfiguration : IEntityTypeConfiguration<Retailer>
{
public void Configure(EntityTypeBuilder<Retailer> builder)
{
builder.Property(x => x.InsertUserId).IsRequired();
builder.Property(x => x.UpdateUserId).IsRequired().HasDefaultValue(0);
builder.Property(x => x.InsertDate).IsRequired();
builder.Property(x => x.UpdateDate).IsRequired(false);
builder.Property(x => x.IsActive).IsRequired().HasDefaultValue(true);
builder.Property(x => x.Id).IsRequired();
builder.HasMany(x => x.Orders)
.WithOne(x => x.Retailer)
.HasForeignKey(x => x.RetailerId)
.IsRequired(true);
builder.HasMany(x => x.ReceiptInfos)
.WithOne(x => x.Retailer)
.HasForeignKey(x => x.RetailerId)
.IsRequired(true)
.OnDelete(DeleteBehavior.Restrict);
}
}
My dbcontext configuration:
public class ECommerceDbContext : DbContext
{
public ECommerceDbContext(DbContextOptions<ECommerceDbContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>().UseTptMappingStrategy();
modelBuilder.ApplyConfiguration(new AddressConfiguration ());
modelBuilder.ApplyConfiguration(new AdminConfiguration());
modelBuilder.ApplyConfiguration(new RetailerConfiguration());
modelBuilder.ApplyConfiguration(new UserConfiguration());
base.OnModelCreating(modelBuilder);
}
I researched various examples on the internet and nobody added an extra Id field to their child classes. I also didn't since child classes automatically inherits Id property from User class. I saw tables have Id property in my initial migration. I could add id to the Admin and Retailer tables before. Than I had to create migration from start.
I am very new into this topic. Tell me if I am wrong but shouldn't migration automatically link tables to each other with Id column? I see nothing in my child classes.
I also add my migration file:
migrationBuilder.CreateTable(
name: "User",
schema: "dbo",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
LastName = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
Email = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
Password = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
UserName = table.Column<string>(type: "nvarchar(10)", maxLength: 10, nullable: false),
RoleId = table.Column<int>(type: "int", nullable: false),
LastActivityDate = table.Column<DateTime>(type: "datetime2", nullable: false),
PasswordRetryCount = table.Column<int>(type: "int", nullable: false, defaultValue: 0),
InsertUserId = table.Column<int>(type: "int", nullable: false),
InsertDate = table.Column<DateTime>(type: "datetime2", nullable: false),
UpdateUserId = table.Column<int>(type: "int", nullable: false, defaultValue: 0),
UpdateDate = table.Column<DateTime>(type: "datetime2", nullable: true),
IsActive = table.Column<bool>(type: "bit", nullable: false, defaultValue: true)
},
constraints: table =>
{
table.PrimaryKey("PK_User", x => x.Id);
table.ForeignKey(
name: "FK_User_Role_RoleId",
column: x => x.RoleId,
principalSchema: "dbo",
principalTable: "Role",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "Admin",
schema: "dbo",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Admin", x => x.Id);
table.ForeignKey(
name: "FK_Admin_User_Id",
column: x => x.Id,
principalSchema: "dbo",
principalTable: "User",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "Retailer",
schema: "dbo",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Retailer", x => x.Id);
table.ForeignKey(
name: "FK_Retailer_User_Id",
column: x => x.Id,
principalSchema: "dbo",
principalTable: "User",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
I can also see that my Id property is created.
Could somedoby please help me to implement TPT approprietly? Thank you for your help.