Issue with .net core 7.0, Npgsql.EntityFrameworkCore.PostgreSQL: 7.0, PSQL 13.3

[RtypeStudios]

Hi!

I recently updated to .net core 7 and the latest npgsql library and have noticed an error when clearing the cache:

42809: cache.updatecacheitemformat(SchemaName => text, TableName => text, DistCacheId => text, UtcNow => timestamp with time zone) is not a procedure

After debugging the error from postgres. The issue appears to be that the cache clearing proceedure needs to be called with "Select" rather than "Call"

This fails (which is what is being generated): CALL cache.UpdateCacheItemFormat("SchemaName" := 'cache', "TableName" := 'cache', "DistCacheId" := 'GetRequest_XSNMW23adNCaUS', "UtcNow" := '20/11/2022 6:49:05 AM +00:00')

This works: SELECT cache.UpdateCacheItemFormat("SchemaName" := 'cache', "TableName" := 'cache', "DistCacheId" := 'GetRequest_XSNMW23adNCaUS', "UtcNow" := '20/11/2022 6:49:05 AM +00:00')

If I run the call command I get this from PSQL:

Am I doing something wrong or is this a compatibility issue with .net core 7?

Versions Being Used: .Net 7.0 Npgsql.EntityFrameworkCore.PostgreSQL: 7.0 Community.Microsoft.Extensions.Caching.PostgreSql: 3.1.2

[RtypeStudios]

Further research indicates that it is an issue caused by a change to how NpgSql wotks in version 7.0:

From here: https://www.npgsql.org/doc/basic-usage.html#stored-functions-and-procedures

This is a fairly massive breaking change by them and there is a flag to switch back to the old behaviour:

AppContext.SetSwitch("Npgsql.EnableStoredProcedureCompatMode", true);

As per here: https://www.npgsql.org/doc/release-notes/7.0.html#commandtype_storedprocedure

Would you like me to submit a PR to fix this?

[leonibr]

Go ahead please

[leonibr]

thank you @f1nzer , already merged is now version 4.0.1

[RtypeStudios]

thank you @f1nzer, apologies for not getting to it myself. Busy month.