Entity Framework: insert an object with a foreign key that is already known but does not exist yet in the DB
I am using Entity Framework and want to insert an object into a database that references another object that does not already exist in the DB (but the foreign key is already known).
Simple example: suppose I have two tables, Books
(identified by their ISBN) and Purchases
. I would like to be able to embed purchases even if the workbook is not yet installed in the DB:
public class Book
{
[Key]
public virtual string ISBN { get; set; }
protected virtual IEnumerable<Purchase> purchases { get; set; }
public virtual string author { get; set; }
public virtual string title { get; set; }
// .....
}
public class Purchase
{
[Key]
protected virtual int id { get; set; }
public virtual Book book { get; set; }
public virtual double price;
}
Conceptually this would be easy: the table Purchase
would have a foreign key, which is an ISBN book. So in regular SQL, I could just insert the ISBN into that field and add the book to the books table after a while.
In EF / C #, although I can't think of an easy way to do this. The class Purchase
refers to the class Book
, so I can't just use the ID (ISBN) right here.
What I can do is create a dummy book object and use that to insert the purchase. The problem is that Entity Framework automatically adds a stub object to the table as well, which I want to avoid:
var dummyBook = new Book();
dummyBook.isbn = "978-0141354217";
var purchase = new Purchase();
purchase.price = 99.95;
purchase.book = dummyBook;
dbContext.Purchases.Add(purchase);
// Should add the purchase to the DB, but NOT the book!
// Unfortunately, EF adds both automatically...
dbContext.SaveChanges();
Is there a way to do this?
Thank!
source to share
Although the ISBN is (should be) unique and seems like a good candidate for a primary key, it is never recommended to use any natural key as the primary key in a database.
Details here: http://en.wikipedia.org/wiki/Natural_key
So, once the ISBN is not your primary key, but just a string field, you can do what you want: insert a purchase for a specific ISBN without a book entry. In this case, you would not have a virtual Book
class property Purchase
. You can replace it with something like this:
public Book GetBook (string isbn, DbContext context)
{
return context.Books.Where(x => x.ISBN == isbn).FirstOrDefaul();
}
All of this without discussing why you would have a purchase of a book that doesn't exist in the DB.
source to share