C#•10mo 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•10mo 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.NOP•10mo ago

using DateTime.Now gives error

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

What is the error?

Omid.NOP•10mo ago

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

Angius•10mo ago

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

Omid.NOP•10mo 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•10mo ago

Do you use code-first or db-first?

Omid.NOP•10mo ago

what does it mean?

Angius•10mo 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.NOP•10mo ago

i think i use codefirst

Angius•10mo ago

Aight Try DateTime.UtcNow

Omid.NOP•10mo ago

also why should i not use AddAsync

Angius•10mo ago

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

Omid.NOP•10mo ago

i only use the code above

Angius•10mo 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.NOP•10mo 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•10mo ago

Omid.NOP•10mo ago

this is the command that is executed for inserting

Angius•10mo ago

Yeah, and?

Omid.NOP•10mo ago

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

Angius•10mo ago

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

Omid.NOP•10mo ago

yeah

Angius•10mo ago

Did you try DateTime.UtcNow like I suggested?

Omid.NOP•10mo ago

no i did not see it. let me try it

Keswiik•10mo 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.NOP•10mo ago

Yeah it worked ! thanks

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