EF Core: The INSERT statement conflicted with the FOREIGN KEY constraint

Question

EF Core: The INSERT statement conflicted with the FOREIGN KEY constraint

c# entity-framework entity-framework-core

Question

I'm new in Entity Framework Core and I have a problem with the next situation.

I have three objects, like this:

TableA {

    public int Id {get; set;}
    public string Name {get; set;}

    public IList<TableA_TableB> TableA_TableBList {get; set;}
}

TableA_TableB {
    public int Id {get; set;}

    public TableA TableA {get; set;}
    public int TableAId {get; set;}

    public TableB TableB {get; set;}
    public int TableBId {get; set;}
}


TableB {
    public int Id {get; set;}
    public string Name {get; set;}

    public IList<TableA_TableB> TableA_TableBList {get; set;}
}

I'm using the Fluent Api to map the relationship:

builder
    .HasOne(a_b => a_b.TableA) 
    .WithMany(a => a.TableA_TableBList) 
    .HasForeignKey(a_b => a_b.TableAId); 

builder
    .HasOne(a_b => a_b.TableB) 
    .WithMany(b => b.TableA_TableBList) 
    .HasForeignKey(a_b => a_b.TableBId);

My problem is when I try to insert a new value in TableA with a new value in TableA_TablesB, but there is an existent value TableB. The code that I used is:

var entity = (TableA)tableADto; //From controller and convert to TableA using explict operator

entity.TableA_TableBList = new List<TableA_TableBList>();
entity.TableA_TableBList.Add(new TableA_TableB()
{
    TableAId = entity.Id,
    TableBId = 1 // I put a fix value just to test
});

_tableARepository.Add(entity);
_unitOfWork.Commit();

When I call SaveChanges(), this error is shown:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TableA_TableB_TableB_TableBId".
The conflict occurred in database "tenantteste", table "dbo.TableB", column 'Id".

I have tried to get TableB value and add a navigation property (TableB property). But, instead of creating a relationship, the value was added when I saved.

I have read the documentation and some questions on stackoverflow, and I believe using FK should work, but it didn't work.

I don't know what I'm doing wrong.

Marina Uliano

View more on Stack Overflow

David Browne - Microsoft · Accepted Answer · 7/27/2018 3:30:16 PM

There are a couple of things wrong. Your linking table should not have a surrogate key, for one. Here's the Many-to-Many sample from the docs:

class MyContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PostTag>()
            .HasKey(t => new { t.PostId, t.TagId });

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Post)
            .WithMany(p => p.PostTags)
            .HasForeignKey(pt => pt.PostId);

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Tag)
            .WithMany(t => t.PostTags)
            .HasForeignKey(pt => pt.TagId);
    }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class PostTag
{
    public int PostId { get; set; }
    public Post Post { get; set; }

    public string TagId { get; set; }
    public Tag Tag { get; set; }
}

https://docs.microsoft.com/en-us/ef/core/modeling/relationships#other-relationship-patterns

Note that the linking entity has a compound key of (PostId,TabId).

EF Core: The INSERT statement conflicted with the FOREIGN KEY constraint

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related