MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 3

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.

3 thoughts on “MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 3

  1. Hey Bob,

    Although this isn’t nearly as flexible as being able to call LINQ to SQL’s ExceuteCommand and call a command on the fly, it is possible to build raw SQL directly into the model with a Defining View. [http://www.thedatafarm.com/blog/2007/11/02/EDMQueryViewsVsDefiningQueriesAndReadonlyViews.aspx].

    Alex James has been showing how to possibly implement calls that can do updates and deletes without pulling data into memory first but the fact that it took 4 blog posts to lay out demonstrates how complex it is. OTOH – I imagine (hope) once they have this worked out, it will be implemented and easily accessible in v2.

    I’m glad that in the long run, you tag this as not something to be too worried about, though. 🙂

    Great series so far!

    Julie

  2. I don’t see how you can say that most web applications use "GET then UPDATE". The GET and the UPDATE are (I’d say 99% of the time) done on separate web requests. This makes them two very distinct, and separate, actions. There’s a new thread, a new request, and a new data context. I’m assuming that’s what you’re calling a blind update. I’ve never seen a web app that doesn’t do it this way. Take REST for instance. The UPDATE action takes an ID, and retrieves all the data from the http form. This typically results in a single SQL statement getting executed that updates that record.

    The detached context scenario that LINQ handles so poorly is the lifeblood of web applications. I would definitely not dismiss the poor support LINQ has for detached context updates.

  3. Hey Bob,

    I wrote a post about performing batch updates and deletes in single database statement and not querying the data first. You can take a peek at Batch Updates and Deletes with LINQ to SQL and see if it helps ease the concerns you have 😉 It’s been working great for me so far.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.