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.
18.226.163.229