Hi,

I am trying to execute the following code

            RuntimeConfiguration.ConfigureDQE<PostgreSqlDQEConfiguration>(x =>
            {
                x.AddDbProviderFactory(typeof(NpgsqlFactory));
            });

            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

DateTimeOffset 

in case of

timestamptz

, 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

This has been addressed before, here

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

Timestamptz

) 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

timestamptz 

is parsed and used in the query as

DateTimeOffset

. 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.

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.

Okay, I will prepare a demo and post a link for it here.

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.

Dear Frans,

Could you take a look on this repository of mine: https://github.com/SherifRefaat/TestNpgsqlDapperDateTimeOffset

This describes what I was trying to say. While I don't know if this is suitable for you or not.

Kindly, read the readme.md first. This repository is created only for you. It contains all my knowledge about the subject.

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; }
}


[Test]
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});
            adapter.SaveEntityCollection(toInsert);
        }

        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);
        }
    }
}

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.

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.

No worries 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