How to instruct linq to sql to update table columns to their default values on UpdateOnSubmit/SubmitChanges?

Question

I have a Linq to SQL entity which emit the following two sql statements:

UPDATE [identity].[AddressTypes]
SET [Name] = @p4
WHERE ([SurrogateKey] = @p0) AND ([Name] = @p1) AND ([LastUpdatedOn] = @p2) AND ([LastUpdatedBy] = @p3)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [b0cf44d9-c6ba-de11-b194-001e37f334ea]
-- @p1: Input VarChar (Size = 11; Prec = 0; Scale = 0) [Residencial]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [16/10/2009 23:43:28]
-- @p3: Input NVarChar (Size = 23; Prec = 0; Scale = 0) [LUCIANO-NOTEBOO\Luciano]
-- @p4: Input VarChar (Size = 4; Prec = 0; Scale = 0) [Home]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

UPDATE [identity].[AddressTypes]
SET [Name] = @p4
WHERE ([SurrogateKey] = @p0) AND ([Name] = @p1) AND ([LastUpdatedOn] = @p2) AND ([LastUpdatedBy] = @p3)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [b1cf44d9-c6ba-de11-b194-001e37f334ea]
-- @p1: Input VarChar (Size = 9; Prec = 0; Scale = 0) [Comercial]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [16/10/2009 23:43:29]
-- @p3: Input NVarChar (Size = 23; Prec = 0; Scale = 0) [LUCIANO-NOTEBOO\Luciano]
-- @p4: Input VarChar (Size = 8; Prec = 0; Scale = 0) [Business]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

But I need the Entity to emit the sql statements like the ones below, where LastUpdatedOn and LastUpdatedBy are set to their default values. I guess I'll have to implement Extensibility Method Definition for "partial void Update...", but don't know how to proceed. Anybody out there could please help me solve that?

UPDATE [identity].[AddressTypes]
SET [Name] = @p4, [LastUpdatedOn] = DEFAULT, [LastUpdatedBy] = DEFAULT
WHERE ([SurrogateKey] = @p0) AND ([Name] = @p1) AND ([LastUpdatedOn] = @p2) AND ([LastUpdatedBy] = @p3)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [b0cf44d9-c6ba-de11-b194-001e37f334ea]
-- @p1: Input VarChar (Size = 11; Prec = 0; Scale = 0) [Residencial]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [16/10/2009 23:43:28]
-- @p3: Input NVarChar (Size = 23; Prec = 0; Scale = 0) [LUCIANO-NOTEBOO\Luciano]
-- @p4: Input VarChar (Size = 4; Prec = 0; Scale = 0) [Home]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

UPDATE [identity].[AddressTypes]
SET [Name] = @p4, [LastUpdatedOn] = DEFAULT, [LastUpdatedBy] = DEFAULT
WHERE ([SurrogateKey] = @p0) AND ([Name] = @p1) AND ([LastUpdatedOn] = @p2) AND ([LastUpdatedBy] = @p3)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [b1cf44d9-c6ba-de11-b194-001e37f334ea]
-- @p1: Input VarChar (Size = 9; Prec = 0; Scale = 0) [Comercial]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [16/10/2009 23:43:29]
-- @p3: Input NVarChar (Size = 23; Prec = 0; Scale = 0) [LUCIANO-NOTEBOO\Luciano]
-- @p4: Input VarChar (Size = 8; Prec = 0; Scale = 0) [Business]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

Regards,

Answer 1

DEFAULT values in SQL Server are only ever applied when you INSERT a new record. They never get applied when you UPDATE an existing record.

If you want your LastUpdateOn and LastUpdateBy columns to be updated for each update as well, you'll probably have to look into using triggers to handle that. Or expose them as properties on your entity/entities so you can set them programmatically before the update statement gets executed.

Linq-to-SQL gives you a number of extension points to hook into - e.g. the OnValidate method on your entity class:

partial void OnValidate(System.Data.Linq.ChangeAction action)
{
    if (action == ChangeAction.Update)
    {
        // set your LastUpdatedBy and LastUpdatedOn values here
    }
}

Or you could implement the partial method OnUpdate(Entity) in your class and set the fields as needed:

partial void UpdateEmployee(Employee instance) 
{
    // set your LastUpdatedBy and LastUpdatedOn values here
    ExecuteDynamicUpdate(instance);
}

Another option using a utility class for auditing is explained here.

Marc

Answer 2

I better handled my problem at database level, using triggers, since I needed these fields to be automatically updated and using server side information (date/time).

CREATE TRIGGER [identity].[AddressTypesInsteadOfUpdate]
   ON  [identity].[AddressTypes]
   INSTEAD OF UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE [identity].AddressTypes
    SET Name = inserted.Name,
        LastUpdatedOn = DEFAULT,
        LastUpdatedBy = DEFAULT
    FROM [identity].AddressTypes
      INNER JOIN
         inserted
         ON [identity].AddressTypes.SurrogateKey = inserted.SurrogateKey
END