.NET 6 DateOnly and TimeOnly mapping support

[arnederuwe]

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

[roji]

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.

[FatTigerWang]

I tracked the source code and found that the TimeOnly and DateOnly types are not supported here. I am not sure if adding these two types to the collection and specifying them as DbType.Date and DbType.Time will work. I will try if it works.

Dapper does not support .NET 6, so DateOnly and TimeOnly types cannot be used

Reference: System.Data.DbType

[kevingates]

Any timeline when this may be fixed?

[mgravell]

@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

[szalapski]

Very much would like to see this. It is weird that SQL Server's DATE type should map to anything but DateOnly in C#.

[h181422]

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

[danielearwicker]

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

[K0rhak]

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

[PedroC88]

How 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 NULL and the breakpoints on the convertion methods aren't being hit.

[h181422]

SqlMapper.AddTypeHandler(new DateOnlyTypeHandler());
SqlMapper.AddTypeHandler(new TimeOnlyTypeHandler());

I've got them in my container config, where they are registered at startup.

[PedroC88]

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

[ScottRFrost]

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

[yacine-karim]

Make sure to declare the DateOnly property is declared as Nullable DateOnly? if the table contains a null values

[AdisonCavani]

SetValue function is called and it works, however Parse is never called. It's always returning NULL

[nhustak]

Is there still no native support for DateOnly & TimeOnly?

[mgravell]

There are some complications on the read side that make it a much bigger change than you would think. I have some ideas,though.

[KeithHenry]

To make this work with record classes I've been using an alternative constructor, which may be useful to others working around this...

With a select like:

select convert(date, Created) 'day', count(*) 'value' 
from...
group by convert(date, Created)

This crashes:

public record WithDateOnly(DateOnly Day, int Value);

Because there is no constructor accepting System.DateTime, System.Int32, even though SQL has output its native date type.

The workaround is to add an alternative constructor:

public record WithDateOnly(DateOnly Day, int Value) {
    public WithDateOnly(DateTime day, int value) : 
        this(DateOnly.FromDateTime(day), value) { }
}

[VictorioBerra]

Just hit this today. Would love to see this built-in. DateOnly has been in 2 major .NET versions now.

[mgravell]

Untested (not at PC), but you could try:

SqlMapper.AddTypeMap(typeof(DateOnly), (DbType)-1, true);
SqlMapper.AddTypeMap(typeof(TimeOnly), (DbType)-1, true);

It is hard for us to configure this automatically because different providers need different configurations to work correctly here.

[mkorsukov]

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.

[nunutu29]

In some cases (i.e. System.Data.SqlClient) parameter.DbType = DbType.Date is 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}" );
    }
}

[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?

[mgravell]

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

[mkorsukov]

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

[lansman]

@mkorsukov I don't have any SqlClient in my project at all

[buzz100]

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)

https://techcommunity.microsoft.com/t5/sql-server-blog/announcement-system-data-sqlclient-package-is-now-deprecated/ba-p/4227205

[nunutu29]

@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 DateOnlyTypeHandler make sure to include the following code in your startup class:

SqlMapper.RemoveTypeMap(typeof(DateOnly));
SqlMapper.AddTypeHandler(new DateOnlyTypeHandler());

[lansman]

@nunutu29

The 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!

[nunutu29]

@nunutu29

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