Fluent NHibernate - How to do Additional Processing when Converting from SQL to C# type?

Question

I'm using Fluent NHibernate as my Data Access layer, and I need to do this every time a value in SQL is mapped to the DateTime type:

var newDateTime = DateTime.SpecifyKind(oldDateTime, DateTimeKind.Local);

In the code above, newDateTime represents the value that should be returned for all SQL to C# conversions, and oldDateTime represents what the default converters for NHibernate automatically convert to.

Besides the issue that Fluent NHibernate documentation is very bleak, I've tried searching around the internet for conventions that will let me do this, but IUserType is too heavy (and I haven't been able to find a comprehensive explanation of how implement the methods derived from IUserType), and IPropertyConvention seems to only offer ways to modify how C# is being converted to SQL (not the other way around, which is what I need in this scenario).

Can someone please point me in the right direction? And/or provide some quality links to read up on conventions? None of the wiki pages explain anything in detail so please refrain from linking those. Thank you.

Radim Köhler · Accepted Answer · 2013-09-04 03:09:39Z

4

NHibernate (not only Fluent) supports setting, distinguishing how to handle the DateTime stored in DB (regardless of some DB Support for offset e.g. datetimeoffset (Transact-SQL)). See 5.2.2. Basic value types

GET from DB:

So, we can explicitly define, how to treat the value returned from a table column like this:

Map(x => x.ExpiryDate).CustomType<UtcDateTimeType>(); // UTC
Map(x => x.MaturityDate).CustomType<LocalDateTimeType>(); // local

So, once retrieved from DB, all DateTime properties will be automatically provided with correct Kind setting:

Assert.IsTrue(entity.ExpiryDate.Kind == DateTimeKind.Utc);
Assert.IsTrue(entity.MaturityDate.Kind == DateTimeKind.Local);

SET

Let me provide some extract from Date/Time Support in NHibernate:

Notice that NHibernate did not perform any conversions or throw an exception when saving/loading a DateTime value with the wrong DateTimeKind. (It could be argued that NHibernate should throw an exception when asked to save a Local DateTime and the property is mapped as a UtcDateTime.) It is up to the developer to ensure that the proper kind of DateTime is in the appropriate field/property.

Other words, the Entity DateTime, coming from a client (during the binding, deserializing etc) must be correctly set in the custom == our code.

An example, in Web API, during the conversion of the DateTime from a JSON. While the JSON is UTC, the DB is set to lcoal. We can inject this converter and be sure, that:

class DateTimeConverter : IsoDateTimeConverter
{
    public DateTimeConverter()
    {
        DateTimeStyles = DateTimeStyles.AdjustToUniversal;
    }

    public override object ReadJson(JsonReader reader, Type objectType
           , object existingValue, JsonSerializer serializer)
    {
        var result = base.ReadJson(reader, objectType, existingValue, serializer);
        var dateTime = result as DateTime?;
        if (dateTime.Is() && dateTime.Value.Kind == DateTimeKind.Utc)
        {
            return dateTime.Value.ToLocalTime();
        }
        return result;
    }

And now we can be sure that:

GET - our mapping .CustomType<LocalDateTimeType>() will correctly inform the application that data coming from DB are in Local zone
SET - the converter, will correctly set the DateTime values to Local zone

answered Sep 4, 2013 at 3:09

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anshul Over a year ago

Thanks for your reply. I ended up extending IUserType and creating a UserTypeConvention out of the new type. I will post my code in a reply.

Radim Köhler Over a year ago

If that helped anyhow, it is great. Enjoy NHibernate ;)

Anshul Over a year ago

yes, your code definitely helped. It led me to the right solution. Thanks!

Anshul · Accepted Answer · 2013-09-06 13:16:55Z

I ended up implementing a new user type using IUserType. In fact, I found a novel way of minimizing the work needed to implement new user type for immutable types, since only a few of the members need to really be implemented when extending an immutable type such as DateTime. Here's my code:

[Serializable]
public class DateTimeKindLocalType : BaseImmutableUserType<DateTime>
{
    public override object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        //This is the line that I needed
        return DateTime.SpecifyKind((DateTime)NHibernateUtil.DateTime2.NullSafeGet(rs, names), DateTimeKind.Local);
    }

    public override void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        NHibernateUtil.DateTime2.NullSafeSet(cmd, value, index);
    }

    public override SqlType[] SqlTypes
    {
        get { return new[] {NHibernateUtil.DateTime2.SqlType}; }
    }
}

[Serializable]
public class DateTimeKindLocalTypeConvention
    : UserTypeConvention<DateTimeKindLocalType>
{
}

[Serializable]
public class DateTimeKindLocalNullableType : BaseImmutableUserType<DateTime?>
{
    public override object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        if (owner == null)
            return null;
        return DateTime.SpecifyKind((DateTime)NHibernateUtil.DateTime2.NullSafeGet(rs, names), DateTimeKind.Local);
    }

    public override void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        NHibernateUtil.DateTime2.NullSafeSet(cmd, value, index);
    }

    public override SqlType[] SqlTypes
    {
        get { return new[] { NHibernateUtil.DateTime2.SqlType }; }
    }
}

[Serializable]
public class DateTimeKindLocalNullableTypeConvention
    : UserTypeConvention<DateTimeKindLocalNullableType>
{
}


[Serializable]
public abstract class BaseImmutableUserType<T> : IUserType
{
    public abstract object NullSafeGet(IDataReader rs, string[] names, object owner);
    public abstract void NullSafeSet(IDbCommand cmd, object value, int index);
    public abstract SqlType[] SqlTypes { get; }

    public new bool Equals(object x, object y)
    {
        if (ReferenceEquals(x, y))
        {
            return true;
        }

        if (x == null || y == null)
        {
            return false;
        }

        return x.Equals(y);
    }

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

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

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

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

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

    public Type ReturnedType
    {
        get { return typeof(T); }
    }

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

Basically, I have created (copied from somewhere else, sorry forgot source) a base class called BaseImmutableUserType<T> that essentially creates an IUserType based on an immutable type, but allows subclasses to extend the functionality of the NullSafeGet() and NullSafeSet() operations (which are essentially the Get and Set operations and alongwith SqlTypes, that's all I need to override in the subclass). I imagine I'll need to override different types more in the long run, so I decided to use a generalized solution for immutable types. Another point to note is that I had to do this both both DateTime and DateTime?. This is my Fluent configuration:

_fnhConfig = Fluently.Configure().Database(
                    MsSqlConfiguration.MsSql2008.ConnectionString(ConnectionString)                         
                    ).Mappings(m => m.FluentMappings.AddFromAssemblyOf<DataAccess.NHMG.Fluent.Mapping.DBBufferMap>()
                                        .Conventions.Add(
                                            DefaultLazy.Never()
                                            ,DefaultCascade.None()
                                    ===>    ,new DateTimeKindLocalTypeConvention()
                                    ===>    ,new DateTimeKindLocalNullableTypeConvention()
                                        ));

Please let me know if there are questions and I'll try to answer as quick as possible.

Yes, thank you, I had forgotten where I got it from. Thanks for the clarification.

Collectives™ on Stack Overflow

Fluent NHibernate - How to do Additional Processing when Converting from SQL to C# type?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related