Entity framework - Reducing round trips to the database

I'm writing an app using WPF, Entity framework and SQLServer, all very run of the mill stuff. I was having a look at what calls get made to the database using sql profiler and found quite a few unnecessary calls. The first one was solved pretty easy but I have included it for anyone reading this thread in the future. Assuming I have a table structure with 3 tables like this Invoice->InvoiceDetail->Product

1) When I load up an Invoice object, it will then execute a seperate statement to retrieve each InvoiceDetail item. This is solved pretty easy by using the Include statement, eg

context.Invoices.Include("InvoiceDetails").Where(i => i.Something == somethingelse);

2) When I delete an Invoice the database has a cascade delete which automatically deletes all of the InvoiceDetails. However EF still insists on calling a delete for each of the InvoiceDetail objects that it has in memory. If an invoice has 100 items on it then it will execute 101 statements instead of 1. This is bad.

3) In addition to the extra statements executed in point 2, assuming each InvoiceDetail object points to a product and I have caused the products to loaded into memory (this would happen if I showed the invoice before I deleted it) then EF executes a useless update statement on every product!!!! In fact this update statement is more than useless because if someone else has cha开发者_如何学Cnged something about the product in the mean time then this code will change the data back!! If I'm logging changes then we get useless log entries. I suspect it is doing this because Product would have had an InvoiceDetails collection which has had some items removed, but the Product itself has not changed so why the update?

Thanks for reading Cheers, Michael

1. The initial behavior was something known as lazy loading. You have replaced it with eager loading which is exact solution for this problem.
2. For entity framework this is the only correct behavior because EF doesn't support any batch modifications. Every record must be deleted with its own statement and round trip to the database. Once you load entities to memory you simply have to delete them one by one otherwise you will get exception before any database call will be done (= database cascade delete will not help you). The only workaround is custom stored procedure for deletion and disposing the current context after running the stored procedure because its internal state will not be consistent with the database.
3. This is interesting. It would require little bit more investigation but it can be simply design flaw / bug in EF and you will most probably not avoid it (unless you use stored procedure as described in 2.). If you want to avoid overwriting changes in Product you must involve optimistic concurrency. In such case your changes will not be overwritten but your delete will fail with OptimisticConcurrencyException. I will check this behavior later and let you know if I'm able to reproduce it and find any workaround.

I've been using this as a solution to let SQL Server handle the cascading deletes without the EF hit.

  Public Sub DeleteCheckedOutByUser(ByVal username As String)
    Dim cmd As String = String.Format("delete  Maintenance.CheckoutManager where CheckOutTo = '{0}'", username)
    _context.ExecuteStoreCommand(cmd)
  End Sub

Sorry it's in VB, that's what my current client is using. If you have any trouble translating what I'm saying just let me know.

To remove the cascading deletes (and presumably rely on SQL Server to do the deletes), see the approach here: http://geekswithblogs.net/danemorgridge/archive/2010/12/17/ef4-cpt5-code-first-remove-cascading-deletes.aspx