EF Core - Cannot save more than 1 entity with TPT approach

Question

We are migrating our application .NET Framework and EF6 to .NET 6 and EF Core v6.

Our application uses the TPT approach in one of our models. I have read all the articles related to TPT and some questions here on SO but it seems it does not provide a resolution to my concern.

I have a sample code here based on this article.

    public class TPTUser
    {
        public int Id { get; set; }
        public string Username { get; set; }
        public string Email { get; set; }
    }

    public class TPTStudent : TPTUser
    {
        public string CGPA { get; set; }
        public string Major { get; set; }
    }

    public class TPTTeacher : TPTUser
    {
        public string Designation { get; set; }
        public string Speciality { get; set; }
    }

My DbContext:

public class TestContext : DbContext
    {
        public DbSet<TPTUser> TPTUsers { get; set; }
        public DbSet<TPTStudent> TPTStudents { get; set; }
        public DbSet<TPTTeacher> TPTTeachers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Foo");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TPTUser>().ToTable("TPTUsers");
            modelBuilder.Entity<TPTUser>()
                .HasKey(p => p.Id);
            modelBuilder.Entity<TPTUser>()
                .Property(p => p.Id)
                .ValueGeneratedOnAdd()
                .UseIdentityColumn();

            modelBuilder.Entity<TPTStudent>().ToTable("TPTStudents");
            modelBuilder.Entity<TPTTeacher>().ToTable("TPTTeachers");
        }
    }

I am trying to add 2 TPTStudent instances with the PK having a value (this is just a random int value). Initially, our application generates the model from the client-side and by default, it has a value on the PK.

This is the code for adding the TPTStudent:

static void Main(string[] args)
        {
            using (var context = new TestContext())
            {

                var std = new List<TPTStudent>
                {
                    new TPTStudent()
                    {
                        Id = 422,
                        Username = Guid.NewGuid().ToString("N"),
                        Email = Guid.NewGuid().ToString("N"),
                        CGPA = Guid.NewGuid().ToString("N"),
                        Major = Guid.NewGuid().ToString("N")
                    },
                    new TPTStudent()
                    {
                        Id = 412,
                        Username = Guid.NewGuid().ToString("N"),
                        Email = Guid.NewGuid().ToString("N"),
                        CGPA = Guid.NewGuid().ToString("N"),
                        Major = Guid.NewGuid().ToString("N")
                    }
                };

                context.TPTStudents.AddRange(std);
                context.SaveChanges();
            }
        }

After hitting the SaveAsync, it returns this error:

Cannot insert explicit value for identity column in table 'TPTUsers' when IDENTITY_INSERT is set to OFF

I am aware that the cause of this issue is that the EF Core assumes that if you set the key value, then that's the key value of that entity.

My main goal is to add these entities successfully and EF Core ignoring the values Id property. The values for Id should be generated on the database because its column is set to IDENTITY.

I have tried using ValueGeneratedOnAdd,UseIdentityColumn, setting IsTemporary to true (before SaveChanges), but none of it seems to work.

If what I want to achieve is impossible, then my last resort will be this approach.

EDIT: I found this SO Question and it is exactly what I need. Does anybody knows how to implement this on EF Core v6?

Answer 1

The same thing happend to me. The problem is, id is increasing by database, and on the other hand , entity framework is trying insert same id. Firstly, you can solve it by disabling the autoincrement from database. or you can apply my second solution.

How i solved it.

 public class TPHUser
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Username { get; set; }
        public string Email { get; set; }
    }

the model should be like this.Furthermore, you should enable the identity insert . then you should insert data, after that you should disable the idendity insert. But the main thing, you should do it in one transaction to keep the data consistency. the below code for a transaction insert.i m using unitOfWork. but if you want , you can use same codes without unitOfWork.

   using (var transaction = unitOfWork._context.Database.BeginTransaction())
        {
            await unitOfWork._context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT TPHUser ON");
            var std = new List<TPTStudent>
           {
            new TPTStudent()
            {

                Username = Guid.NewGuid().ToString("N"),
                Email = Guid.NewGuid().ToString("N"),
                CGPA = Guid.NewGuid().ToString("N"),
                Major = Guid.NewGuid().ToString("N")
            },
            new TPTStudent()
            {

                Username = Guid.NewGuid().ToString("N"),
                Email = Guid.NewGuid().ToString("N"),
                CGPA = Guid.NewGuid().ToString("N"),
                Major = Guid.NewGuid().ToString("N")
            }
           }

            var ss = await unitOfWork.TPTStudent_Repostory.AddRange(std);
            await unitOfWork.CompleteAsync();

            await unitOfWork._context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT TPHUser OFF");
            transaction.Commit();
        }