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)

Advertisements