How can I make SQLite return a proper DateTime when doing ISQLQuery.UniqueResult<DateTime>()?

Question

TLDR:

The following code is run in the different databases, Oracle: select sysdate from dual SQLite select datetime('now')

When doing Session.CreateSQLQuery(cmd).UniqueResult<DateTime>() the result is a DateTime when working against Oracle but a string when working against SQLite.

It feels like a bug in the SQLite driver and a hack to check the returned type and do a DateTime.Parse() if it is a string. I could do that but are there any ways to have NHibernate return the correct type?

I am trying to fetch current database time from the database. It works fine when using Oracle but when I try to do it against SQLite (in my unit tests) it breaks as the date returned is not a DateTime but a string.

I've seen solutions using custom IUserType but I cannot see how I should use that in this case. Any suggestions?

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Dialect.Function;

namespace My.Common.Types {

    public class MyNHibernateDialectException : Exception {
        public MyNHibernateDialectException(string message) : base(message) { }
    } 

    /// <summary>
    /// Define all custom functions here by name. It is important that when adding a new custom sql function, that function will work
    /// in all dialects supported.
    /// </summary>
    public static class MyDatabaseDialects {

        public enum Query {
            SysDate
        }

        /// <summary>
        ///  Dialect implementations will use this function to verify that they all implement the same functions.
        /// </summary>
        /// <param name="dialect"></param>
        public static void VerifyRegistrations(this NHibernate.Dialect.Dialect dialect) {
            // Verify that the required function are there
            foreach (var func in Enum.GetValues(typeof(Function))) {
                var enumName = func.ToString();
                if (!dialect.Functions.ContainsKey(enumName)) {
                    throw new MyNHibernateDialectException(
                        string.Format("The custom function '{0}' is not defined. Did you forget it in factory '{1}'?", enumName, dialect));
                }
            }
        }

    }

    /// <summary>
    /// An interface to reveal more advanced functionality that is database specific
    /// </summary>
    public interface IDialectExtensions {

        /// <summary>
        /// Fetch a query specfic for the current database.
        /// </summary>
        ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query);

        /// <summary>
        /// Fetch a parameterized query specfic for the current database.
        /// </summary>
        ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams);
    }

    /// <summary>
    /// Class to store database specific objects except functions (which are supported by NHibernate).
    /// </summary>
    class DialectExtension {

        private readonly Dictionary<MyDatabaseDialects.Query, string> queryDictionary = new Dictionary<MyDatabaseDialects.Query, string>();

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return this.GetQuery(session, query, null);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            var cmd = (queryParams == null) ? queryDictionary[query] : string.Format(queryDictionary[query], queryParams);
            return session.Session.CreateSQLQuery(cmd);
        }

        public void RegisterQuery(MyDatabaseDialects.Query query, string hqlString) {
            queryDictionary.Add(query, hqlString);
        }

        public void VerifyQueryRegistrations() {

            foreach (var query in Enum.GetValues(typeof(MyDatabaseDialects.Query))) {
                if (!queryDictionary.ContainsKey((MyDatabaseDialects.Query)query)) {
                    throw new MyNHibernateDialectException(string.Format("The custom query '{0}' is not defined.", query.ToString()));
                }
            }
        }
    }

    public class MyOracle10gDialect : NHibernate.Dialect.Oracle10gDialect, IDialectExtensions {

        private readonly DialectExtension dialectExtension = new DialectExtension();

        public MyOracle10gDialect() {

            #region Dialect extensions

            dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select sysdate from dual");

            dialectExtension.VerifyQueryRegistrations();

            #endregion Dialect extensions

        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return dialectExtension.GetQuery(session, query);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            return dialectExtension.GetQuery(session, query, queryParams);
        }
    }

    public class MySqliteDialect : NHibernate.Dialect.SQLiteDialect, IDialectExtensions {

        private readonly DialectExtension dialectExtension = new DialectExtension();

        public MySqliteDialect() {

            #region Dialect extensions

            dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select datetime('now')");

            dialectExtension.VerifyQueryRegistrations();

            #endregion Dialect extensions
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return dialectExtension.GetQuery(session, query);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            return dialectExtension.GetQuery(session, query, queryParams);
        }

    }

}

And I use the code above like this:

/// <summary>
/// Fetches a DialectExtensions object allowing us to have more advanced functionality that is database specific
/// </summary>
public static IDialectExtensions GetDialectExtensions(this IOperationContext operationContext) {
    return Session.GetSessionImplementation().Factory.Dialect as IDialectExtensions;
}

/// <summary>
/// Get the database time by executing a raw SQL statement.
/// </summary>
public static DateTime? GetDatabaseTime() {
    DateTime? result = null;
    try {
        result = GetDialectExtensions()
            .GetQuery(Session, MyDatabaseDialects.Query.SysDate)
            .UniqueResult<DateTime>();
    } catch {
        // SQLite will throw exception here as the result is returned as a string instead of a DateTime
    }
    return result;
}

Answer 1

When doing Session.CreateSQLQuery(cmd).UniqueResult() the result is a DateTime when working against Oracle but a string when working against SQLite.

Sounds like you might need to further modify the hibernate dialect for SQLite to return the same results. Make sure you have the default column map types set for SQLite, to match what's returned Oracle. I map the timestamp columns to datetime. Here's my SQLiteDialect, keep in mind this is a Java version, but should still be similar:

public class SQLiteDialect extends Dialect {

    public SQLiteDialect() {
        super();

        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.REAL, "real");
        registerColumnType(Types.DOUBLE, "double");
        registerColumnType(Types.NUMERIC, "numeric");
        registerColumnType(Types.DECIMAL, "decimal");
        registerColumnType(Types.CHAR, "char");
        registerColumnType(Types.VARCHAR, "varchar");
        registerColumnType(Types.LONGVARCHAR, "longvarchar");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "longvarchar");
        registerColumnType(Types.TIMESTAMP, "datetime");
        registerColumnType(Types.BINARY, "blob");
        registerColumnType(Types.VARBINARY, "blob");
        registerColumnType(Types.LONGVARBINARY, "blob");
        // registerColumnType(Types.NULL, "null");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");
        registerColumnType(Types.NULL, "null");
        registerHibernateType(Types.NULL, "null");

        registerFunction("concat", new VarArgsSQLFunction(IntegerType.INSTANCE,
            "", "||", ""));
        registerFunction("mod", new SQLFunctionTemplate(IntegerType.INSTANCE,
            "?1 % ?2"));
        registerFunction("substr", new StandardSQLFunction("substr",
            IntegerType.INSTANCE));
        registerFunction("substring", new StandardSQLFunction("substr",
            IntegerType.INSTANCE));

        registerFunction("replace", new StandardSQLFunction("replace",
            IntegerType.INSTANCE));

        registerFunction("current_time", new NoArgSQLFunction(
            "datetime('now')",
            CalendarType.INSTANCE, false));
        registerFunction("current_timestamp", new NoArgSQLFunction(
            "strftime('%s','now')*1000", CalendarType.INSTANCE, false));
        registerFunction("current_date", new NoArgSQLFunction(
            "strftime('%s','now')*1000",
            CalendarType.INSTANCE, false));

        registerFunction(
            "trunc",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%d-%m-%Y\", ?1/1000, 'unixepoch', 'localtime') as string)"));
        registerFunction(
            "second",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%S\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "minute",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%M\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "hour",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%H\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "day",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%d\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "month",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%m\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "year",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%Y\", ?1/1000, 'unixepoch', 'localtime') as integer)"));

        registerFunction("trim", new AbstractAnsiTrimEmulationFunction() {

            protected SQLFunction resolveBothSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?1)");
            }

            protected SQLFunction resolveBothSpaceTrimFromFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?2)");
            }

            protected SQLFunction resolveLeadingSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "ltrim(?1)");
            }

            protected SQLFunction resolveTrailingSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "rtrim(?1)");
            }

            protected SQLFunction resolveBothTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?1, ?2)");
            }

            protected SQLFunction resolveLeadingTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "ltrim(?1, ?2)");
            }

            protected SQLFunction resolveTrailingTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "rtrim(?1, ?2)");
            }
        });
    }

    @Override
    public boolean supportsIdentityColumns() {
        return true;
    }

    /*
     * public boolean supportsInsertSelectIdentity() { return true; // As
     * specify in NHibernate dialect }
     */

    @Override
    public boolean hasDataTypeInIdentityColumn() {
        return false; // As specify in NHibernate dialect
    }

    /*
     * public String appendIdentitySelectToInsert(String insertString) { return
     * new StringBuffer(insertString.length()+30). // As specify in NHibernate
     * dialect append(insertString).
     * append("; ").append(getIdentitySelectString()). toString(); }
     */

    @Override
    public String getIdentityColumnString() {
        // return "integer primary key autoincrement";
        return "integer";
    }

    @Override
    public String getIdentitySelectString() {
        return "select last_insert_rowid()";
    }

    @Override
    public boolean supportsLimit() {
        return true;
    }

    // Added fix from bug,
    // http://code.google.com/p/hibernate-sqlite/issues/detail?id=1
    @Override
    public boolean bindLimitParametersInReverseOrder() {
        return true;
    }

    @Override
    public String getLimitString(String query, boolean hasOffset) {
        return new StringBuffer(query.length() + 20).append(query)
            .append(hasOffset ? " limit ? offset ?" : " limit ?").toString();
    }

    @Override
    public String getLimitString(final String s, final int min, final int max) {
        String limitString = super.getLimitString(s, min, max);

        return limitString;
    }

    @Override
    public boolean supportsTemporaryTables() {
        return true;
    }

    @Override
    public String getCreateTemporaryTableString() {
        return "create temporary table if not exists";
    }

    @Override
    public boolean dropTemporaryTableAfterUse() {
        return false;
    }

    @Override
    public boolean supportsCurrentTimestampSelection() {
        return true;
    }

    @Override
    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    @Override
    public String getCurrentTimestampSelectString() {
        return "select current_timestamp";
    }

    @Override
    public boolean supportsUnionAll() {
        return true;
    }

    @Override
    public boolean hasAlterTable() {
        return false; // As specify in NHibernate dialect
    }

    @Override
    public boolean dropConstraints() {
        return false;
    }

    @Override
    public String getAddColumnString() {
        return "add column";
    }

    @Override
    public String getForUpdateString() {
        return "";
    }

    @Override
    public boolean supportsOuterJoinForUpdate() {
        return false;
    }

    @Override
    public String getDropForeignKeyString() {
        throw new UnsupportedOperationException(
            "No drop foreign key syntax supported by SQLiteDialect");
    }

    @Override
    public String
                    getAddForeignKeyConstraintString(String constraintName,
                                                     String[] foreignKey,
                                                     String referencedTable,
                                                     String[] primaryKey,
                                                     boolean referencesPrimaryKey) {
        throw new UnsupportedOperationException(
            "No add foreign key syntax supported by SQLiteDialect");
    }

    @Override
    public String getAddPrimaryKeyConstraintString(String constraintName) {
        throw new UnsupportedOperationException(
            "No add primary key syntax supported by SQLiteDialect");
    }

    @Override
    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    @Override
    public boolean supportsIfExistsAfterTableName() {
        return false;
    }

    @Override
    public boolean supportsCascadeDelete() {
        return false;
    }
}

Make sure you set the 'hibernate.dialect' property before you build the session factory:

private SessionFactory buildSessionFactory() throws Exception {
    StandardServiceRegistryBuilder serviceRegistryBuilder =
        new StandardServiceRegistryBuilder();

    org.hibernate.cfg.Configuration config = new Configuration();
    config.setProperty("hibernate.dialect", dialect);
    //Any additional config options, or just set this in your hibernate xml config...


    serviceRegistryBuilder.applySettings(config.getProperties());

    ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();

    sessionFactory = config.buildSessionFactory(serviceRegistry);

    return sessionFactory;
}

Here's a smaller version I found for .NET

This should resolve your issue. If it doesn't, there are other work arounds you can use by setting temporal types for your ORM mapping settings:

[Temporal(TemporalType.TIMESTAMP)]

Apparently, TemporalTypes are referred to as CustomTypes in csharp. There's an example provided in one of the answers for this question.

Also, you may have to account for timezones.