Sql-server – NHibernate and sql timestamp columns as version

concurrencyfluent-nhibernatenhibernatesql server

I've been racking my head trying to get Nhibernate to work with a byte
array as version mapping to an sql timestamp. I'd implemented an
IUserVersionType but Nhibernate was creating varbinary in the database
rather than timestamp. Inspired by a blog post by Ayende recently on
concurrency, I changed my mapping to specify the sql-type to timestamp
which worked perfectly. However I now face a rather curious problem
wherein Nhibernate does an insert, gets the new version and then
immediately tries to do an update and attempts to set the version
column, which being an sql timestamp fails.

This is my mapping:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" namespace="Core.Domain.Entities"
default-lazy="false">
 <class name="Contact" table="Contacts" xmlns="urn:nhibernate-
mapping-2.2" optimistic-lock="version" dynamic-insert="true" dynamic-
update="true">
   <id name="Id" type="Int32" column="Id">
     <generator class="identity" />
   </id>
   <version name="Version" type="BinaryBlob" generated="always"
unsaved-value="null">
     <column name="Version" sql-type="timestamp" not-null="false" />
   </version>
   <property name="Title" type="String">
     <column name="Title" length="5" />
   </property>
   <property name="FirstName" type="String">
     <column name="FirstName" not-null="true" length="50" />
   </property>
   <property name="MiddleName" type="String">
     <column name="MiddleName" length="50" />
   </property>
   <property name="LastName" type="String">
     <column name="LastName" not-null="true" length="50" />
   </property>
   <property name="Suffix" type="String">
     <column name="Suffix" length="5" />
   </property>
   <property name="Email" type="String">
     <column name="Email" length="50" />
   </property>
   <bag name="PhoneNumbers" inverse="true" cascade="all-delete-
orphan">
     <key foreign-key="FK_Contacts_PhoneNumbers_ContactId" on-
delete="cascade" column="ContactId" />
     <one-to-many class="Core.Domain.Entities.PhoneNumber,
Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" />
   </bag>
   <property name="DateCreated" type="DateTime">
     <column name="DateCreated" />
   </property>
   <property name="DateModified" type="DateTime">
     <column name="DateModified" />
   </property>
   <property name="LastModifiedBy" type="String">
     <column name="LastModifiedBy" />
   </property>
 </class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" namespace="Core.Domain.Entities"
default-lazy="false">
 <class name="Customer" table="Customers" xmlns="urn:nhibernate-
mapping-2.2" optimistic-lock="version" dynamic-insert="true" dynamic-
update="true">
   <id name="Id" type="Int32" column="Id">
     <generator class="identity" />
   </id>
   <version name="Version" type="BinaryBlob" generated="always"
unsaved-value="null">
     <column name="Version" sql-type="timestamp" not-null="false" />
   </version>
   <property name="AccountNumber" access="nosetter.pascalcase-
underscore" type="String">
     <column name="AccountNumber" unique="true" length="25" />
   </property>
<!-- other mappings... -->
   <property name="DateCreated" type="DateTime">
     <column name="DateCreated" />
   </property>
   <property name="DateModified" type="DateTime">
     <column name="DateModified" />
   </property>
   <property name="LastModifiedBy" type="String">
     <column name="LastModifiedBy" />
   </property>
   <joined-subclass name="Core.Domain.Entities.Individual,
Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" table="Individuals">
     <key column="CustomerId" />
     <many-to-one fetch="join" lazy="false" not-null="true"
cascade="all" unique="true" not-found="exception" name="Contact"
column="ContactID" />
     <bag name="Addresses" table="Addresses_Individuals">
       <key column="AddressId" foreign-
key="FK_Addresses_Individuals_Addresses_AddressId" />
       <many-to-many column="IndividualId"
class="Core.Domain.Entities.Address, Core.Domain,
Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" foreign-
key="FK_Addresses_Individuals_Individuals_IndividualId" />
     </bag>
   </joined-subclass>
   <joined-subclass name="Core.Domain.Entities.Store, Core.Domain,
Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" table="Stores">
     <key column="CustomerId" />
     <many-to-one unique="true" cascade="save-update" fetch="join"
not-null="true" not-found="exception" name="Address"
column="AddressId" />
     <many-to-one lazy="proxy" not-null="true" cascade="all" not-
found="exception" name="Client" column="ClientId" />
     <property name="StoreName" type="String">
       <column name="StoreName" not-null="true" length="50" />
     </property>
     <bag name="Contacts" table="Contacts_Stores">
       <key column="ContactId" foreign-
key="FK_Contacts_Stores_Contacts_ContactId" />
       <many-to-many column="StoreId"
class="Core.Domain.Entities.Contact, Core.Domain,
Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" foreign-
key="FK_Contacts_Stores_Stores_StoreId" />
     </bag>
   </joined-subclass>
 </class>
</hibernate-mapping>

Calling Session.Save on an Individual with associated Contact results
in the following error:

NHibernate: INSERT INTO Addresses (Line1, PostalCode, Country,
DateCreated, DateModified, LastModifiedBy) VALUES (@p0, @p1, @p2, @p3,
@p4, @p5); select SCOPE_IDENTITY(); @p0 = 'Order Address Line 1', @p1
= 'CV31 6BW', @p2 = 'United Kingdom', @p3 = '20/04/2009 19:45:32', @p4
= '20/04/2009 19:45:32', @p5 = ''
NHibernate: SELECT address_.Version as Version22_ FROM Addresses
address_ WHERE address_.Id=@p0; @p0 = '1'
NHibernate: INSERT INTO Contacts (FirstName, LastName, DateCreated,
DateModified, LastModifiedBy) VALUES (@p0, @p1, @p2, @p3, @p4); select
SCOPE_IDENTITY(); @p0 = 'Joe', @p1 = 'Bloggs', @p2 = '20/04/2009
19:45:34', @p3 = '20/04/2009 19:45:34', @p4 = ''
NHibernate: SELECT contact_.Version as Version33_ FROM Contacts
contact_ WHERE contact_.Id=@p0; @p0 = '1'
NHibernate: INSERT INTO Customers (AccountNumber, DateCreated,
DateModified, LastModifiedBy) VALUES (@p0, @p1, @p2, @p3); select
SCOPE_IDENTITY(); @p0 = '', @p1 = '20/04/2009 19:45:34', @p2 =
'20/04/2009 19:45:34', @p3 = ''
NHibernate: INSERT INTO Individuals (ContactID, CustomerId) VALUES
(@p0, @p1); @p0 = '1', @p1 = '1'
NHibernate: SELECT individual_1_.Version as Version2_ FROM Individuals
individual_ inner join Customers individual_1_ on
individual_.CustomerId=individual_1_.Id WHERE
individual_.CustomerId=@p0; @p0 = '1'
NHibernate: UPDATE Contacts SET Version = @p0 WHERE Id = @p1 AND
Version = @p2; @p0 = 'System.Byte[]', @p1 = '1', @p2 = 'System.Byte[]'

System.Data.SqlClient.SqlException: Cannot update a timestamp column.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning
(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds
(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean
returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery
(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
in c:\CSharp\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:
line 203
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object
id, Object[] fields, Object[] oldFields, Object rowId, Boolean[]
includeProperty, Int32 j, Object oldVersion, Object obj,
SqlCommandInfo sql, ISessionImplementor session) in c:\CSharp\NH
\nhibernate\src\NHibernate\Persister\Entity
\AbstractEntityPersister.cs: line 2713
NHibernate.Exceptions.GenericADOException: could not update:
[Core.Domain.Entities.Contact#1][SQL: UPDATE Contacts SET Version =
@p0 WHERE Id = @p1 AND Version = @p2]

Any ideas why NHibernate is attempting to update the version column
for Contact, even though it didn't for the Address?

Best Answer

I have found that using dynamic-insert="true" on the class along with causes this issue. I use the following mapping successfully:

...
    <class name="Contact" table="Contact">
        <id name="ID" column="ID" type="int">
            <generator class="identity" />
        </id>
        <version name="Version" generated="always" unsaved-value="null" type="BinaryBlob"/>
...

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic