Forum:  Bugs & Issues

Thread:  DateTimeOffset Mapping

sherifr (User)   Posted on: 07-Nov-2019 14:47:06.

I am trying to execute the following code


            RuntimeConfiguration.ConfigureDQE<PostgreSqlDQEConfiguration>(x =>

            var result = await con.FetchQueryAsync<Model>(query1);

    class Model
        public long Id { get; set; }
        public string Extra { get; set; }
        public DateTimeOffset DateCreated { get; set; }

While NpgSql supports returning
in case of
, I get the following exception

Unhandled exception. System.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.DateTimeOffset'.
at lambda_method(Closure , DataReaderProjectionRow )
at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.<>c__DisplayClass2_1`1.<FetchProjectionFromReader>b__1(IDataReader r)
at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader[T](List`1 destination, IDataReader dataSource, IRetrievalQuery queryExecuted, Boolean performImplicitTypeConversions)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjectionAsync[T](List`1 destination, IRetrievalQuery queryToExecute, CancellationToken cancellationToken, Boolean performImplicitTypeConversions)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchQueryAsync[T](CancellationToken cancellationToken, PlainSQLFetchAspects fetchAspects, String sqlQuery, Object parameterValues)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategyAsync[T](Func`1 toExecute, CancellationToken cancellationToken)
at TestNpgsqlDapperDateTimeOffset.Program.Main(String[] args) in C:\Users\sherifr\source\repos\TestNpgsqlDapperDateTimeOffset\Program.cs:line 70
at TestNpgsqlDapperDateTimeOffset.Program.<Main>(String[] args)

Nuget packages

    <PackageReference Include="SD.LLBLGen.Pro.DQE.PostgreSql" Version="5.4.4" />
    <PackageReference Include="SD.LLBLGen.Pro.ORMSupportClasses" Version="5.4.4" />

LLBLgen Designer version

5.5 (5.5.2) RTM
Walaa (Support Team)   Posted on: 07-Nov-2019 17:26:50.
This has been addressed before, here

sherifr (User)   Posted on: 11-Nov-2019 21:18:02.
The following is my opinion and my use case and I think it can apply to other users.

This renders LLBLGen useless in the following case.
Fetching the records from database (and one one of the columns is
) this is seems fine. But if you try to use the parsed datetime value in a query like the following
.Where(x => x.DateCreated >= minFetchedTimestamptz && x.DateCreated <= maxFetchedTimestamptz)

this won't fetch the right values unless the
is parsed and used in the query as
. Which renders LLBLGen useless in running SQL queries for reporting or more advanced use cases.

More advanced use cases, that is more advanced than fetching data using LINQ and CRUD operations.

If you don't agree with me, I don't mind to close the case.
Otis (LLBLGen Pro Team)   Posted on: 12-Nov-2019 10:49:26.
I'm not sure I follow what you're trying to say. Timestamptz fields are mapped as DateTime fields, as the value returned from the DB is a DateTime value, not a DatetimeOffset value.

Nowhere will they be a DateTimeOffset value, if you want a DateTimeOffset value, you have to use the Timetz type for the database field.

If you want to have the value of the Timestamptz field converted to be a DateTimeOffset value, you need to use a typeconverter.

As you give very little info about specifics regarding the failed query and the types in the projection I can't give you a more detailed answer and what you should do to fix the problems you run into.

sherifr (User)   Posted on: 12-Nov-2019 17:41:06.
Okay, I will prepare a demo and post a link for it here.
Otis (LLBLGen Pro Team)   Posted on: 13-Nov-2019 09:02:16.
I think it's key that you give more info what it is that you're after and what you're using as table models.

sherifr (User)   Posted on: 13-Nov-2019 10:50:27.
Dear Frans,

Could you take a look on this repository of mine:

This describes what I was trying to say. While I don't know if this is suitable for you or not.
sherifr (User)   Posted on: 13-Nov-2019 10:55:49.
Kindly, read the first. This repository is created only for you. It contains all my knowledge about the subject.

Otis (LLBLGen Pro Team)   Posted on: 13-Nov-2019 15:08:32.
ah I see what you mean.
I think, but I asked Marc to conform this, is that Dapper does an implicit conversion between the DateTime value and the DateTimeOffset property in the DTO you project to and LLBLGen Pro does not. So that way your query on dapper works, and on llblgen pro it doesn't, at least not by default.

Our runtime does support implicit type conversions for resultsets, by using the
new PlainSQLFetchAspects() { PerformImplicitTypeConversions = true}
construct, as shown below in our test for your situation, but it we discovered with this that there's no implicit conversion defined in our pipeline class for this feature: it performs a lot of conversions (like short to int etc.) but not for value to DateTimeOffset.

We'll see this as a bug in v5.5 and v5.6 and will release a fix for this. With the fix the query below should work (it currently indeed doesn't).


public class TimezoneDest
    public long Id { get; set; }
    public DateTimeOffset DateCreated { get; set; }

public void TimezoneConversionImplicitlyTest()
    using(var adapter = new DataAccessAdapter())
        var currentEntities = new LinqMetaData(adapter).Timezonetest.ToList();
        if(currentEntities.Count <= 0)
            // insert entities
            var toInsert = new EntityCollection<TimezonetestEntity>();
            toInsert.Add(new TimezonetestEntity() { Id = 1, DateCreated = DateTime.Now});
            toInsert.Add(new TimezonetestEntity() {Id = 2, DateCreated = DateTime.Now});
            toInsert.Add(new TimezonetestEntity() {Id = 3, DateCreated = DateTime.Now});

        var results = adapter.FetchQuery<TimezoneDest>(new PlainSQLFetchAspects() { PerformImplicitTypeConversions = true},
                                                     "SELECT id as Id, date_created as DateCreated FROM public.timezonetest WHERE id > @id", new {id = 0});
        Assert.AreEqual(3, results.Count);
        foreach(var v in results)
            Console.WriteLine("id:{0}, dc:{1}", v.Id, v.DateCreated);

Otis (LLBLGen Pro Team)   Posted on: 13-Nov-2019 16:02:20.
We fixed it, in runtime version 5.5.6 and 5.6.2, which are now on nuget as hotfix builds.

The conversion between DateTime->DateTimeOffset is 'implicit' but only works through a cast. Convert.ChangeType(value, typeof(DateTimeOffset)) for instance still crashes. A bit of an odd decision in the .net BCL, but alas...

your code should now work.

sherifr (User)   Posted on: 28-Nov-2019 14:20:40.
Dear Frans, I am sorry that I am taking to much to reply. But I have currently no time to test to be able to provide feedback. I hope you understand this.
Otis (LLBLGen Pro Team)   Posted on: 28-Nov-2019 15:45:10.
No worries Regular Smiley We saw we missed a special case implementation for this, added tests to cover it and they pass now so it should work fine. When you have time and things don't work, please just post in this thread, it'll automatically re-open Regular Smiley