2

How do I add a new child record in Entity Framework and update the ParentId reference key?

We have an Employee and a ProductSales table.

The Employee table contains column of most recent ProductSaleId.

If I add a new entry to ProductSale, it will acquire a new identity column in the SQL Server database.

lastProductSale = new ProductSale { Product = "Furniture", Amount= 1000, EmployeeId = 5};
_dbContext.Add(productSale);
_dbContext.SaveChanges();
int lastProductSaleId = lastProductSale.productSaleId;

Now updating the Employee table reference key like this:

employee.ProductSaleId = lastProductSaleId;
_dbContext.SaveChanges();

We know the EmployeeId which is 5. However, this takes two transactions from two save operations.

I want to this to be done in one transaction. How can this be completed?

Using EF Core 3.1, Entities are scaffolded with foreign keys

Resource:

How can I retrieve Id of inserted entity using Entity framework?

  • hi @devlincarnate yeah, I have a foreign key setup in DB and EF –  Aug 20 '20 at 22:02
  • 1
    Oh wait... LastProductSaleId is only the id of the most recent product sold. Sorry, I misunderstood the relationship. As far as I know, you will have to do 2 saves. However, you can use `TransactionScope` to manage the case where one save fails. – devlin carnate Aug 20 '20 at 22:12
  • Note that this is a problematic database design. You should avoid storing `LastProductSaleId` unless you absolutely know, because you tried it, that computing it on demand is too slow. – Aluan Haddad Aug 21 '20 at 03:30
  • 1
    Yes, this database design has problem. You don't need LastProductSaleId in employee table. Whenever you want display employee's latest sale, then get latest sale data using employeeid + date. Anyway, if you want one transaction, it's impossible because you need to use savechanges to be able to get new id. – Asherguru Aug 21 '20 at 03:40
  • 1
    hi @Asherguru I agree, I told db architect that, just doing my job, but question is more conceptual, Insert child and update Parent in one Transaction –  Aug 21 '20 at 16:35

1 Answers1

0

Entity Framework automatic scaffold was setup. So the following worked, Instead of using employee.ProductSaleId, just use ProductSale, and update the Parent directly.

productSale = new ProductSale { Product = "Furniture", Amount= 1000, EmployeeId = 5};
employee.ProductSale = productSale;
_dbContext.SaveChanges();

Entity Framework Core will automatically adjust with Insert or Update