Unit testing NHibernate w/ SQLite and DateTimeOffset mappings

[Vikram Nayak]

The issue is fully described here
http://stackoverflow.com/questions/2304849/unit-testing-nhibernate-w-sqlite-and-datetimeoffset-mappings

We are using SQLite in our unit tests, and SQLite doesnot support
DateTimeOffset. As suggested in the blog post I created a custom User
Type that implements the IUserType interface, to use in for the Nh
Mapping in the Inmemory database.

code for the custom user types:
public class NormalizedDateTimeUserType : IUserType
{
private readonly TimeZoneInfo databaseTimeZone = TimeZoneInfo.Local;


public virtual Type ReturnedType
{
get { return typeof(DateTimeOffset); }
}

public virtual bool IsMutable
{
get { return false; }
}

public virtual object Disassemble(object value)
{
return value;
}

public virtual SqlType[] SqlTypes
{
get { return new[] { new SqlType(DbType.DateTime) }; }
}

public virtual bool Equals(object x, object y)
{
return object.Equals(x, y);
}

public virtual int GetHashCode(object x)
{
return x.GetHashCode();
}

public virtual object NullSafeGet(IDataReader dr, string[] names,
object owner)
{
object r = dr[names[0]];
if (r == DBNull.Value)
{
return null;
}

DateTime storedTime = (DateTime)r;
return new DateTimeOffset(storedTime,
this.databaseTimeZone.BaseUtcOffset);
}

public virtual void NullSafeSet(IDbCommand cmd, object value, int
index)
{
if (value == null)
{
NHibernateUtil.DateTime.NullSafeSet(cmd, null, index);
}
else
{
IDataParameter parameter = (IDataParameter)cmd.Parameters[index];
try
{
DateTimeOffset dateTimeOffset = (DateTimeOffset)value;
DateTime paramVal =
dateTimeOffset.ToOffset(this.databaseTimeZone.BaseUtcOffset).DateTime;

parameter.Value = paramVal;
}
catch (Exception e)
{
parameter.Value = DateTime.MinValue;
}
}
}

public virtual object DeepCopy(object value)
{
return value;
}

public virtual object Replace(object original, object target, object
owner)
{
return original;
}

public virtual object Assemble(object cached, object owner)
{
return cached;
}
}

public class NormalizedNullabeDateTimeUserType :
NormalizedDateTimeUserType
{
public override Type ReturnedType
{
get { return typeof(DateTimeOffset?); }
}
}

public class NormalizedDateTimeUserTypeConvention :
UserTypeConvention<NormalizedDateTimeUserType>
{

}

public class NormalizedNullableDateTimeUserTypeConvention :
UserTypeConvention<NormalizedNullabeDateTimeUserType>
{

}

Mapping code:
Fluently.Configure()
.Database(_configuration.DbConfiguration)
.Mappings(m =>
{
m.FluentMappings
.Add<DemographicsVerificationMap>()
m.AutoMappings
.Add(new AutoPersistenceModel().Instance);
m.FluentMappings.Conventions.Add(new
NormalizedDateTimeUserTypeConvention(), new
NormalizedNullableDateTimeUserTypeConvention());
})
.ExposeConfiguration(
x => x.SetProperty("current_session_context_class",
_configuration.SessionLifecyletype.ToNhibernateString()))
.BuildConfiguration();

Even with this, I still get this exception when I rebuild the schema
using:
schemaExport.Execute(false, true, false, connection, null);

Exception:
System.ArgumentException : Dialect does not support
DbType.DateTimeOffset
Parameter name: typecode
at NHibernate.Dialect.Dialect.GetTypeName(SqlType sqlType)
at NHibernate.Mapping.Table.SqlCreateString(Dialect dialect, IMapping
p, String defaultCatalog, String defaultSchema)
at NHibernate.Cfg.Configuration.GenerateSchemaCreationScript(Dialect
dialect)
at NHibernate.Tool.hbm2ddl.SchemaExport.Initialize()
at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action`1 scriptAction,
Boolean export, Boolean justDrop, IDbConnection connection, TextWriter
exportOutput)
at Mede.Pai.Dal.NhibernateSessionFactoryManager.BuildSchema() in
NhibernateSessionFactoryManager.cs: line 39
at Mede.Pai.IntegrationTest.InMemoryDbTestBase.SetUpBase() in
InMemoryDbTestBase.cs: line 35

My guess is the Conventions are getting registered in the
configuration.

When I explicitly register the custom type using for individual
DateTimeOffset properties(below) it works as expected, so it doesn't
seem to be a problem with the CustomType but a problem with how the
conventions are registered.
m.Map(x => x.DateAdded).CustomType(typeof
(NormalizedDateTimeUserType));

[Vikram Nayak]

It works as described. The problem was that I have both Fluent and
AutoMapping in my configuration, and I had registered the conventions
only with AutoMap.

here is the working configuration:
public Configuration GetConfiguration()
{
return Fluently.Configure()
.Database(_configuration.DbConfiguration)
.Mappings(m =>
{
AddConventions(m.FluentMappings.Conventions);
m.FluentMappings
.Add<DemographicsVerificationMap>()

var autoPersistenceModel = new
AutoPersistenceModel().Instance;
AddConventions(autoPersistenceModel.Conventions);
m.AutoMappings.Add(autoPersistenceModel);

})

.ExposeConfiguration(
x => x.SetProperty("current_session_context_class",
_configuration.SessionLifecyletype.ToNhibernateString()))
.BuildConfiguration();
}

public void AddConventions<T>(SetupConventionFinder<T> conventions)
{
if (_configuration.DbDialect == DatabaseDialect.SQLite)
{
conventions.Add(new SqliteDateTimeOffsetUserTypeConvention(), new
SqliteNullableDateTimeOffsetUserTypeConvention());
}
}

-Vikram

On Mar 24, 11:41 am, Vikram Nayak <vikram.na...@gmail.com> wrote:
> The issue is fully described herehttp://stackoverflow.com/questions/2304849/unit-testing-nhibernate-w-...