0

I have the following situation:

  • A postgresql 15 database with a table with a `timestamp with time zone' as part of the primary key.
  • A C# .NET Core 7 application using:
    • Microsoft.EntityFrameworkCore (7.0.4)
    • Microsoft.EntityFrameworkCore.Design (7.0.4)
    • Npgsql.EntityFrameworkCore.PostgreSQL (7.0.3)
    • Npgsql.EntityFrameworkCore.PostgreSQL.Design (1.1.0)

The table I am working with has 3 columns:

 table_name | column_name |        data_type
------------+-------------+--------------------------
 MyTable    | SiteId      | bigint
 MyTable    | Timestamp   | timestamp with time zone
 MyTable    | Energy      | real

SiteId and Timestamp are (together) the primary key for the table.
Timestamps are stored in UTC.

I created a record with the following information:

 SiteId |       Timestamp        | Energy
--------+------------------------+--------
 884524 | 2023-03-12 12:13:14+00 |      7

I want to find a record by SiteId and Timestamp and update the Energy value.

I get errors on the update. I brought the 'problem' down to the following code:

var timestampUtc = new DateTime(2023, 3, 12, 12, 13, 14, DateTimeKind.Utc);
Expression<Func<MyTable, bool>> predicate =
    s => s.SiteId == 884524 &&
         s.Timestamp == timestampUtc;
var entity = context.MyTable.Where(predicate).SingleOrDefault();
entity.Energy = 15;
context.MyTable.Update(entity);
context.SaveChanges();

The SaveChanges() call throws an exception. On the console the following info and fail information is printed:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@p1='884524', @p2='2023-03-12T13:13:14.0000000+01:00' (DbType = DateTime), @p0='15' (Nullable = true)], CommandType='Text', CommandTimeout='30']
      UPDATE "MyTable" SET "Energy" = @p0
      WHERE "SiteId" = @p1 AND "Timestamp" = @p2;
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'MyHome.Models.MyHomeContext'.
      Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

The info line shows that the local time is used in the update query and not the original UTC time.

When I look with the debugger, after the var entity = statement I see that in entity the following data is present.

entity.Timestamp      {2023-03-12 13:13:14} System.DateTime
entity.Timestamp.Kind Local                 System.DateTimeKind

The timestamp is 'automatically' converted from UTC to local time. When the updated is posted, it looks in the database for the local time, not the 'original' UTC time.

What do I need to do to make updates work?

jokr
  • 73
  • 1
  • 9
  • Have you validated, that `entity` is not null? – Marco Mar 16 '23 at 13:35
  • Yes @Marco, When I look with the debugger, after the `var entity = ` statement I see that in entity the following data is present. `entity.Timestamp {2023-03-12 13:13:14} System.DateTime entity.Timestamp.Kind Local System.DateTimeKind` The timestamp is 'automatically' converted from UTC to local time. When the updated is posted, it looks in the database for the local time, not the 'original' UTC time. – jokr Mar 16 '23 at 14:58

1 Answers1

0

I think I found the source of the problem. There is/was a line in the code:

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

This changes the behavior of dealing with timestamp with time zone fields. Also the behavior seems to be different, depending on the Postgresql server version.

jokr
  • 73
  • 1
  • 9