Dapper limitations
There are a few limitations you should be aware of when using Microsoft.Data.Sqlite with Dapper.
Parameters
SQLite parameter names are case-sensitive. Ensure that the parameter names used in SQL match the case of the anonymous object's properties. Issue #18861 would improve this experience.
Dapper also expects parameters to use the @ prefix. Other prefixes won't work.
var result = connection.ExecuteScalar(
"SELECT @Value",
new { Value = 1 });
Data types
Dapper reads values using the SqliteDataReader indexer. The return type of this indexer is object, which means it will only ever return long, double, string, or byte[] values. For more information, see Data types. Dapper handles most conversions between these and other primitive types. Unfortunately, it doesn't handle DateTimeOffset, Guid, or TimeSpan. Create type handlers if you want to use these types in your results.
abstract class SqliteTypeHandler<T> : SqlMapper.TypeHandler<T>
{
// Parameters are converted by Microsoft.Data.Sqlite
public override void SetValue(IDbDataParameter parameter, T? value)
=> parameter.Value = value;
}
class DateTimeOffsetHandler : SqliteTypeHandler<DateTimeOffset>
{
public override DateTimeOffset Parse(object value)
=> DateTimeOffset.Parse((string)value);
}
class GuidHandler : SqliteTypeHandler<Guid>
{
public override Guid Parse(object value)
=> Guid.Parse((string)value);
}
class TimeSpanHandler : SqliteTypeHandler<TimeSpan>
{
public override TimeSpan Parse(object value)
=> TimeSpan.Parse((string)value);
}
Don't forget to add the type handlers before querying.
SqlMapper.AddTypeHandler(new DateTimeOffsetHandler());
SqlMapper.AddTypeHandler(new GuidHandler());
SqlMapper.AddTypeHandler(new TimeSpanHandler());
