Perform conditional bulk update in Linq to SQL

Question

I have an SQL table that stores photos with a small SortOrder field. Users can insert new photos by specifying the decimal sort order to place a new record between two existing photos (or before the first photo). SortOrder will be saved as small, so when I find that the insert moves existing records, I need to update all the affected photos to increase 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 need to pull out all the records to the client, update them, and then send them, then I will just stick to the stored procedure, which is already working and very quickly.

Here's the T-SQL that pushes records:

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

Is there a way to do this bulk update in Linq to SQL without having to record?

Answer 1

LINQ to SQL does not execute CUD statements for sets, so stick with your existing implementation, as that would be best in your scenario.

Answer 2

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

I only use it in development for a couple of months, but so far it has been very good.

Answer 3

, , .

sproc-, ? , , ( ) . , , , .

Answer 4

One option would be to build the sql string that was in the stored procedure and execute it using the DataContext.ExecuteQuery method. Performing this method will prevent the retrieval of records.