One way to efficiently update specific columns of a database record in Linq to SQL is to write a Stored Procedure which is called with the primary key and the updated column values:
CREATE PROCEDURE [dbo].[UpdateFooMaxID] ( @OID INT, @ID NVARCHAR(50) ) AS BEGIN SET NOCOUNT ON; UPDATE FooMax SET ID = @ID WHERE OID = @OID END
The stored procedure is then mapped in the .dbml file, and executed from the DataContext:
using (var database = new DataContext()) { database.UpdateFooMaxID(OID, newID); database.SubmitChanges(); }
The SQL statement generated by this piece of code is simply:
EXEC @RETURN_VALUE = [dbo].[UpdateFooMaxID] @OID = @p0, @ID = @p1
The advantages of this solution are
- it is easy to implement.
- if your organization requires every database access is performed via stored procedures, it is also the only way to go.
I find, though, that there are many disadvantages in this solution:
- a basic operation is coded in a different language (C#/VB.net vs. TSQL)
- each combination of updated columns require a separate stored procedure
- TSQL stored procedures are not compiled or syntax-checked
- later changes in the data model may break the stored procedure without notice