EF Core: Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF on one to many

Question

I am using EF Core together with ASP NET Core for my server, and when I am trying to update an existing value in the database I receive the following error:

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

What I am doing is this:

1. creating a Teambuilding element, with the foreign key for the TeamBuildingTypeId set to NULL initially

2. creating two TeambuildingType directly from the SQL Management Studio using INSERT INTO.... (the Id is auto incremented for both the Teambuilding and TeambuildingType)

3. trying to update the existing Teambuilding by adding either the TeambuildingTypeId like this: team.TeambuildingTypeId = 1 or team.Type = (object fetched from the database in the same context)

4. receiving the error from above in a catch

Here is my code:

TeamBuilding.cs

public class TeamBuilding
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }

    public DateTime StartDate { get; set; }

    public DateTime EndDate { get; set; }

    public double? TargetBudget { get; set; }

    public TeambuildingStatus? Status { get; set; }

    public int? TeambuildingTypeId { get; set; }

    public virtual TeambuildingType Type { get; set; }

    public int? LocationId { get; set; }

    public virtual Location Location { get; set; }

    public virtual ICollection<Participant> Participants { get; set; }

    public virtual ICollection<Room> Rooms { get; set; }

    public int TimePollId { get; set; }

    public virtual TimePoll TimePoll { get; set; }
}

TeambuildingType.cs

public class TeambuildingType
{
    [Key]
    public int Id { get; set; }

    public string Type { get; set; }

    public virtual ICollection<TeamBuilding> Teambuildings { get; set; }
}

TeamBuildingForUpdateDto.cs

public class TeamBuildingForUpdateDto
{
    [Required]
    public int Id { get; set; }
    public string Title { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime StartDate { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime EndDate { get; set; }
    public LocationViewModel Location { get; set; }
    public TeambuildingStatus Status { get; set; }
    public double TargetBudget { get; set; }
    public TeamBuildingTypeDto Type { get; set; }
}

The update controller method:

    [HttpPut]
    public IActionResult UpdateTeamBuilding([FromBody]TeamBuildingForUpdateDto teamBuildingForUpdateDto)
    {
        try
        {
            var existingTeamBuilding = _service.GetByID(teamBuildingForUpdateDto.Id);
            if (existingTeamBuilding == null)
            {
                return NotFound("There is no team buiding with such an ID");
            }

            _service.UpdateTeamBuilding(teamBuildingForUpdateDto);
            return Ok();
        }
        catch (Exception ex)
        {
            return BadRequest(ex.Message);
        }
    }

The service method:

    public TeamBuildingForUpdateDto UpdateTeamBuilding(TeamBuildingForUpdateDto teamBuildingDto)
    {
        var teamBuilding = _repositoryTeam.GetByID(teamBuildingDto.Id);
        var type = _repositoryType.GetByID(teamBuildingDto.Type.Id);
        Mapper.Map(teamBuildingDto.Type, type);
        Mapper.Map(teamBuildingDto, teamBuilding);

        teamBuilding.Type = type;
        //OR
        //teamBuilding.TeambuildingTypeId = type.Id;
        //Neither from above works

        _repositoryTeam.Edit(teamBuilding);
        _repositoryTeam.Commit();
        return teamBuildingDto;
    }

Context using the Fluent API:

            modelBuilder.Entity<Models.TeamBuilding>()
            .HasOne(t => t.Type)
            .WithMany(ty => ty.Teambuildings)
            .HasForeignKey(t => t.TeambuildingTypeId);

            modelBuilder.Entity<TeambuildingType>().ToTable("TeambuildingType");
            modelBuilder.Entity<Models.TeamBuilding>().ToTable("TeamBuilding");


        public DbSet<TeambuildingType> TeambuildingTypes { get; set; }
        public DbSet<Models.TeamBuilding> TeamBuildings { get; set; }

I also don't receive this error on those models only, I receive the same thing on anything that uses FK and on where I try to insert a new value in there.

The relationship is one to many between the TeamBuilding and the TeambuildingType

What am I doing wrong?

Answer 1

I fixed the problem. As per mcbowes suggestion I checked the AutoMapper and what I send from the server, and I saw that I was trying to assign a TeamBuildingType in my TeamBuilding Type field, then trying to do the update.

I fixed the problem by not assigning any TeamBuildingType to the Type field (making it being null) and assigning only the TeamBuildingType primary key to the TeambuildingTypeId field. Now it does the update.

Thanks mcbowes for the suggestion.