Runtime error when using DateOnly

I have a database that has a date property. However, the C# class is DateOnly since I only need the date, not the time. However, when I try to use a sql command and the c# class is DateOnly, the error is something like this
The member WhatDate1 of type System.DateOnly cannot be used as a parameter value
at Dapper.SqlMapper.LookupDbType(Type type, String name, Boolean demand, ITypeHandler& handler) in /_/Dapper/SqlMapper.cs:line 426

WhatDate1 is the field name

Here is what I think can fix the problem. Under static SqlMapper() in SQLMapper.cs file, if you add DateOnly and DateOnly?, that will fix the problem.
I would also need TimeOnly and TimeOnly? to be added for cases where only time is needed.

There's also a branch (and possibly PR) that does this, but I was waiting on .NET 6 GA (which has now passed, so: yay).

So: what RDBMS/provider are you seeing this with, so I can validate?

I am using the sql server one.

There are 2 official SQL Server drivers - System.Data.SqlClient, and Microsoft.Data.SqlClient; which are you using, and exactly what version?

Sorry, I should have added: I don't mean to sound picky - my aim is to add the relevant tests and deploy this tomorrow, so anything I can discover to help shorten that: is good

…

On Sun, 21 Nov 2021, 19:15 musictopia2, ***@***.***> wrote: I am using the sql server one. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#1728 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAEHMEANBB43LK6BFT3KB3UNFAMZANCNFSM5IPHCV7Q> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

The version I am using is this one.

Microsoft.Data.SqlClient
version 1.1.1
Tried to add the xml but that was deleted.

looks like they have that one up to 4.0.0.
I can use that one though if that would help as well.

So: I updated my test harness, and: it simply doesn't work for either SqlClient; both give an exception like:

  Message: 
System.ArgumentException : No mapping exists from object type System.DateOnly to a known managed provider native type.

  Stack Trace: 
MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)
MetaType.GetMetaTypeFromType(Type dataType)
SqlParameter.GetMetaTypeOnly()
SqlParameter.Validate(Int32 index, Boolean isCommandProc)
SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters, Boolean includeReturnValue)
SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
SqlCommand.ExecuteReader(CommandBehavior behavior)
SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

Do you have an example of it actually working with raw ADO.NET?

(to be clear: I mean "the underlying ADO.NET provider doesn't work with DateOnly/TimeOnly", not "Dapper doesn't ...")

Cross-referencing: dotnet/SqlClient#1009

Do you think there is a way for dapper to do some type of version so if dateonly was used, then it can somehow go to the external as datetime behind the scenes. Because not only its possible they may not ever fix it, but i found the newest version of microsofts version of the sql provider only works if a person uses ssl on a webpage which would not work if somebody had an intranet that is only for people on the local network with no connection to the internet.

I see an issue that was posted here dotnet/efcore#24507 where they showed a converter a person can use as a temporary workaround in entity framework core. Can something like this be done to dapper? Would be disappointing if a person is forced to use entity framework core. If that is the case, then dapper would not work so well since without the dateonly support, businesses are blocked from even moving forward.

additional work on #1728

@mgravell

@mgravell re return types, yeah - that's expected; I didn't change the default return type for PostgreSQL date and time to avoid breaking people (it would also mean the driver would have returned different types across different .NET TFMs). So these still return DateTime and TimeSpan, respectively. You can use GetFieldValue<DateOnly> to get what you want - hopefully that's something Dapper can do. I this this would also solve the SQLite-side issue (where there's no database-side types at all for these).

Re the precision issue, can you provide more detail, ideally with an ADO.NET repro? Here's some ADO.NET code that shows what I think is correct behavior:

await using var conn = new NpgsqlConnection("Host=localhost;Username=test;Password=test");
await conn.OpenAsync();

var expected = TimeOnly.MaxValue;
await using var command = new NpgsqlCommand("SELECT @p", conn)
{
    Parameters = { new("p", expected) }
};
await using var reader = await command.ExecuteReaderAsync();
await reader.ReadAsync();

var actual = reader.GetFieldValue<TimeOnly>(0);
Console.WriteLine($"Actual:   {actual:o}");
Console.WriteLine($"Expected: {expected:o}");

The results are:

Actual:   23:59:59.9999990
Expected: 23:59:59.9999999

The discrepancy is normal, since .NET has tick precision (100ns) whereas PostgreSQL has microsecond precision (1000ns). Are you seeing something different?

As for more thoughts on how to handle this; I don't know if this would be considered "doing too much", but I'd kind of like the idea of member attributes. Where you'd have something like this

    [DapperSerialize(Convert.ToString)]
    [DapperDeserialize(DateOnly.Parse)]
    internal string DateString { get; init; }

Where a static method needs to be passed in with exactly one argument. It's fairly trivial to write your own static method if you need something more complex.

@roji

@roji ah, ta; I changed the rounding to millis and it still passes, so: great!

Re the GetFieldValue<>() - that's a bigger change; I'll need to think; maybe this is a good time to code a list of types that should use that approach. I also need to fix SQLite for this scenario, so... fun!

Yeah, makes sense. Let me know if you need anything else.

Any workarounds for this?

Unfortunately, there is still none unfortunately.

There are 2 official SQL Server drivers - System.Data.SqlClient, and Microsoft.Data.SqlClient; which are you using, and exactly what version?

For both do not work

FYI I am using "Microsoft.Data.SqlClient" Version="5.1.0". I have introduced the type handlers from #1715 (comment) and they are working great - any chance we can get them in Dapper directly?

Probably not relevant but i've come across this https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#dateonlytimeonly-supported-on-sql-server

It sound like a "recent release of a [Microsoft.Data.SqlClient]" added features that made it possible for someone to add EF support for these types, maybe the change to Microsoft.Data.SqlClient may also allow dapper to support it with some work?

"For SQL Server, the recent release of a Microsoft.Data.SqlClient package targeting .NET 6 has allowed ErikEJ to add support for these types at the ADO.NET level. This in turn paved the way for support in EF8 for DateOnly and TimeOnly as properties in entity types."

I tried to insert a DateOnly into a datetime sql server column and received this error about it not being able to be used as a parameter value.

It's not a problem for me because I can just switch to DateTime, but I guess this issue is still relevant. Is there anything blocking the implementation?

I actually created my own library like dapper and the first thing i did with this library was make sure it would work with date only (which it did). plus its completely reflection free.

Dapper is a fantastic library and I really appreciate the work that been done.
I can see this job says "waiting on external factors outside our control" and that all fine, nothing you can do about that.

I would be interested to understand what key issues are and what would need to change to address this issue?
We might then be able to upvote some change requests on whatever your dependent on

before i created my own library, i looked at their code but don't really understand it. for what i did, i did not even rely on the provider. they wanted to trust the provider. i ended up doing my own work to do the parsing to the dateonly. those chose not to do that unfortunately.

Re-enable DateOnly/TimeOnly support, fixing the defects that got it d…

Navigation Menu

Repository navigation

Runtime error when using DateOnly #1728

7 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Runtime error when using DateOnly #1728

Description

Activity

musictopia2 commented on Nov 21, 2021

mgravell commented on Nov 21, 2021

musictopia2 commented on Nov 21, 2021

mgravell commented on Nov 21, 2021

mgravell commented on Nov 21, 2021

musictopia2 commented on Nov 21, 2021

musictopia2 commented on Nov 21, 2021

musictopia2 commented on Nov 21, 2021

mgravell commented on Nov 22, 2021

mgravell commented on Nov 22, 2021

mgravell commented on Nov 22, 2021

musictopia2 commented on Nov 22, 2021

musictopia2 commented on Nov 22, 2021

7 remaining items

roji commented on Dec 9, 2021

Rainmaker52 commented on Dec 9, 2021

mgravell commented on Dec 9, 2021

roji commented on Dec 9, 2021

Anarios commented on Mar 12, 2022

musictopia2 commented on Mar 12, 2022

zanyar3 commented on Nov 30, 2022

celluj34 commented on Mar 1, 2023

buzz100 commented on Apr 14, 2023

CrispyDrone commented on Jan 27, 2026

musictopia2 commented on Jan 27, 2026

buzz100 commented on Jan 28, 2026

musictopia2 commented on Jan 29, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions