Bug: Wrong where condition due to DbType of parameter

[davidrot]

Bug Description

I have found a bug related to the where condition, what is not matching due to wrong DbType of the DbParameter. Luckily, I am able to reproduce it in a kind of unit test using sqlite.

In this example, count (other methods could be affected as well) is not returning the right value. The condition is simple, but the types are a bit different. Database is using string/text, C# is using DateTime. Therefore, I use a PropertyHandlerMapper.

I debugged down the whole path and found that on DbCommandExtensions.cs:63 the DbType of the DbParameter changed to DateTime. I would assume this is wrong due to the PropertyHandlerMapper. The PropertyHandler is called a view lines later (DbCommandExtensions.cs:312-315) the dbtype is not set.

var parameter = command.CreateParameter();

// Set the values
parameter.ParameterName = name.AsParameter(DbSettingMapper.Get(command.Connection));
parameter.Value = value ?? DBNull.Value; // <-- this line is chaging DbType

DbCommandExtensions.cs:63

Example:

using System;
using System.Data.SQLite;
using System.Globalization;
using RepoDb;
using RepoDb.Enumerations;
using RepoDb.Interfaces;
using RepoDb.Options;
using Xunit;

namespace test;

public class StringToDateTimePropertyHandler : IPropertyHandler<string, DateTime>
{
    public DateTime Get(string input, PropertyHandlerGetOptions property)
    {
        var output = DateTime.Parse(input, null, DateTimeStyles.RoundtripKind);
        return output;
    }

    public string Set(DateTime input, PropertyHandlerSetOptions property)
    {
        return input.ToUniversalTime().ToString("o");
    }
}

public class DateDemo
{
    public Guid Id { get; set; }
    public DateTime Date { get; set; }
}

public class Demo
{
    [Fact]
    public void Test()
    {
        GlobalConfiguration.Setup().UseSQLite();
        PropertyHandlerMapper.Add<DateTime, StringToDateTimePropertyHandler>(new StringToDateTimePropertyHandler(), true);

        var path = "C:\\temp\\test.db";
        var connectionString = $"Data Source={path}; Mode=Memory; Cache=Shared";
        var connection = new SQLiteConnection(connectionString);
        connection.ExecuteNonQuery(@$"CREATE TABLE IF NOT EXISTS [{nameof(DateDemo)}]
                (
                    {nameof(DateDemo.Id)} TEXT PRIMARY KEY,
                    {nameof(DateDemo.Date)} TEXT
                );");

        connection.Insert(new DateDemo { Id = Guid.NewGuid(), Date = DateTime.MaxValue });

        var result = connection.Count<DateDemo>(where: new QueryField("Date", Operation.Equal, DateTime.MaxValue));

        Assert.Equal(1, result);
    }
}

[davidrot]

Today I found a possible solution, then I guess it is not a bug.

public class StringToDateTimePropertyHandler : IPropertyHandler<string, DateTime>
{
    public DateTime Get(string input, PropertyHandlerGetOptions property)
    {
        var output = DateTime.Parse(input, null, DateTimeStyles.RoundtripKind);
        return output;
    }

    public string Set(DateTime input, PropertyHandlerSetOptions property)
    {
        property.DbParameter.DbType = DbType.String;
        return input.ToUniversalTime().ToString("o");
    }
}

[mikependon]

Please leave this one open as we will look on how to fix this one without having to create the Property Handler.