|
|
sherifr
User
Location:
Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
|
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 in case of , 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
|
|
|
Walaa
Support Team
Location:
Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
|
This has been addressed before, here
|
|
|
sherifr
User
Location:
Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
|
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
| Code: |
.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
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
|
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
Location:
Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
|
|
Okay, I will prepare a demo and post a link for it here.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
|
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
Location:
Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
|
|
|
sherifr
User
Location:
Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
|
Kindly, read the readme.md first. This repository is created only for you. It contains all my knowledge about the subject.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
|
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);
}
}
}
|
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
|
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
Location:
Joined on:
26-Apr-2018 21:18:02
Posted:
11 posts
|
|
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
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
|
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
|
|
|
|