E.F Core 6 Error insert many to many error inserted into the identity column of table

Question

I have some many-to-many tables and when I save to the database I get the following error.

public class SwUCard
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    [Column(TypeName = "nvarchar(250)")]
    public string? NameEs { get; set; }
     
    [Column(TypeName = "nvarchar(250)")]
    public string? NameEn { get; set; }
     
    [Column(TypeName = "nvarchar(250)")]
    public string? SubtitleEs { get; set; }  
     
    public ICollection<SwUTrait> Traits { get; set; } 
}

public class SwUTrait

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    public string NameEs { get; set; }
    
    public string NameEn { get; set; }
    
    public ICollection<SwUCard> Cards { get; set; }
}

public SwUCardDto Add(SwUCardDto t) 
    
    var d = t.Adapt<SwUCard>();
    _context.SwUCard.Add(d);
    _context.SaveChanges();

    SwUCard dbObjectResult = _context.Set<SwUCard>().Local.LastOrDefault();
    return new ContextToApp.GenericMapper<SwUCard, SwUCardDto>().Create(dbObjectResult);

I create the swucard entity and add the SwUTrait list that is in the database and recover it and insert it into the new swucard entity

the mistake is:

An explicit value cannot be inserted into the identity column of table 'SwUTrait' when IDENTITY_INSERT is OFF.

Answer 1

An explicit value cannot be inserted into the identity column of table 'SwUTrait' when IDENTITY_INSERT is OFF.

Well based on your description and scenario, the error you are getting that indicates that you're trying to set the Id property of an existing SwUTrait entity before adding it to the SwUCard.Traits collection, which violates a database constraint.

As you may know, when you create a new SwUCard entity, you populate its Traits collection with existing SwUTrait objects retrieved from the database. Since these SwUTrait objects already have their Id properties set (presumably auto-generated by the database), attempting to add them to the Traits collection again forces EF Core to insert them into the database again, even though they already exist.

In order to fix the issue, you can do following modification:

public SwUCardDto Add(SwUCardDto t) 
{
    var d = t.Adapt<SwUCard>();

    foreach(var trait in d.Traits)
    {
        trait.Id = 0; 
    }

    d.Traits = d.Traits.Distinct().ToList();

    _context.SwUCard.Add(d);
    _context.SaveChanges();

    SwUCard dbObjectResult = _context.Set<SwUCard>().Local.LastOrDefault();
    return new ContextToApp.GenericMapper<SwUCard, SwUCardDto>().Create(dbObjectResult);
}

Note: By ensuring that the Id property of SwUTrait entities is not explicitly set before saving changes, EF Core should handle the identity column generation correctly.Please refer to this official document for more details.