The INSERT statement conflicted with the FOREIGN KEY constraint in Entity Framework Core

Question

I'm using Entity Framework 7 RC1, I have these entities:

public class Book
{
    [Key]
    public string BookId { get; set; }
    public List<BookPage> Pages { get; set; }
    public Author Author { get; set; }
    public string Text { get; set; }
} 

public class BookPage
{
    [Key]
    public string BookPageId { get; set; }
    public int Number { get; set; }
}

public class Author
{
    [Key]
    public string AuthorId { get; set; }
    public string FullName { get; set; }
}

ApplicationDbContext.cs is default, with public DbSet<Book> Books { get; set; }

When I'm trying to insert new Book, like this

var book = new Book()
{
    BookId = Guid.NewGuid().ToString(),
    Pages = new List<BookPage>()
    {
        new BookPage()
        {
            BookPageId = Guid.NewGuid().ToString(),
            Number = 1
        }
    },
    Author = new Author()
    {
        AuthorId = Guid.NewGuid().ToString(),
        FullName = "Forexample"
    },
    Text = "new book"
};
dbContext.Books.Add(book);
dbContext.SaveChanges();

It throws exception:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_Author_AuthorAuthorId".

What's the problem with this simple insert? I don't want manually add BookPage and Author, it's Entity Framework job.

If your ids are Guids why are use using `string`? Also I'm pretty sure you have to `dbContext.BookPages.Add` the `BookPage`s and the same with the new `Author`. — juharr, Apr 11 '16 at 11:57
Try first adding BookPage and Author in respective table and then use same Guid in Book's BookPage and Author. — PKV, Apr 11 '16 at 11:58
@PKV this is simple example, but if I'll have more entities, what should I do with them? Add hundred if entities to database manually? — Yurii N., Apr 11 '16 at 12:02
Can you provides us the code of the `protected override void OnModelCreating(ModelBuilder modelBuilder)` method — Thomas, Apr 12 '16 at 04:49

score 2 · Accepted Answer · edited May 23 '17 at 11:48

In your ApplicationDbContext class you have to add a DbSet for the Author class and for the BookPage class :

public class ApplicationDbContext : DbContext
{
    public DbSet<Book> Books { get; set; }

    public DbSet<BookPage> BookPages { get; set; }

    public DbSet<Author> Authors { get; set; }

    ...
}

So that you can rewrite your code like that :

var author = dbContext.Authors.Add(new Author()
{
    AuthorId = Guid.NewGuid().ToString(),
    FullName = "Forexample"
}).Entity;

var page = dbContext.BookPages.Add(new BookPage()
{
    BookPageId = Guid.NewGuid().ToString(),
    Number = 1
}).Entity;


var book = new Book.Book()
{
    BookId = Guid.NewGuid().ToString(),
    Pages = new List<BookPage>(),
    Text = "new book"
};
book.Pages.Add(page);
book.Author = author ;
dbContext.Books.Add(book);
dbContext.SaveChanges();

Anyway you shouldn't use Guid as a primary key (in fact as a clustered index), this is a bad practice using SQL Server.

You can have a look at this post for more information :

What are the best practices for using a GUID as a primary key, specifically regarding performance?

An alternative is to use an identity column as the primary key and add a other column (which will contain the unique id) with a unique constraint so that your model could look like that :

public class Author
{
    public Author()
    {
        AuthorUid = Guid.NewGuid();
    }

    public int AuthorId { get; set; }
    public Guid AuthorUid { get; set; }
    public string FullName { get; set; }
}

public class Book
{
    public Book()
    {
        BookUid = Guid.NewGuid();
        Pages = new List<BookPage>();
    }

    public int BookId { get; set; }
    public Guid BookUid { get; set; }
    public List<BookPage> Pages { get; set; }
    public Author Author { get; set; }
    public string Text { get; set; }
}

public class BookPage
{
    public BookPage()
    {
        BookPageUid = Guid.NewGuid();
    }

    public int BookPageId { get; set; }
    public Guid BookPageUid { get; set; }
    public int Number { get; set; }
}

In your DbContext, you can specify the unique constraints:

public class BookContext : DbContext
{
    public DbSet<Book> Books { get; set; }

    public DbSet<BookPage> BookPages { get; set; }

    public DbSet<Author> Authors { get; set; }

    ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>().HasAlternateKey(a => a.AuthorUid);
        modelBuilder.Entity<Book>().HasAlternateKey(a => a.BookUid);
        modelBuilder.Entity<BookPage>().HasAlternateKey(a => a.BookPageUid);
    }
}

And add your new entities like that :

using (var dbContext = new BookContext())
{
    var author = dbContext.Authors.Add(new Author()
    {
        FullName = "Forexample"
    }).Entity;

    var page = dbContext.BookPages.Add(new BookPage()
    {
        Number = 1
    }).Entity;

    var book = new Book.Book()
    {
        Text = "new book"
    };

    book.Pages.Add(page);
    book.Author = author;

    dbContext.Books.Add(book);
    dbContext.SaveChanges();
}

yes, only create book, wiith bookpages and author inside, and then add to DbSet only book. — Yurii N., Apr 12 '16 at 09:42
I don't think so because you need to attach the author and the bookpages to the EF context. But I am going to check this for you — Thomas, Apr 12 '16 at 09:45
Yes you need to tell EF explicitly which element you want to insert — Thomas, Apr 12 '16 at 09:52

The INSERT statement conflicted with the FOREIGN KEY constraint in Entity Framework Core

1 Answers1

Linked