EF Core - problem with inheritance from table and use primary key in the next table

user_14713516 0
Viewed 140

I tried to create database using TPT. I have nine entities but when i launch DbInitializer its give me following exception

"System.InvalidOperationException: 'The instance of entity type 'Boss' cannot be tracked because another instance with the same key value for {'UserId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using " but sometimes i have this exception "cannot insert explicit value for identity column in table when identity insert is set to off"

I used TPT becouse my Bosses and Employees inheritance from User but i use the same key in the Companies from Bosses.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Boss>()
            .HasOne(c => c.Company)
            .WithOne(b => b.Boss)
            .HasForeignKey<Company>(c => c.UserId);

        modelBuilder.Entity<LevelSpecializationOfAdvance>()
            .HasKey(ls => new { ls.SpecializationId, ls.LevelOfAdvanceId, ls.UserId});

        modelBuilder.Entity<LevelSpecializationOfAdvance>()
            .HasOne(e => e.Employee)
            .WithMany(l => l.LevelSpecializationOfAdvances)
            .HasForeignKey(e => e.UserId);

        modelBuilder.Entity<Boss>().ToTable("Bosses");
        modelBuilder.Entity<Employee>().ToTable("Employees");
    }

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }

    public int RoleId { get; set; }
    public virtual Role Role { get; set; }

}

public class Boss : User
{
    public string UniqueCode { get; set; }
    public Company Company { get; set; }
}

public class Company
{
    public int CompanyId { get; set; }
    public string NIP { get; set; }
    public string CompanyName { get; set; }
    public string Code { get; set; }

    public virtual List<Employee> Employees { get; set; }
    public int UserId { get; set; }
    public virtual Boss Boss { get; set; }
}

public class Employee: User
{
    public int PhoneNumber { get; set; }
    public int CompanyId { get; set; }
    public List<LevelSpecializationOfAdvance> LevelSpecializationOfAdvances { get; set; }
}

Code in DBInitializer

            if (!context.Users.Any())
            {
                var users = GetUsers();
                context.AddRange(users);
                context.SaveChanges();
            }
            if(!context.Bosses.Any())
            {
                var bosses = GetBosses();
                context.AddRange(bosses);
                context.SaveChanges();
            }

    public IEnumerable<Boss> GetBosses()
    {
        var bosses = new List<Boss>()
        {
            new Boss()
            {
                UserId = 2,
                UniqueCode = "test"
            }
        };
        return bosses;
    }

Screen with keys in the database

enter image description here

entity-framework-4 .net-core asp.net c#
1 Answers

"Cannot insert explicit value for identity column in table when identity insert is set to off"

Instead of:

            new Boss()
            {
                UserId = 2,
                UniqueCode = "test"
            }

Do:

                Role role = //load an existing role from your db context
                new Boss()
                {
                    UniqueCode = "test",
                    RoleId=role.Id
                }

If your table is configured with Auto-increment (each new entity gets a generated primary key based on the last inserted one) and you are using .Add() or .AddRange() you should NOT specify a primary key value (because again, auto-increment means that it gets generated for you). Note that this is only when you are using EF. If instead you were writing SQL code you should specify the primary key value as well.

When creating an entity, always remember to put its relationship data as well. The Boss model has a foreign key RoleId, because it inherits from the model User. By default foreign keys can NOT be NULL.

Also the way you've created your relationships a single role can be owned by many users, but one user wouldn't be able to have many roles. That means a user can only have an Admin/Seller or Premium role for example. A user can NOT have more than 1 role. Consider making a many to many table so you can have the option of giving a single user many roles. For example you can have Sellers without the Premium role and Sellers with the Premium role and based on that you can implement additional logic.

user_184616470
Related