Insert data into table which have as part of primary key foreign key - Entity Framework

Question

I am trying to write into a table which has primary key from two parts. One of them is foreign primary key from another table. I am able to create this table, but not insert into.

I am using Entity Framework 6.4.0

My db is created by this code:

class School
{
      public int Id { get; set; }
      public string Name { get; set; }
      public List<Classroom> Classrooms { get; set; }
}

class Classroom
{
      [Key]
      [Column(Order = 0)]
      public int Id { get; set; }

      [Key]
      [Column(Order = 1)]
      [ForeignKey("School")]
      public int SchoolId { get; set; }
      public School School { get; set; }
      public string Name { get; set; }
}

class TestDbContext : DbContext
{
      public DbSet<School> Schools { get; set; }
      public DbSet<Classroom> Classrooms { get; set; }
}

How can I insert data into the Classrooms table? I tried this (school with name: schoolName is in db):

var context = new TestDbContext();`

var school = context.Schools.FirstOrDefault(s => s.Name == "SchoolName");
context.Classrooms.Add(new Classroom() {Name = "5B", School = school});
context.SaveChanges();

Result of this code was this error:

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

I think what you were after is a unique key, which [this question](https://stackoverflow.com/questions/18889218/unique-key-constraints-for-multiple-columns-in-entity-framework) has solutions for. It sounds like your table was generated with `SchoolId` set to be an identity column, which means you can't insert values (unless, as the error notes, you set IDENTITY_INSERT ON in the query, which EF doesn't do by default). — Tieson T., Dec 28 '19 at 00:18

score 1 · Accepted Answer · answered Dec 28 '19 at 07:44

It appears one of the two columns in your table Classroom which make up the primary key is an IDENTITY column - I'm guessing, it's Id.

You need to decorate that column with a data annotation to tell EF that this is an Identity column and you won't be providing any value for it:

class Classroom
{
      [Key]
      [Column(Order = 0)]
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public int Id { get; set; }
      ....
}

This should solve your problem. BUT: if this is an Identity column - then WHY did you create a compound PK together with SchoolId at all? This seems totally unnecessary, since that Id column alone is already your PK - it uniquely and reliably identifies eac and every single row in your table.....

score 0 · Answer 2 · answered Dec 28 '19 at 07:29

0

I guess what you need is ExecuteStoreCommand.

public int ExecuteStoreCommand (string commandText, params object[] parameters);

Try this before adding records to Classrooms:

context.ExecuteStoreCommand("SET IDENTITY_INSERT [dbo].[Classrooms] ON");

answered Dec 28 '19 at 07:29

Shridhar R Kulkarni

6,653
3
37
57

score 0 · Answer 3 · answered Dec 28 '19 at 09:32

By default EF treats Id properties as identity column. As @marc_s noted, there's no sense in having compound key with identity column, so if your Id column is not identity column, then you need to explicitly say about it:

class Classroom
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    // ...other properties..
}

Insert data into table which have as part of primary key foreign key - Entity Framework

3 Answers3