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
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;

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)

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.