Executing PostgreSQL stored procedure using EF Core DbContext.Database.ExecuteSqlRaw

Question

I tried to execute the following stored procedure:

CREATE OR REPLACE PROCEDURE gselogwebsite_add
(
    _logUserId INT,
    _logSourceId INT,
    _logCat INT,
    _logLevel INT,
    _logType INT,
    _logText TEXT,
    _logDateTimeUTC TIMESTAMP WITHOUT TIME ZONE,
    INOUT _logId BIGINT
)
LANGUAGE plpgsql AS
$BODY$
BEGIN
    ...
    ... 
    ...
END;
$BODY$;

I tried to call in my ASP.NET Core 8.0 MVC using EF Core 8.0.5 with the following code:

context.Database.ExecuteSqlRaw("CALL gselogwebsite_add(@_logUserId, @_logSourceId, @_logCat, @_logLevel, @_logType, @_logText, @_logDateTimeUTC, @_logId)", 
    new NpgsqlParameter("_logUserId", logUserId),
    new NpgsqlParameter("_logSourceId", logSourceId),
    new NpgsqlParameter("_logCat", logCat),
    new NpgsqlParameter("_logLevel", logLevel),
    new NpgsqlParameter("_logType", logType),
    new NpgsqlParameter("_logText", logText),
    new NpgsqlParameter("_logDateTimeUTC", logDateTimeUTC),
    new NpgsqlParameter("_logId", DBNull.Value) { DbType = DbType.Int64, Direction = ParameterDirection.InputOutput }
);

But I am getting this error:

Npgsql.PostgresException: '42883: procedure gselogwebsite_add(integer, integer, integer, integer, integer, text, timestamp with time zone, bigint) does not exist

I checked the connection everything looks ok. I checked the data types, all look ok. I check the database using DBeaver and I can see the function there gselogwebsite_add(in int4, in int4, in int4, in int4, in int4, in text, in timestamp, inout int8).

But for some reason, I can't execute this stored procedure.

Any ideas?

Thanks :)

Answer 1

For Ef Core 6+, Npgsql will translate DateTime with Kind DateTimeKind.Utc to TIMESTAMP WIH TIME ZONE, DateTimeKind.Local and DateTimeKind.Unspecified to TIMESTAMP WITHOUT TIME ZONE. Please ensure logDateTimeUTC DateTime.Kind is not UTC. If you ensure its kind is not UTC, well, as far as I know, it may be an internal issue of EF Core and Npgsql please let me know to deed dive into your problem.