INSERT statement conflicted with FOREIGN KEY SAME TABLE constraint error

Question

This error occurs when adding new category with no parent. I have tried similar question's answers but no result.

Model

public class Category
{
    [Column("CategoryId")]
    public Guid Id { get; set; }
    [Required(ErrorMessage = "Category name is a required field.")]
    [MinLength(1, ErrorMessage = "Minimum length for the Name is 1 characters.")]
    public string Name { get; set; }
    [ForeignKey("ParentId")]
    public Guid? ParentId { get; set; }
    public virtual Category Parent { get; set; }
    public virtual ICollection<Category> Children { get; set; }

}

Request

public class CategoryCreationDto
{
    public Guid ParentId { get; set; }
    public string Name { get; set; }
}

Repository

public void Create(T entity) => RepositoryContext.Set<T>().Add(entity); 
public void CreateCategory(Category category) => Create(category);

Controller

 Category categoryEntity = _mapper.Map<Category>(category);
        _repository.Category.CreateCategory(categoryEntity);
        await _repository.SaveAsync();

Table generated by entity framework

Can't test, but changing the attribute to `[ForeignKey("Parent")]` on the same property _or_ moving the attribute as is to the Parent property would make more sense to me. — Joachim Isaksson, Jul 18 '21 at 08:18
The code you show is largely a black box. We don't see what's in `category`/`categoryEntity`. — Gert Arnold, Jul 18 '21 at 08:29

Piotr Żak · Answer 1 · 2021-07-18T08:44:04.933

2

It's correlated with your entity relations.

Look at this example:

There is issue - when one entity can't exist with another. You can't have Order without Customer and Product.

You need to first INSERT record for *Customer and Product to add Product.

In your specific case:

First add those to db:

 public Guid? ParentId { get; set; }
    public virtual Category Parent { get; set; }
    public virtual ICollection<Category> Children { get; set; }

to add Category.

For optional foreign key:

modelBuilder.Entity<Category>()
    .HasOptional(p => p.Parent)
    .WithMany()
    .HasForeignKey(p => p.ParentId);

edited Jul 18 '21 at 08:44

answered Jul 18 '21 at 08:32

Piotr Żak

2,046
5
18
30

I need to add Category without a parent. Parent property is optional – MuhammedBalta Jul 18 '21 at 08:42
1

Ok - then when mapping relationship - there is need to add optional foreign key(edited answer) – Piotr Żak Jul 18 '21 at 08:43
2

Just for the record - ef core ModelBuilder doesn't have .HasOptional – Serge Jul 18 '21 at 12:54
It's depend on version -> https://stackoverflow.com/questions/35562483/equivalent-for-hasoptional-in-entity-framework-core-1-ef7 – Piotr Żak Jul 18 '21 at 12:55
@PiotrŻak EF core was called EF7 – Serge Jul 18 '21 at 12:57

Serge · Answer 2 · 2021-07-18T13:49:37.347

You will have to add fluent apis (repeat db migration after this)

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
  modelBuilder.Entity<Category>()
            .HasOne(s => s.Parent)
            .WithMany(m => m.Children)
            .HasForeignKey(e => e.ParentId);

 OnModelCreatingPartial(modelBuilder);
 }

I am sorry but since you didn't post what is your mapper doing and what is inside of category, I will post the whole code for the test (it was tested in VS)

        var categoryEntity = new Category {Id=new Guid(), Name="CategoryName"};
        context.Set<Category>().Add(categoryEntity ); 
        await context.SaveChangesAsync();

INSERT statement conflicted with FOREIGN KEY SAME TABLE constraint error

2 Answers2