When dealing with DML statements, EF shows some limitations due to its object orientation. An example on all comes with a DELETE statement made in EF. This example shows how to make a master-detail delete operation:
int InvoiceID = 11;
using (var db = new InvoicingDBContainer())
{
//materialize an invoice
//this will produce a SELECT statement
var invoice = db.Invoice
.Include("InvoiceElement") //eager-load elements for deletion
.FirstOrDefault(x => x.InvoiceID == InvoiceID);
//manually load elements for deletion
//no lazy-load works for cascade delete objects
//db.Entry(invoice).Collection("InvoiceElement").Load();
//informs EF context to remove invoice from database
db.Invoice.Remove(invoice);
//asks EF context to persist changed entities
//this will produce the DELETE statement
db.SaveChanges();
}Obviously, the whole selection of the Invoice and of all InvoiceElement instances (both for eager or lazy loading approaches is the same) is mandatory before asking the context to delete them, marking them as removed. This leads to a big resource wastage and heavily increases the execution time of any DELETE statement.
Please note that without specifying the cascade-delete operation on the Invoice and the InvoiceElement relation in EF designer, we will have to iterate all invoice elements of the correct navigation property from the invoice variable.
Do not think that EF has poor DELETE operation performances. Also, note that for the case of manipulating data by hand, any application will need data retrieval before knowing what item to delete. So, the entity instances required to be deleted are, for the most times, already available to your application.
Specifically for data deletion, a simple workaround comes to the rescue:
var ElementID = 9;
using (var db = new InvoicingDBContainer())
{
//create a disconnected instance with needed key
var element = new InvoiceElement
{
//manually set the primary-key value
InvoiceElementID = ElementID,
};
//asks the context for attaching the disconnected instance
db.InvoiceElement.Attach(element);
//mark element with removed status within context change tracker
db.InvoiceElement.Remove(element);
//asks EF context to persist changed entities
//this will produce the DELETE statement
db.SaveChanges();
}It is also possible to make deletions in master-detail scenarios, but the reduced strength of the whole design should convince you to avoid this choice. The same behavior occurs when dealing with the UPDATE statement:
var InvoiceID = 15;
using (var db = new InvoicingDBContainer())
{
//materialize an invoice
//this will produce a SELECT statement
var invoice = db.Invoice
.FirstOrDefault(x => x.InvoiceID == InvoiceID);
//a simple edit
invoice.Number = "updated nr";
//asks EF context to persiste changed entities
//this will produce the UPDATE statement
db.SaveChanges();
}
using (var db = new InvoicingDBContainer())
{
//a fake invoice for updating data
var invoice = new Invoice
{
InvoiceID = InvoiceID,
};
//attach the fake invoice to the context
//this will start the change tracking of such entity
db.Invoice.Attach(invoice);
//a simple edit
invoice.Number = "updated again";
//asks EF context to persiste changed entities
//this will produce the UPDATE statement
db.SaveChanges();
}In the first example, EF will make a SELECT operation and later an UPDATE operation, setting the only edited column to the new value. In the second example, the workaround shows how to have the same result without having to make a SELECT operation. Obviously, as was said before, sometimes such SELECT operations will always be required because we need a valid ID.
This workaround works fine because the change tracker can produce the SQL statement of only the changed properties, leaving all the others intact on the database server.
About the INSERT operation: when dealing with EF, this adds no cost overhead, so no performance concerns exist more than with a legacy SQL statement that is manually written.