Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> DateTimeOffset Mapping
 

Pages: 1
Bugs & Issues
DateTimeOffset Mapping
Page:1/1 

  Print all messages in this thread  
Poster Message
sherifr
User



Location:

Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
# Posted on: 07-Nov-2019 14:47:06.  
Hi,

I am trying to execute the following code

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
Code:
DateTimeOffset
in case of
Code:
timestamptz
, I get the following exception

Code:
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

Code:
    <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
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14592 posts
# Posted on: 07-Nov-2019 17:26:50.  
This has been addressed before, here

  Top
sherifr
User



Location:

Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
# 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
Code:
Timestamptz
) this is seems fine. But if you try to use the parsed datetime value in a query like the following
Code:
.Where(x => x.DateCreated >= minFetchedTimestamptz && x.DateCreated <= maxFetchedTimestamptz)

this won't fetch the right values unless the
Code:
timestamptz
is parsed and used in the query as
Code:
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.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37885 posts
# 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.



Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sherifr
User



Location:

Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
# Posted on: 12-Nov-2019 17:41:06.  
Okay, I will prepare a demo and post a link for it here.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37885 posts
# 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.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sherifr
User



Location:

Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
# Posted on: 13-Nov-2019 10:50:27.  
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.
  Top
sherifr
User



Location:

Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
# Posted on: 13-Nov-2019 10:55:49.  
Kindly, read the readme.md first. This repository is created only for you. It contains all my knowledge about the subject.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37885 posts
# 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
Code:
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).

Code:

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

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37885 posts
# 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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sherifr
User



Location:

Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
# 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.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37885 posts
# 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

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.