INSERT语句与Entity Framework Core中的FOREIGN KEY约束冲突 [英] The INSERT statement conflicted with the FOREIGN KEY constraint in Entity Framework Core
问题描述
我使用的是Entity Framework 7 RC1,我有以下实体:
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 是默认设置,其中 public DbSet< Book>书{组; }
当我尝试插入新书时,像这样
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();
抛出异常:
SqlException:INSERT语句与FOREIGN KEY约束 FK_Book_Author_AuthorAuthorId冲突。
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_Author_AuthorAuthorId".
这个简单的插入有什么问题?我不想手动添加BookPage和Author,它是Entity Framework的工作。
What's the problem with this simple insert? I don't want manually add BookPage and Author, it's Entity Framework job.
推荐答案
在您的 ApplicationDbContext中
类,您必须为 Author
类和<$ c添加一个 DbSet
$ c> BookPage 类:
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();
无论如何您不应该使用 Guid
作为主键(实际上是聚集索引),这在使用SQL Server时是一种不好的做法。
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?
另一种方法是使用一个身份列作为主键,并添加另一个具有唯一约束的列(它将包含唯一ID),因此您的模型看起来像这样:
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; }
}
在您的 DbContext ,您可以指定唯一约束:
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);
}
}
并添加您的新实体:
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();
}
这篇关于INSERT语句与Entity Framework Core中的FOREIGN KEY约束冲突的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!