Issue in Utc dates in Sqlite

[mm-development]

In NH 5.3 there are changes in SQLiteDialect related with types. Because of this we have
encounter problems with Utc dates. Here is a test case (important: use Sqlite database to run this test):

        public void Test()
        {
            using (var session = _sessionFactory.OpenSession())
            {
                var entity = new Entity();
                var currentDate = DateTime.UtcNow;
                entity.Date = currentDate;
                session.Save(entity);
                session.Flush();
                session.Clear();
                entity = session.Get<Entity>(entity.Id);
                Assert.AreEqual(currentDate, entity.Date);
            }
                
        }
    }

    public class EntityMapping : ClassMapping<Entity>
    {
        public EntityMapping()
        {
            Id(x => x.Id, map => map.Generator(Generators.GuidComb));
            Property(x => x.Date, h =>
            {
                h.NotNullable(false);
                h.Type<UtcDateTimeType>();
            });
        }
    }

    public class Entity
    {
        public virtual Guid Id { get; set; }

        public virtual DateTime Date { get; set; }
    }

Test case is simple. We have an entity with a DateTime property. In mappings we specify Utc. In our scenario we set this property to the current Utc date and save the entity. Next we read it from the database. Date from database is different comparing to the original date (it looks like the value is local date and only a type is set to Utc).
In the previous version of NH, this scenario works good.

I've already made an investigation. The cause of this problem is change in SQLiteDialect class:
Old (good) code:

RegisterColumnType(DbType.Date, "DATE");
RegisterColumnType(DbType.DateTime, "DATETIME");
RegisterColumnType(DbType.Time, "TIME");

New (cause of the problem):

RegisterColumnType(DbType.Date, "TEXT");
RegisterColumnType(DbType.DateTime, "TEXT");
RegisterColumnType(DbType.Time, "TEXT");

[fredericDelaporte]

The old code is no good and was quite wrong instead. DATE, DATETIME and TIME are nonexistent types in SQLite, causing it to do some loose inference for deducting a type it supports, leading to many bugs. This has been fixed in #2346, and a detailed explanation of the trouble can be found here.

But anyway, I highly suspect your trouble has nothing to do with #2346, but instead is #1362, which is an external issue due to short-comings of SQLite with datetime and timezone handling. You should fix your configuration to account for them, maybe like it was done for our own tests in #1378.

[fredericDelaporte]

Closing as an external issue, caused by System.Data.SQLite implementation, with an issue raised on its side but closed as "Won't fix".