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.
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).