NET8 Npgsql.PostgresException: '42883: function pg_catalog.timezone(unknown, interval) does not exist

[gieniowski]

writing query that contain such condition:

var queryable = _context.SomeTable
	.Where(x => x.Date > DateOnly.FromDateTime(DateTime.UtcNow) && x.Date <= DateOnly.FromDateTime(DateTime.UtcNow.AddDays(14)))

which is translated to this where statement:

FROM some_table AS f
WHERE f.date > CAST(now() AT TIME ZONE 'UTC' AS date) AND f.date <= CAST(now() + INTERVAL '14 days' AT TIME ZONE 'UTC' AS date)

causes this error:
Npgsql.PostgresException: '42883: function pg_catalog.timezone(unknown, interval) does not exist

Not sure if it changes anyting, but I am using EFCore.NamingConventions. I have tried both versions 7.0.2 and 8.0.0-rc.2 and have the same error.

[roji]

@gieniowski I'm not actually seeing any timezone function call in the above quoted SQL.

In general, it's always best to post an actual runnable, minimal code sample, rather than a query snippet as above - can you please do that? That would allow me to reproduce the issue and investigate.

[gieniowski]

@roji Sorry, I wasn't able to prepare nice snippet when creating the issuse... Now you can find it below:

using Microsoft.EntityFrameworkCore;

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

await ctx.Blogs
    .Where(x => x.Date <= DateOnly.FromDateTime(DateTime.UtcNow.AddDays(14)))
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql(@"Host=localhost;Port=5432;Database=test_db;Username=root;Password=TODO");
}

public class Blog
{
    public int Id { get; set; }
    public DateOnly Date { get; set; }
}

<Project Sdk="Microsoft.NET.Sdk">

	<PropertyGroup>
		<OutputType>Exe</OutputType>
		<TargetFramework>net8.0</TargetFramework>
		<ImplicitUsings>enable</ImplicitUsings>
		<Nullable>enable</Nullable>
	</PropertyGroup>

	<ItemGroup>
		<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.0" />
	</ItemGroup>

</Project>

It works when you change the package version to <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.11" />

[roji]

Thanks for the code sample - I can now reproduce it and see the regression. This is a parentheses/precedence bug, with 7.0 producing:

SELECT b."Id", b."Date"
FROM "Blogs" AS b
WHERE b."Date" <= CAST(((now() + INTERVAL '14 days') AT TIME ZONE 'UTC') AS date)

... and 8.0 producing:

SELECT b."Id", b."Date"
FROM "Blogs" AS b
WHERE b."Date" <= CAST(now() + INTERVAL '14 days' AT TIME ZONE 'UTC' AS date)

I'll fix this for 8.0.1.

[roji]

Docs discussion on pg-hackers

[KonstantinBadanin]

8.0.2 Still not fixed
case:

LINQ2SQL
table.Where(x => x.StartDate.AddMinutes(fixTimeMinutes).Year == year)

Generates
SELECT *
FROM dbo."Table" AS t
WHERE date_part('year', t."StartDate" + CAST(@__fixTimeMinutes_0::text || ' mins' AS interval) AT TIME ZONE 'UTC')::int = @__year_1

instead of

SELECT *
FROM dbo."Table" AS t
WHERE date_part('year', ( t."StartDate" + CAST(@__fixTimeMinutes_0::text || ' mins' AS interval) ) AT TIME ZONE 'UTC')::int = @__year_1

[NinoFloris]

@KonstantinBadanin This has to be fixed in Npgsql.EntityFrameworkCore.PostgreSQL which only has the 8.0.0 release for now.

[roji]

Yeah, I need to release an EFCore.PG patch version (and deal with some other bugs before I do). I'll try to devote some time to this in the coming week or two.

[jhartmann123]

@roji It looks like this wasn't fixed in 8.0.2. I still get this error, and if I didn't loose all my git knowledge over night, that fix was only committed to main, first commit after bumping it to 9.0.0-preview.1, without a cherry pick to the v8 branch

[roji]

Thanks @jhartmann123 - you're right, I seem to have forgotten to backport the fix... I've done this now for 8.0.3 - sorry for the additional wait....

[msvprogs]

@roji Hello, have you uploaded the 8.0.3 version to NuGet repository? It seems like the last available 8.x version is 8.0.2. Thanks!

[roji]

Not yet. There's a lot going on at the moment, I'll try to do that in the coming couple of weeks.

[gieniowski]

Please try to do this before May 14 when EF Core 7 looses support.

[msvprogs]

At the moment, prerelease version of 8.0.3 looks like to have been deployed in the private repo.
It can be downloaded with the following nuget.config:

<configuration>
	<packageSources>
		<clear />    
		<add key="nuget.org" value="https://api.nuget.org/v3/index.json" protocolVersion="3" />
		<add key="NpgsqlMyGet" value="https://www.myget.org/F/npgsql/api/v3/index.json" />
	</packageSources>
	<packageSourceMapping>
	  <packageSource key="NpgsqlMyGet">
		<package pattern="Npgsql.EntityFrameworkCore.PostgreSQL" />
	  </packageSource>
	  <packageSource key="nuget.org">
		<package pattern="*" />
	  </packageSource>
	</packageSourceMapping>
</configuration>