Npgsql EF 6: Timestamp fails when using ExecuteSqlInterpolatedAsync-CodePudding

After update to 6.0.3 I'm getting error in ExecuteSqlInterpolatedAsync. Well I know it's a breking change, but I review all my tables in database and all columns are "timestamp without time zone". And doing updates or inserts through the model/DbSet are ok.

With that in mind, take a look at this piece of code:

        await Database.ExecuteSqlInterpolatedAsync($@"update nfservico set 
                chavenfse = {chave},
                lotenfse = {cLote},
                lotenfse_dh = {dhRecbto},
                lotenfse_sit = {sit},
                lotenfse_mot = {mot}
                where id = {id}");

dhRecbto it's a DateTime parameter with no "kind" specified. Running that give the following error:

System.InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken can...

I can't understand the "timestamp with time zone" part, like I said before, the field's without time zone.

I had to do an ugly work around:

            var sql = new StringBuilder();
            sql.AppendLine("update nfservico set ");
            sql.AppendLine($"    chavenfse = '{chave}',");
            sql.AppendLine($"    lotenfse = '{cLote}',");
            sql.AppendLine($"    lotenfse_dh = '{dhRecbto:yyyy-MM-dd HH:mm:ss}',");
            sql.AppendLine($"    lotenfse_sit = {sit},");
            sql.AppendLine($"    lotenfse_mot = '{mot}'");
            sql.AppendLine($"    where id = {id}");
            await Database.ExecuteSqlRawAsync(sql.ToString());

Anyone with the same issue? And no, I don't want to use "EnableLegacyTimestampBehavior" switch, want to do it right (at least like found at release notes from Npgsql 6).

CodePudding user response：

This is a limitation in EF Core's raw SQL support. The EF type mapping - which manages the PG type of the parameter sent (timestamp vs. timestamptz) - is determined only by the CLR type of the parameter (DateTime), without looking at its contents (i.e. the Kind). And the default EF mapping for DateTime is timestamptz, not timestamp; hence the error. Note that this has nothing to do with your actual database column type: it's purely a client-side question.

Fortunately, EF's raw SQL APIs allow passing in a DbParameter directly, allowing you to specify exactly which PostgreSQL type you want:

var p = new NpgsqlParameter { Value = new DateTime(2020, 1, 1, 12, 0, 0), NpgsqlDbType = NpgsqlDbType.Timestamp };
_ = ctx.Database.ExecuteSqlInterpolatedAsync($"SELECT {p}");

This is the safe, correct way to pass a parameter where the CLR type (DateTime) has a default PG type (timestamptz) which isn't what you want (you want timestamp). Your workaround above is likely to be wrong in some way, and may introduce an unwanted timezone conversion.