Updating a Single Column in Linq to SQL

The standard way to update a record using Linq to SQL is to load a record from the database, assign the new column values, and submit the changes:

using (var database = new DataContext())
{
  var foo = database.Foos.Where(f => f.OID == OID).FirstOrDefault();
  foo.ID = newID;
  database.SubmitChanges();
}

In case you are accessing tables containing n/varchar(max) or varbinary(max) columns (previously n/text and image), such as this:

CREATE TABLE [dbo].[FooMax](
  [OID] [int] IDENTITY(1,1) NOT NULL,
  [ID] [nvarchar](50) NOT NULL,
  [SomeText] [nvarchar](max) NULL,
  [SomeBinary] [varbinary](max) NULL,
  CONSTRAINT [PK_FooMax] PRIMARY KEY CLUSTERED ([OID] ASC)
)

above code means that the whole record is loaded into memory just to update a single column. Bear in mind that n/varchar(max) and varbinary(max) column can hold data up to 2GB each!

This post starts a series of posts presenting solutions to this problem.

4 Responses to Updating a Single Column in Linq to SQL

  1. […] a Single Column in Linq to SQL using a Stored Procedure One way to efficiently update specific columns of a database record is to write a Stored Procedure which is called with the primary key and the updated column […]

  2. […] way to update specific columns of a database record in Linq to SQL is to create a View on the table containing large columns, and only include the “short” […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: