Npgsql: No mapping to a relational type can be found for the CLR type 'NpgsqlDate'

Question

In my ASP.NET Core v2.1 app I have the following:

// beginDate and endDate are .NET DateTime types
var beginDateParam = new NpgsqlDate(beginDate);
var endDateParam = new NpgsqlDate(endDate);

var result = await _reportDb.ReportRows
    .FromSql(
        "SELECT * FROM \"fnReport\"(@p0, @p1);",
        new object[] { beginDateParam, endDateParam
    })
    .AsNoTracking().ToListAsync();

fnReport function's parameters are of PostgreSQL date type. Before, Npgsql v2.1 the above code used to work. Now, after upgrade it throws an error: "No mapping to a relational type can be found for the CLR type 'NpgsqlDate'".

Without NpgsqlDate, the error will be: " function fnReport(timestamp without time zone, timestamp without time zone) does not exist".

I know that recommended approach is to use NodaTime library. But, using NodaTime library requires a lot of code change (see this issue).

Since, my datetime requirements are quite basic, I can change fnReport's argument types to timestamp data type, and it will work.

However, I have an entity with the following property:

[Column(TypeName = "date")]
[DataType(DataType.Date)]
public DateTime EntryDate { get; set; }

And, for this entity, CRUD operations work fine. My question is, why Npgsql/EF Core is able to do that, but fails to execute a function with date type parameters?

Shay Rojansky Shay Rojansky · Accepted Answer · 2018-12-30T10:40:39

The provider-specific date/time types, such as NpgsqlDate, currently aren't mapped by the Entity Framework Core provider - see https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/467. While this is an open issue in the backlog, there's probably not much chance it will be done, since these types don't have much use anymore (we're even considering dropping them altogether - https://github.com/npgsql/npgsql/issues/2009).

As you've noted, using the NodaTime types is recommended. However, even if you don't want to use NodaTime, the built-in .NET types work well and are fully mapped - the only reason to use NpgsqlDateTime and the related types is if you're dealing with date/time values that are out of the range (or precision) of the built-in .NET types (see the PostgreSQL docs for more details). Almost all regular applications should be able to use .NET DateTime just fine.

Npgsql: No mapping to a relational type can be found for the CLR type 'NpgsqlDate'

1 Answers