This post is part of a series, see parts 1 and 2. Sorry to be so long getting back to this series.

This post covers LINQ to SQL and EF worry #3. That is: LINQ to SQL and EF will encourage "SELECT to middle-tier, then update/delete" rather than issuing SQL UPDATE/DELETE statements that are set-based.

Neither LINQ to SQL or Entity Framework currently contains an insert/update/delete language. Entity SQL could contain DML in the future, LINQ (Language Intergrated QUERY) to SQL doesn't have one. But both APIs can affect INSERT/UPDATE/DELETE operations on the database. You create or manipulate object instances then call SaveChanges (EF) or SubmitChanges (LINQ to SQL).

The manipulate objects and save method works well in LINQ to SQL and reasonably well in EF. The distinction is that in EF, if there are related entities (e.g. a title row contains an integrity constraint that mandates that is title's publisher must exist in the publisher's table), you must fetch the related entity first. Or synthsize a reference using EntityKey (see associated post here) to save the database roundtrip involved in fetching the related entity. See this associated post about deleting a single row without fetching. But how about UPDATE?

SaveChanges and SubmitChanges can boxcar multiple INSERT/UPDATE/DELETE operations in a single roundtrip. But let's consider the number of database roundtrips involved to change a single customer row. This requires one roundtrip to "GET" the row and another to update the row. And what about a searched update in SQL (UPDATE…WHERE) that updates multiple rows? Or an updated based on a SQL join condition (my favorite example, using update over a recusrive CTE, gets all employees reporting to a certain manager and gives them all a raise)? The number of fetches required JUST to do the update increases. Maybe not the roundtrips required to get the rows, but the sheer number of fetches (network/datebase traffic) required.

Let's address the general "GET then UPDATE" pattern first. I worried about this one until I realized that, in most applications I've worked on, you don't usually do a "blind" UPDATE or DELETE. A customer web application fetches a row (and related rows), a pair of eyes inspects the row to ensure this IS indeed the right row, and then presses the gadget that causes an UPDATE/DELETE. So "GET then UPDATE" is an integral part of most applications anyway. OK for now. If UPDATE/DELETE of a row affects related rows, this can be accomplished with cascading UPDATE/DELETE in the database.

But how about multiple, searched, updates without inspecting/fetching ALL the rows involved? Neither LINQ to SQL or EF has a straighforward way to deal with this. AlexJ wrote an excellent 4-part blog series (start here) about rolling your own searched update in EF with an underlying SQL Server using .NET extension methods getting the SQL query text and string handling to turn it into an Update, but its not necessarily what I'd call straightforward. Maybe, wrapped in a library to encapsulate the details… It also looks SQL Server-dependent, and I thought EF wasn't supposed to be. So you'd need to replicate this for each provider.

LINQ to SQL contains the ultimate fallback method for this case. DataContext.ExecuteCommand() lets you execute any SQL command, including parameters. EF doesn't have the equivalent because (remember) your data store is an object model over a conceptual data source, not the data source itself.

I think this is one place (searched UPDATE/DELETE) that I'd suggest/mandate using stored procedures. The blind searched operation is accomplished in a single database roundtrip, and you can even (in SQL Server at least) use the OUTPUT clause in SQL DML to obtain information in rowset form as to exactly what got deleted. Since this is a database-specific operation, stored procedure sounds like a good workaround for this problem.

MHO: With stored procedures as needed and the realization that most apps use "GET then UPDATE" anyway, I think I'll dismiss this worry.