I am using Dapper in an ASP.NET Core 6 Web API project with SQL Server 2016 database as backend. The ExecuteScalar method of Dapper is returning an error

The member DepartureTime of type System.TimeOnly cannot be used as a parameter value

Is there any workaround?

public virtual int Create(TModel entity)
{
    int result = -1;
    var insertQuery = QueryGenerator.GenerateInsertQuery(typeof(TModel));
    var factory = new SqlServerDbConnectionFactory(connectionString);
    var profiler = CustomDbProfiler.Current;

    using (var connection = ProfiledDbConnectionFactory.New(factory, profiler))
    {
        try
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.ConnectionString = connectionString;
                connection.Open();
            }

            result =  connection.ExecuteScalar<int>(insertQuery, entity, transaction: null); // Throwing error
        }
        catch (Exception e)
        {
            var sql = profiler.GetCommands();
            throw;
        }
    }

    return result;
}

insertQuery contains the following query:

INSERT INTO [Flight] ([FlightNumber], [SourceCode], [DestinCode],[DepartureTime], [EffectDate], [Stops], [TravelMinutes]) 
VALUES (@FlightNumber, @SourceCode, @DestinCode, @DepartureTime, @EffectDate, @Stops, @TravelMinutes);
SELECT SCOPE_IDENTITY()

entity is of type FlightModel:

public class FlightModel
{
    public int Id { get; set; }
    public string FlightNumber{ get; set; }
    public string SourceCode{ get; set; }
    public string DestinCode { get; set; }
    public TimeOnly DepartureTime{ get; set; }
    public DateOnly EffectDate { get; set; }
    public int Stops{ get; set; }
    public int TravelMinutes { get; set; }
}

CodePudding user response：

From here, 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);
    }
}

CodePudding user response：

You can use TimeSpan:

public class FlightModel
{
    ...
    public TimeSpan DepartureTime{ get; set; }
    public DateTime EffectDate { get; set; }
}