Perform conditional bulk update in Linq to SQL

Question

I have a SQL table that stores photos with a smallint SortOrder field. Users can insert new photos, specifying a decimal sort order to place the new record between 2 existing photos (or before the first photo). The SortOrder will be stored as a smallint, so when I detect that an insertion will shift existing records, I need to update all affected photos to increment the SortOrder by 1.

This is easy to do in a stored procedure, but I'm looking for the most efficient way to accomplish this with Linq to SQL. If I have to pull all of the records down to the client, update them, and then submit them, then I will just stick with the stored procedure that is already working and very fast.

Here's the T-SQL that shifts the records:

    UPDATE      Photo
    SET         SortOrder = SortOrder + 1
    WHERE       AlbumId = @AlbumId
    AND         SortOrder >= CEILING(@SortOrder)

Is there a way to do this kind of bulk update in Linq to SQL without having to fetch the records?

Answer 1

One option would be to build the sql string that was in the stored procedure and execute it via your DataContext.ExecuteQuery method. Doing it this way would prevent the records from being fetched.

Answer 2

LINQ to SQL doesn't do CUD statements for sets, so stick with your existing implementation as it would be the best in your scenario.

Answer 3

Yes, you'd have to pull down your objects, manipulate them, and push them back.

Is the sproc something that the client is responsible for calling when pushing a new photo? You might do well to set it up as a trigger, instead, so your application is not directly responsible for the extra (easily forgotten) step. This is a trade-off in complexity, of course, and a matter of preference.

Answer 4

I had a lot of success with this guys work: http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

I've only been using it in development for a couple of months, but so far it's been pretty good.