CODEWITHSUNDEEP
c#postgresqlentity-framework-corenpgsql
Luciano Trez
Luciano Trez

Reputation: 159

Npgsql EF 6: Timestamp fails when using ExecuteSqlInterpolatedAsync

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).

Upvotes: 1

Views: 9214

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16722

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.

Upvotes: 3

Related Questions