Binder Error: No function matches the given name and argument types '@(DATE)'. You might need to add explicit type casts.

[brendonparker]

I'm getting the following exception when trying to use Dapper and query using a DateTime parameter:

Unhandled exception. DuckDB.NET.Data.DuckDBException (0x00000001): Binder Error: No function matches the given name and argument types '@(DATE)'. You might need to add explicit type casts.
        Candidate functions:
        @(TINYINT) -> TINYINT
        @(SMALLINT) -> SMALLINT
        @(INTEGER) -> INTEGER
        @(BIGINT) -> BIGINT
        @(HUGEINT) -> HUGEINT
        @(FLOAT) -> FLOAT
        @(DOUBLE) -> DOUBLE
        @(DECIMAL) -> DECIMAL
        @(UTINYINT) -> UTINYINT
        @(USMALLINT) -> USMALLINT
        @(UINTEGER) -> UINTEGER
        @(UBIGINT) -> UBIGINT
        @(UHUGEINT) -> UHUGEINT

LINE 1: ...T * FROM items WHERE TransactionDate > @TransactionDate
                                                  ^
   at DuckDB.NET.Data.PreparedStatement.PrepareMultiple(DuckDBNativeConnection connection, String query, DuckDBParameterCollection parameters)+MoveNext()
   at DuckDB.NET.Data.DuckDBDataReader.InitNextReader()
   at DuckDB.NET.Data.DuckDBDataReader..ctor(DuckDbCommand command, IEnumerable`1 queryResults, CommandBehavior behavior)
   at DuckDB.NET.Data.DuckDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

Here is code to reproduce:

using Dapper;
using DuckDB.NET.Data;

await File.WriteAllTextAsync("./items.csv", """
    ItemId,TransactionDate,Quantity
    1001,2024-04-11,1
    1002,2024-04-11,1
    1003,2024-04-11,1
    1003,2024-04-10,1
    """);

using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

await connection.ExecuteAsync("CREATE TABLE items as SELECT * FROM read_csv('./items.csv')");

var items = await connection.QueryAsync<Item>(
    sql: "SELECT * FROM items WHERE TransactionDate > @TransactionDate", 
    param: new { TransactionDate = new DateTime(2024, 04, 10) });

Console.WriteLine($"Loaded {items.AsList().Count} items");

internal class Item
{
    public long ItemId { get; set; }
    public DateTime TransactionDate { get; set; }
    public int Quantity { get; set; }
}

[Giorgi]

Have you checked docs for parameterized statements?

[brendonparker]

No. But I’ll test an example using that approach to rule out Dapper.

[brendonparker]

Ok - so my problem was using a named parameter with @ instead of $. Thanks for turning me on to that.

May consider supporting parameters as @ as since that is more common, I think. (At least my experience).

Thanks for your work on this. It's awesome!

Final working snippet:

using Dapper;
using DuckDB.NET.Data;

await File.WriteAllTextAsync("./items.csv", """
    ItemId,TransactionDate,Quantity
    1001,2024-04-11T00:00:00,1
    1002,2024-04-11T00:00:00,1
    1003,2024-04-11T00:00:00,1
    1003,2024-04-10T00:00:00,1
    """);

using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

await connection.ExecuteAsync("CREATE TABLE items as SELECT * FROM read_csv('./items.csv')");

var items = await connection.QueryAsync<Item>(
    sql: "SELECT * FROM items WHERE TransactionDate > $TransactionDate",
    param: new { TransactionDate = new DateTime(2024, 04, 10) });

Console.WriteLine($"Loaded {items.AsList().Count} items");

internal class Item
{
    public long ItemId { get; set; }
    public DateTime TransactionDate { get; set; }
    public int Quantity { get; set; }
}