Entity Framework and simple triggers

It is often required to increment a property on an Entity to signify that modifications have occurred on the Entity and persist this “version” to the data store. Performing this property increment in the database avoids issues with multiple processes performing read before writes to the same Entity. It is also normally necessary to round trip the last value of this property back to the Entity that has been modified.
Entity framework 4 supports this behaviour but not by default. The following is a simple example of the steps required to make this behaviour function using Entity framework 4.
Define a table with a version column, see the following simple example

CREATE TABLE [dbo].[TableWithVersion](
    [Identifier] [uniqueidentifier] NOT NULL,
    [Type] [int] NOT NULL,
    [Version] [int] NULL,
CONSTRAINT [PK_TableWithVersion] PRIMARY KEY CLUSTERED 
(
    [Identifier] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Define a trigger that will increment the Version value on insert or update. For this example use case the trigger fires on insert the previous version of the row remains in the database.

CREATE TRIGGER dbo.IncrementVersion 
   ON  dbo.TableWithVersion 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @value int
    SELECT @value = MAX([Version]) FROM [TableWithVersion] WHERE TableWithVersion.Type = [Type]
    
    UPDATE [TableWithVersion] SET [Version] = ISNULL(@value,0) + 1 from inserted WHERE [TableWithVersion].Type = inserted.Type AND [TableWithVersion].Identifier = inserted.Identifier
END
GO

The table is added to an Entity Framework Data Model using the standard mechanism. By default when an insert is performed Entity Framework executes the following SQL.

exec sp_executesql N'insert [dbo].[TableWithVersion]([Identifier], [Type], [Version])
values (@0, @1, null)
',N'@0 uniqueidentifier,@1 int',@0='717EE861-F416-46FA-87F0-3788AFB3F844',@1=0

To support the round tripping of the version value you must set StoreGeneratedPattern the on the property to Computed.



On updating an Entity, Entity Framework executes the following SQL to retrieve the Version number for the inserted row

exec sp_executesql N'insert [dbo].[TableWithVersion]([Identifier], [Type])
values (@0, @1)
select [Version]
from [dbo].[TableWithVersion]
where @@ROWCOUNT > 0 and [Identifier] = @0',N'@0 uniqueidentifier,@1 int',@0='840E3F4D-7E46-4848-9FED-2DCDC1206117',@1=0

Code is available here: GitHub EF4WithTrigger