Updating a Single Column in Linq to SQL using a View

Another 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” columns:

CREATE VIEW [dbo].[V_FooMax] AS
SELECT  OID, ID
FROM    dbo.FooMax

Since views based on single tables are updatable, an update on the view is performed as an update on the table:

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

When mapping views in a .dbml file, you must take care of:

• definition of a primary key column

• if you do not insert into the view, the primary key’s Auto-Sync property can be set to “Never”.

The SQL statements generated by this code are:

SELECT TOP (1) [t0].[OID], [t0].[ID]
FROM [dbo].[V_FooMax] AS [t0]
WHERE [t0].[OID] = @p0

UPDATE [dbo].[V_FooMax]
SET [ID] = @p2
WHERE ([OID] = @p0) AND ([ID] = @p1)

Updating a Single Column in Linq to SQL using a Stored Procedure

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

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.

SELECT from temp table in Stored Procedure with LINQ to SQL

I started with LINQ and MVC recently, and found the following restriction in LINQ to SQL:

Thus the LINQ to SQL code generator does not support the following:

Stored procedures that produce results based on temporary table.

While this statement is true for generated code, you can still manually work around the problem.

When you create a LINQ to SQL item, VS creates a .dbml and a .designer.cs file for the database connection specified. The .cs file contains the DataContext, which is declared as a public partial class, which means we can easily extend it.

Suppose we have a database called MyDB and a stored procedure MyProc which collects data in a temporary table and returns this data using a SELECT * FROM #Temp.

To declare the #Temp record in C#, we need to create a separate .cs file (e.g. MyDBExt.cs).

Start with a C# class mapping to the #Temp table (taken from one of my projects):

[Table]
public partial class MyProc_Result
{
    public MyProc_Result()    {   }

    [Column]
    public int User_OID;
    [Column]
    public string User_ID;
    [Column]
    public int ItemCount;
    [Column]
    public string Description;
}

What’s important here is that the class has the [Table] attribute, and each field has a [Column] attribute from the System.Data.Linq.Mapping namespace. No getters and setters necessary, as in the generated class file.

Next, extend the partial class from the original .designer.cs file and create an additional method for your SP:

[Function(Name = "dbo.MyProc")]
public IEnumerable<MyProc_Result> MyProcResultset( 
    .. copy parameters from original declaration .. )
{
    IExecuteResult result = this.ExecuteMethodCall(this, 
        ((MethodInfo)(MethodInfo.GetCurrentMethod())), ..params..);
    return result.ReturnValue as IEnumerable<MyProc_Result>;
}

Since overloaded methods are distinguished by the types of their parameters, and not by their return type, you have to change the method name for your extension. (Optionally, you can derive your own class and override the original method)