Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DateOnly "off by one" column error #2072

Open
edwardmjackson opened this issue Apr 15, 2024 · 12 comments
Open

DateOnly "off by one" column error #2072

edwardmjackson opened this issue Apr 15, 2024 · 12 comments

Comments

@edwardmjackson
Copy link

edwardmjackson commented Apr 15, 2024

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
Copy link

quiian commented Apr 15, 2024

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.. :)

Screenshot 2024-04-15 141405
image

@mgravell
Copy link
Member

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

@sander1095
Copy link

sander1095 commented Apr 16, 2024

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
Copy link

Gondlir commented Apr 18, 2024

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
Copy link
Member

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
Copy link

This is the same issue I reported in #2071

@mgravell
Copy link
Member

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
Copy link

Dean-NC commented May 1, 2024

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
Copy link

Dean-NC commented May 11, 2024

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
Copy link

buzz100 commented Jul 5, 2024

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
Copy link

W0GER commented Sep 9, 2024

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

@mgravell
Copy link
Member

I need to revisit what went wrong here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

9 participants