Basically I am mixing EF with one call to a stored procedure which does some batch deletions, otherwise EF is too slow.
Here is some pseudo-code of this scenario (I have more complex code in reality):
public void RemoveCustomer(int customerUID)
{
// this code is running in one db transaction
{
// retrieve certain orders of particular customer using EF
var orders = repoOrders.GetOrdersOfCustomer(filter, customerUID);
// do something with above orders before deletion using EF
repoX.DoSomethingWithOrders(orders);
// call SP to delete all orders of customer
repoOrders.DeleteAllOrdersOfCustomer(customerUID); // this calls a stored procedure
// delete customer using EF
repoCustomers.DeleteCustomer(customerUID); // throws exception due to relationship!
}
}
of course, customers-orders is one-to-many
(1:m) relation.
I want to avoid in the above scenario the exception which is thrown when there will be some orders loaded by the context belonging to the customer that gets deleted. Exception is:
"The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted."
So, I want to know if is possible to clear some/all orders from <DbSet>.Local
without causing any change into the database after calling the stored procedure and before user gets deleted.
I guess Detach could be used, but this means I should loop through.
What would you recommend?
Edit: I am new to EF and I am integrating EF now after the repositories were done using ADO.NET, and yes, the BL has been kept the same... so I try this integration with minimal efforts at this point.
Note: I can't make changes on the database structure.
I'd personally make each repository method use its own context, save its own changes at the end of the method, etc., and then use a TransactionScope
to ensure that the operations are atomic.
void DeleteAllOrdersOfCustomer(Guid customerUID)
{
using (var context = new Context())
{
...
context.SaveChanges();
}
}
...
using (var ts = new TransactionScope())
{
// call SP to delete all orders of customer
repoOrders.DeleteAllOrdersOfCustomer(customerUID); // this calls a stored procedure
// delete customer using EF
repoCustomers.DeleteCustomer(customerUID); // throws exception due to relationship!
ts.Complete();
}