Repository navigation
.NET 6 DateOnly and TimeOnly mapping support #1715
Description
Activity
I gave this a try with an Npgsql 6.0 RC, which does already support DateOnly/TimeOnly, and it seems this doesn't work:
await conn.ExecuteScalarAsync("SELECT @Foo", new { Foo = new DateOnly(2020, 1, 1) })
Throws:
Unhandled exception. System.NotSupportedException: The member Foo 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 417 at Dapper.SqlMapper.CreateParamInfoGenerator(Identity identity, Boolean checkForDuplicates, Boolean removeUnused, IList`1 literals) in /_/Dapper/SqlMapper.cs:line 2504 at Dapper.SqlMapper.GetCacheInfo(Identity identity, Object exampleParameters, Boolean addToCache) in /_/Dapper/SqlMapper.cs:line 1727 at Dapper.SqlMapper.ExecuteScalarImplAsync[T](IDbConnection cnn, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 1200 at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 12 at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 12 at Program.<Main>(String[] args)Somewhat related to #1716... I know nothing about Dapper internals, but ideally it would be possible to use any arbitrary .NET type as a parameter, and Dapper would simply pass that along to the DbParameter.Value, without anything else (that would obviate needing any special type handlers or something). But there may be some good reason why things don't work this way currently.
I tracked the source code and found that the
TimeOnlyandDateOnlytypes are not supported here. I am not sure if adding these two types to the collection and specifying them asDbType.DateandDbType.Timewill work. I will try if it works.Dapper does not support
.NET 6, soDateOnlyandTimeOnlytypes cannot be usedReference: System.Data.DbType
Reacted by Arne Deruwe, Mostafa BLEU, buzz100, Dmitrii Selivanov, Kunal Mukherjee and Yanuarizal KurniaReacted by Christof Sprenger, ca-rose and OtavianoAny timeline when this may be fixed?
Reacted by ohnv and Silvio Delgado@kevingates see also #1728; there is an open branch, but we need to first be sure how this is intended to interact with the various providers
Reacted by Frank AlvarezVery much would like to see this. It is weird that SQL Server's DATE type should map to anything but DateOnly in C#.
Reacted by Julian May, Frank Alvarez, Ronnie Overby, Scuzzlebuttt, William Baldwin, Tim Thompson, Cory Robinson and Daniel JonssonFor those who are looking for a workaround: make your own type handler.
Add the following to your configuration:
SqlMapper.AddTypeHandler(new SqlTimeOnlyTypeHandler());public class SqlTimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly> { public override void SetValue(IDbDataParameter parameter, TimeOnly time) { parameter.Value = time.ToString(); } public override TimeOnly Parse(object value) { return TimeOnly.FromTimeSpan((TimeSpan)value); } }Reacted by Isaac Rabin, JensErikP, Ronald Swaine, Isaac Lyman, Scuzzlebuttt, George, Farhad Zamani, REDECODE, Dennis Langthjem, Eryckson Magno and 6 moreAnd similarly for
DateOnly:public class DapperSqlDateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly> { public override void SetValue(IDbDataParameter parameter, DateOnly date) => parameter.Value = date.ToDateTime(new TimeOnly(0, 0)); public override DateOnly Parse(object value) => DateOnly.FromDateTime((DateTime)value); }
Reacted by Frank Alvarez, Isaac Rabin, Adnan, JensErikP, Ronald Swaine, Vladyslav Sats, Zamran Gill, Edgar Calderón Muñoz, Andrew Carr, KTownD and 23 moreI had problems with database conversion so I had to specify the type. Like this
public override void SetValue(IDbDataParameter parameter, DateOnly date) { parameter.DbType = DbType.DateTime; parameter.Value = date.ToDateTime(new TimeOnly(0, 0)); }
Reacted by Isaac Lyman, Kris Bunda and ScuzzlebutttHow exactly is the TypeHandler used? I have the following line in the constructor of my DB class
SqlMapper.AddTypeHandler(new DateOnlyTypeHandler()); SqlMapper.AddTypeHandler(new TimeOnlyTypeHandler());
and the following TypeHandlers
public class DateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly> { public override DateOnly Parse(object value) => DateOnly.FromDateTime((DateTime)value); public override void SetValue(IDbDataParameter parameter, DateOnly value) { parameter.DbType = DbType.Date; parameter.Value = value; } } public class TimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly> { public override TimeOnly Parse(object value) => TimeOnly.FromDateTime((DateTime)value); public override void SetValue(IDbDataParameter parameter, TimeOnly value) { parameter.DbType = DbType.Time; parameter.Value = value; } }
But I'm still getting a an exception that the Date field in my object is
NULLand the breakpoints on the convertion methods aren't being hit.Reacted by Harsha Vardhan, KTownD, Christophe CHATEAU, Scuzzlebuttt, William Baldwin, Adrian Środoń, Silvio Delgado, Cory Robinson and Daniel JonssonSqlMapper.AddTypeHandler(new DateOnlyTypeHandler()); SqlMapper.AddTypeHandler(new TimeOnlyTypeHandler());
I've got them in my container config, where they are registered at startup.
Reacted by KTownD, Scuzzlebuttt and AdamThat makes more sense indeed. And I also realized that z.dapper.pluss doesn't use the TypeHandlers for bulk inserts, which is why the breakpoints weren't hitting the methods in the first place.
Found this issue in a search and was able to get the type handlers working (The SqlMapper.AddTypeHandler lines go in Startup.cs \ ConfigureServices if you're working on an API by they way, that took me a few minutes to figure out).
However, to make them work in converting either a MS SQL DateTime OR a MS SQL Time to TimeOnly, I needed this modification (MS SQL Date to DateOnly seemed to work without change):
public class DateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly> // Dapper handler for DateOnly { public override DateOnly Parse(object value) => DateOnly.FromDateTime((DateTime)value); public override void SetValue(IDbDataParameter parameter, DateOnly value) { parameter.DbType = DbType.Date; parameter.Value = value; } } public class TimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly> // Dapper handler for TimeOnly { public override TimeOnly Parse(object value) { if (value.GetType() == typeof(DateTime)) { return TimeOnly.FromDateTime((DateTime)value); } else if (value.GetType() == typeof(TimeSpan)) { return TimeOnly.FromTimeSpan((TimeSpan)value); } return default; } public override void SetValue(IDbDataParameter parameter, TimeOnly value) { parameter.DbType = DbType.Time; parameter.Value = value; } }Hopefully these mappings can be added to default dapper one day
Reacted by brian, Peter Dzugas, Francisco Coulon, Vladimir Chirikov and Raymond SunReacted by Guilherme Lima de Melo, MinhTNQ and David GuerraMake sure to declare the DateOnly property is declared as Nullable
DateOnly?if the table contains a null valuesSetValuefunction is called and it works, howeverParseis never called. It's always returning NULLIs there still no native support for DateOnly & TimeOnly?
5 remaining items
- added a commit that references this issue
on Mar 7, 2024 After dependencies upgrade to latest versions, new .NET types seem to work fine with Dapper.
<ItemGroup> <PackageReference Include="Dapper" Version="2.1.37" /> <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.0" /> </ItemGroup>And custom type handlers
SqlMapper.TypeHandler<DateOnly>are not used by Dapper any more.In some cases (i.e. System.Data.SqlClient)
parameter.DbType = DbType.Dateis automatically converted to DbType.DateTime (See here).private class DateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly> { public override void SetValue(IDbDataParameter parameter, DateOnly value) { // Sets the parameter's data type to DbType.Date. parameter.DbType = DbType.Date; // Note: // - In System.Data.SqlClient, setting DbType.Date is automatically converted to DbType.DateTime. // - In Microsoft.Data.SqlClient, setting remains DbType.Date. if (parameter.DbType == DbType.DateTime) { // If the parameter's data type has been changed to DbType.DateTime, // converts the DateOnly value to DateTime, setting the time to zero. parameter.Value = value.ToDateTime(new TimeOnly(0, 0, 0, 0)); } else { // Otherwise, directly assigns the DateOnly value to the parameter. parameter.Value = value; } } public override DateOnly Parse(object value) { // Checks if the value is already of type DateOnly if (value is DateOnly dtOnly) { return dtOnly; } // Checks if the value is of type DateTime and converts it to DateOnly if (value is DateTime dt) { return DateOnly.FromDateTime(dt); } // If the value is neither DateOnly nor DateTime, throws an exception with an error message throw new InvalidOperationException($"Errore durante la conversione DateOnly: {value}" ); } }
Reacted by lansman@nunutu29 hi, I've got an error in my project
System.Data.DataException: Error parsing column 0 (date=23.09.2024 00:00:00 - DateTime) ---> System.InvalidCastException: Invalid cast from 'System.DateTime' to 'System.DateOnly'.
I use dotnet core 8.0. Dapper 2.1.35 (the latest nuget version).
Npgsql 7.0.7. Npgsql.EntityFrameworkCore.PostgreSQL 7.0.18.
UPD: upgraded to Npgsql, Npgsql.EntityFrameworkCore.PostgreSQL 8.0.4, Microsoft.EntityFrameworkCore 8.0.8, it didn't help.
Database field is Date. C# DTO field is DateOnly. I don't want to have any intermediate DateTime cast, how to avoid it?// - In System.Data.SqlClient, setting DbType.Date is automatically converted to DbType.DateTime.
// - In Microsoft.Data.SqlClient, setting remains DbType.Date.I don't use any of these libraries explicitly. Are they used by Dapper implicitly? But how can I find out which one is used? How can I switch to another?
We're still trying to unpick the mess of DateOnly/TimeOnly; different ADO.NET providers have different demands here (and the underlying connection comes from your code - that isn't something that Dapper gets to choose); we're making progress on this topic over in DapperAOT, and when we've stabilised the rules there (where it is easier to debug etc), the plan is to migrate back as much as we can back into Dapper
Reacted by lansman@lansman If you search through you projects (*.csproj files for "SqlClient"), you may find the actual SQL client library. Here is my case:
<ItemGroup> <PackageReference Include="Dapper" Version="2.1.44" /> <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.2" /> </ItemGroup>
@mkorsukov I don't have any SqlClient in my project at all
I assume dapper will continue to support "System.Data.SqlClient", I personally don't need it but its now officially deprecated, this may be one less complication in the future. (Support will be removed from .net 8 at EOL and not included in .net 9)
Reacted by lansman@nunutu29 hi, I've got an error in my project
System.Data.DataException: Error parsing column 0 (date=23.09.2024 00:00:00 - DateTime) ---> System.InvalidCastException: Invalid cast from 'System.DateTime' to 'System.DateOnly'.
I use dotnet core 8.0. Dapper 2.1.35 (the latest nuget version). Npgsql 7.0.7. Npgsql.EntityFrameworkCore.PostgreSQL 7.0.18. UPD: upgraded to Npgsql, Npgsql.EntityFrameworkCore.PostgreSQL 8.0.4, Microsoft.EntityFrameworkCore 8.0.8, it didn't help. Database field is Date. C# DTO field is DateOnly. I don't want to have any intermediate DateTime cast, how to avoid it?
// - In System.Data.SqlClient, setting DbType.Date is automatically converted to DbType.DateTime.
// - In Microsoft.Data.SqlClient, setting remains DbType.Date.I don't use any of these libraries explicitly. Are they used by Dapper implicitly? But how can I find out which one is used? How can I switch to another?
Hello @lansman,
The easiest way is to set a breakpoint during runtime and inspect the connection variable directly.Additionally, to use
DateOnlyTypeHandlermake sure to include the following code in your startup class:SqlMapper.RemoveTypeMap(typeof(DateOnly)); SqlMapper.AddTypeHandler(new DateOnlyTypeHandler());
Reacted by Pascal Köchli and Jan RomeroThe easiest way is to set a breakpoint during runtime and inspect the connection variable directly.
Still no clue
Additionally, to use DateOnlyTypeHandler make sure to include the following code in your startup class:
Thank you, it works for me!
The reason you don't find SqlClient it's simply because you don't have it. You are using Npgsql.
Just had this error this morning after updating to version 2.1.66.
Reverting back to 2.1.44 solved it.
Reacted by aki-seppanen, Casper Weiss Bang, c-m-l-d, Fernando Luiz de Lima, Marcello Rutter and Pedro AzevedoReacted by paulywog80During development, we identified an issue where DateOnly and DateOnly? fields were always being saved as NULL in PostgreSQL, even though the values were correctly populated in the application. Notably, Dapper did not throw any exception— it simply failed to map the DateOnly value to the database parameter.
This happened because Dapper does not natively support the .NET DateOnly type, and as a result, it could not translate the value into a format PostgreSQL understands (date).
To fix this, we implemented a custom Dapper type handler (SqlDateOnlyNullableTypeHandler), which explicitly converts:
DateOnly → DateTime when sending data to the database
DateTime → DateOnly when reading data from the database
After registering this type handler during application startup, Dapper began correctly mapping the DateOnly values, and the dates are now properly persisted in the database.
This solution is efficient, stable, and leverages Dapper’s extensibility model, ensuring accurate date handling across all insert/update operations without modifying the existing database schema.
It seems that starting with version 2.1.86, a custom DateOnlyTypeHandler is no longer needed. 💖
#2228: Re-enable DateOnly/TimeOnly support, fixing the defects that got it disabled
Reacted by Marc Gravell and Maxim Korsukov
.NET 6 will introduce the DateOnly and TimeOnly structs, these are good mapping candidates for SQL Server's Date and Time types.
There is an open issue in the .NET SqlClient repo here
Am I correct to assume that once SqlClient supports it, Dapper will implicitly support it as well? Or is there some work required in this repo as well in order for this to work?