System Design Considerations - Pro SQL Server Internals - page 363

Unfortunately, there is very little that can be done in this scenario. Loading rows one by one or in small batches
is not efficient either. The best approach would be to execute a custom SQL Statement using table-valued parameters,
and to create objects manually afterwards. However, this requires a large amount of coding. With all that being said,
Entity Framework is not the best choice for systems that require batch data processing.
Deletions
Deleting data from within Entity Framework is a tricky process. While it is easy to write the code that performs a
deletion, auto-generated SQL code is not ideal. Let's look at the example shown in Listing 16-18, which deletes a row
in the code.
Listing 16-18. Deleting Data: Client code
int customerID = 10;
var customer = context.Customers.Find(customerID);
context.Customers.Remove(customer);
context.SaveChanges();
Unfortunately, this approach forces Entity Framework to load the Customer object before deletion. It leads to an
additional SELECT statement, as shown in Listing 16-19.
Listing 16-19. Deleting Data: Generated SQL
exec sp_executesql N'SELECT TOP (2)
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Email] AS [Email],
[Extent1].[LastPurchaseDate] AS [LastPurchaseDate],
[Extent1].[CreditLimit] AS [CreditLimit],
[Extent1].[Photo] AS [Photo],
[Extent1].[Ver] AS [Ver]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CustomerId] = @p0',N'@p0 int',@p0=10
exec sp_executesql N'DELETE [dbo].[Customers]
WHERE ([CustomerId] = @0)',N'@0 int',@0=10
You can address this problem by creating a dummy Customer object and attaching it to the model, as shown in
Listing 16-20.
Listing 16-20. Deleting Data: Using dummy object
var customer = new Customer();
customer.CustomerId = 10;
context.Customers.Attach(customer);
context.Customers.Remove(customer);
context.SaveChanges();
Unfortunately, the situation becomes more complicated in cases where referential integrity is involved. Unless
foreign key constraints are defined with an ON DELETE CASCADE action, deletion of the dummy referenced object would
trigger a foreign key violation exception. You can attach dummy referencing objects to the model to avoid this, but it
requires you to know their key values to reference them.