Here is the simple example showing the problem (even though it does not call SqlKata):
[Fact]
public void Dapper_WithSqlServerFailsWithRegularBinaryNullable()
{
// Arrange
using var connection = new SqlConnection(Fixture.ConnectionString);
connection.Open();
// This simulates what SqlKata Compiler does with typed parameters: it converts them into dictionary
// SqlResult.NamedBindings thus losing the type information for null parameters
var parametersObject = new Dictionary<string, object?>
{
// ReSharper disable once RedundantCast
["BinaryData"] = (byte[]?)null
};
// Act
var action = () =>
// ReSharper disable once AccessToDisposedClosure
connection.Execute(
"INSERT INTO NullableVarbinary (BinaryData) VALUES (@BinaryData)",
parametersObject
);
// Assert
action
.Should()
.Throw<SqlException>()
.WithMessage(
"Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."
);
}
The way the SqlResult.DynamicParameters are declared makes it impossible to pass null value while maintaining type information for Dapper to do its job mapping to DB type.
Since the class is declared in core library and used by all compilers, it's hard to work around this problem.
I would propose making NamedBindings an interface similar to Dapper.IDynamicParameters with implementation that could be replaced for a QueryFactory.
Here is the simple example showing the problem (even though it does not call SqlKata):
The way the SqlResult.DynamicParameters are declared makes it impossible to pass
null
value while maintaining type information for Dapper to do its job mapping to DB type.Since the class is declared in core library and used by all compilers, it's hard to work around this problem.
I would propose making
NamedBindings
an interface similar to Dapper.IDynamicParameters with implementation that could be replaced for aQueryFactory
.