C#•5mo ago

✅ EF does not put datetimes properly to the datebase

I am writing a backend app in asp.net core. This is my service code :

c#
public virtual async Task<Note> CreateNote(Note note, string userId)
    {
        note.CreatedAt = new DateTime();
        note.UpdatedAt = new DateTime();
        BeforeSave(note, userId);
        await db.Notes.AddAsync(note);
        await db.SaveChangesAsync();
        return note;
    }

c#
public virtual async Task<Note> CreateNote(Note note, string userId)
    {
        note.CreatedAt = new DateTime();
        note.UpdatedAt = new DateTime();
        BeforeSave(note, userId);
        await db.Notes.AddAsync(note);
        await db.SaveChangesAsync();
        return note;
    }

But in the database they are saved as infinity:

28 Replies

Angius•5mo ago

1. Don't use AddAsync 2. Use a DTO, don't receive or send database models directly into/from an API 3. What is BeforeSave()? Also, use DateTime.Now

Omid.N•5mo ago

using DateTime.Now gives error

MODiX•5mo ago

Angius

REPL Result: Success

new {
    New = new DateTime(),
    Now = DateTime.Now
}

new {
    New = new DateTime(),
    Now = DateTime.Now
}

Result: <>f__AnonymousType0#1<DateTime, DateTime>

{
  "new": "0001-01-01T00:00:00",
  "now": "2024-03-05T18:05:31.9018999+00:00"
}

{
  "new": "0001-01-01T00:00:00",
  "now": "2024-03-05T18:05:31.9018999+00:00"
}

Compile: 254.013ms | Execution: 53.787ms | React with ❌ to remove this embed.

Angius•5mo ago

What is the error?

Omid.N•5mo ago

Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported.

Angius•5mo ago

What is your Note model? Does it actually use datetime?

Omid.N•5mo ago

c#
public class Note
{
    public int ID { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }

    public NoteType NoteType { get; set; }

    [Omitted]
    public User? User { get; set; }
    public string? UserId { get; set; }
    public List<Tag>? Tags { get; set; }

    // shows how many times the note has been viewed
    public int? ViewCount { get; set; }
}

c#
public class Note
{
    public int ID { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }

    public NoteType NoteType { get; set; }

    [Omitted]
    public User? User { get; set; }
    public string? UserId { get; set; }
    public List<Tag>? Tags { get; set; }

    // shows how many times the note has been viewed
    public int? ViewCount { get; set; }
}

Angius•5mo ago

Do you use code-first or db-first?

Omid.N•5mo ago

what does it mean?

Angius•5mo ago

Do you create data models and use migrations to modify the database? Or do you modify the database and scaffold the data models?

Omid.N•5mo ago

i think i use codefirst

Angius•5mo ago

Aight Try DateTime.UtcNow

Omid.N•5mo ago

also why should i not use AddAsync

Angius•5mo ago

Because it's only useful for some very specific edge cases Asynchronous code is used when any sort of IO happens

Omid.N•5mo ago

i only use the code above

Angius•5mo ago

In case of the database, SaveChangesAsync() does the IO .Add() only impacts the change tracker So it doesn't need to be async

Omid.N•5mo ago

okay

Executed DbCommand (31ms) [Parameters=[@p0='?', @p1='?' (DbType = DateTime), @p2='?' (DbType = Int32), @p3='?', @p4='?' (DbType = DateTime), @p5='?', @p6='?' (DbType = Int32)], CommandType='Text', Co
mmandTimeout='30']
      INSERT INTO "Notes" ("Content", "CreatedAt", "NoteType", "Title", "UpdatedAt", "UserId", "ViewCount")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
      RETURNING "ID";

Executed DbCommand (31ms) [Parameters=[@p0='?', @p1='?' (DbType = DateTime), @p2='?' (DbType = Int32), @p3='?', @p4='?' (DbType = DateTime), @p5='?', @p6='?' (DbType = Int32)], CommandType='Text', Co
mmandTimeout='30']
      INSERT INTO "Notes" ("Content", "CreatedAt", "NoteType", "Title", "UpdatedAt", "UserId", "ViewCount")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
      RETURNING "ID";

Angius•5mo ago

Omid.N•5mo ago

this is the command that is executed for inserting

Angius•5mo ago

Yeah, and?

Omid.N•5mo ago

should i do anything so that it converts datetime to database type?

Angius•5mo ago

No, that would defeat the purpose of an ORM like EF

Omid.N•5mo ago

yeah

Angius•5mo ago

Did you try DateTime.UtcNow like I suggested?

Omid.N•5mo ago

no i did not see it. let me try it

Keswiik•5mo ago

DateTimeOffset.Now should also work according to this stack thread with the same issue: https://stackoverflow.com/questions/73693917/net-postgres-ef-core-cannot-write-datetime-with-kind-local-to-postgresql-type

Stack Overflow

.net postgres EF core Cannot write DateTime with Kind=Local to Post...

I have a .Net6 console app that uses nuget packages: "Microsoft.EntityFrameworkCore, Version 6.0.8" "Npgsql.EntityFrameworkCore.PostgreSQL, Version 6.0.6" "Npgsql, Version...

Omid.N•5mo ago

Yeah it worked ! thanks

Angius•5mo ago

Yeah, it has to do with datetime handling they improved in some version of NpgSQL

Gaming

Programming

✅ EF does not put datetimes properly to the datebase