DateOnly "off by one" column error

[edwardmjackson]

Hi,

Using Microsoft.Data.SqlClient 5.2.0 and Dapper 2.1.44, we've noticed an issue with the new DateOnly type support from #2051.
Using a simple class such as

  public class PersonTest
  {
    public string FirstName { get; set; }
    public DateOnly FromDate { get; set; }
  }

and a QueryAsync with

    public async Task<PersonTest> GetPerson(string Username)
    => (await QueryAsync<PersonTest>(AdminProcs.GetPerson, new { Username })).FirstOrDefault();

we get an error response of

{
  "type": "System.Data.DataException",
  "title": "Dapper",
  "status": 500,
  "detail": "Error parsing column 1 (FromDate=Ed - String)",
  "traceId": "00-ae3a4c30e843c63d927302fde275a754-ca340c148ee9f15f-00"
}

The data, returned by MS SQL Server, as a varchar and DateTime type, are as follows:

FirstName                                          FromDate
-------------------------------------------------- -----------------------
Ed                                                 2019-10-01 00:00:00.000

The issue seems to be that it is trying to parse column 1 as the FromDate with the value of Ed, which is in column 0. Hence the "off by one" error.

Running this with Dapper 2.1.35, using the the previous custom solution from #1715, does not have this error.

Staying on 2.1.44, if we remove FromDate from PersonTest, it works fine with just the FirstName.
Changing FromDate in PersonText to a more open type, such as string, also works fine.
Changing the SQL type, as e.g., as MS SQL Date, or an int or varchar in ISO8601 format, doesn't have any effect.

[quiian]

Also ran into this, dapper attempts to parse DateOnly from column 11 instead of column 12. Code was working fine before we updated. Attempting to roll back for now.. :)

[mgravell]

Well that sounds very bad; I'm hiding 2.1.44 while I investigate - hopefully should have a fix out today or tomorrow

[sander1095]

I'm running into the same issue. I stronly feel that it is related #2051 as I had my own SqlMapper.TypeHandler<DateOnly> . I feel like Dapper now ignores my custom handler and uses it's built-in one.

Perhaps it's also worth talking about that specific behaviour. Wouldn't it make more sense for Dapper to still use my own mapper, which (probably) should not have made this bug show up in my code?

[Gondlir]

I had almost the same problem. Tell me, do you use .NET 8 with Arm64?
Where I work there is a team that uses Dapper. They upgraded to .NET 8 with ARM64 and had problems parsing columns.
I'm not sure if this was 100% the cause but I had the same problem in a project that used dapper, after this update to .NET 8 with ARM64 the Dapper simply broke.
I think it's worth the investigation @mgravell

[mgravell]

well, I'm going to assume it isn't as exotic as that and is simply a "Marc is an idiot" thing; hopefully should be able to look on Friday (I've been completely snowed under)

[GonzaloVisma]

This is the same issue I reported in #2071

[mgravell]

I have been unable to get a repro of this failing; for now I guess I have no choice except to revert that change, but if anyone who was seeing this problem could show a runnable repro against 2.1.44 (or similar affected), I'd be very grateful

[Dean-NC]

I use 2.1.37 targeting .NET 8 with no problem for DateOnly for both records and classes. I use Microsoft.Data.SqlClient 5.1.3 (accepting the security risks and will stay on it until they split-out the basic client from Azure and Identity, mainly so it doesn't require .NET framework). I don't use any custom type mapping, and I use queries like:

connection.QueryAsync<MyRecord>("Select Id, Col2, MyDateCol From Table...")

So, I'll stay on .37. I also use Dapper.AOT, but it doesn't support DateOnly yet.

[Dean-NC]

If the db column is defined with a time component like DateTime2 or DateTime, then I do get this error of being off by 1, but not if the column is defined as Date.

[buzz100]

I have been unable to get a repro of this failing; for now I guess I have no choice except to revert that change, but if anyone who was seeing this problem could show a runnable repro against 2.1.44 (or similar affected), I'd be very grateful

Hi,
Very easy to reproduce, create a new .Net 8 console app on Win 11
Issue only occurs when the database field is DateTime or DateTime2 (Date is fine) and and the c# object property is DateOnly

Precision of DateTime2 has no impact

Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) Mar 19 2024 00:23:01 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

Program.cs

using Dapper;
using Microsoft.Data.SqlClient;
 
Console.WriteLine("Dapper Test");
 
 
var sql = "select Title, MyDate from zDapperTest";
 
var ServerName = "hidden";
var DbName = "hidden";
var User = "hidden";
var Password = "hidden";
 
using (var connection = new SqlConnection($"Data Source={ServerName};Initial Catalog={DbName}; User ID='{User}';Password='{Password}';Encrypt=false;"))
{
    Console.WriteLine(connection.Query<DataObject>(sql).Single().MyDate);
}
 
public class DataObject
{
    public string Title { get; set; } = "";
    public DateOnly MyDate { get; set; }
 
}

Project File

<Project Sdk="Microsoft.NET.Sdk">
 
  <PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net8.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
 
  <ItemGroup>
<PackageReference Include="Dapper" Version="2.1.44" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.1" />
</ItemGroup>
 
</Project>

[W0GER]

Any updates on this issue? This is blocking us from upgrading to the latest version.

[mgravell]

I need to revisit what went wrong here.