DBFunctions.DiffHours issue with big Time differences

[danners]

Hi,

while porting an application from EF6 + MSSql to EF6 + npgsql, we noticed a failure in our db integration test:

In our C# code we have the following code snipped:

DbFunctions.DiffHours(x.EndedDate, DateTime.Now) > 2

this gets passed as a WHERE condition into a ef query to delete some db entries.

This results, in case of our integration test in a query that contains something similar to the following excerpt:

select extract(epoch from date_trunc('hour',LOCALTIMESTAMP) - date_trunc('hour', TIMESTAMP '1753-01-01 00:00:00'))::int4 / 3600 > 2

Note the cast to ::int4, which fails, since the difference is out of range for int4. Same test works for MSSQL, we didn't check the resulting query.

We are not sure: Is this an issue in the EF integration of npgsql or npgsql itself ?

NPGSQL version 3.2.7 EF6.NPGSQL 3.1.1

Any idea on how to avoid this?

[Emill]

Good catch, the cast to int4 before the division of 3600 makes the intermediate value out of range. The bug is in the Npgsql integration for EF here: https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/SqlGenerators/SqlBaseGenerator.cs#L1376.