C#•8mo 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•8mo 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•8mo ago

using DateTime.Now gives error

MODiX•8mo 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•8mo ago

What is the error?

Omid.N•8mo ago

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

Angius•8mo ago

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

Omid.N•8mo 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•8mo ago

Do you use code-first or db-first?

Omid.N•8mo ago

what does it mean?

Angius•8mo 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•8mo ago

i think i use codefirst

Angius•8mo ago

Aight Try DateTime.UtcNow

Omid.N•8mo ago

also why should i not use AddAsync

Angius•8mo ago

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

Omid.N•8mo ago

i only use the code above

Angius•8mo 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•8mo 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•8mo ago

Omid.N•8mo ago

this is the command that is executed for inserting

Angius•8mo ago

Yeah, and?

Omid.N•8mo ago

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

Angius•8mo ago

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

Omid.N•8mo ago

yeah

Angius•8mo ago

Did you try DateTime.UtcNow like I suggested?

Omid.N•8mo ago

no i did not see it. let me try it

Keswiik•8mo 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•8mo ago

Yeah it worked ! thanks

Angius•8mo 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