How can I have LINQPad detect a SQLite column as a DateTime?

JeffMercado · November 2023

I'm trying to set up an app that will use EFCore 8 (code first) and SQLite for storage. My understanding is that SQLite doesn't have a DateTime type and uses text to store it. Fair enough.

However, I noticed that the included demo database (ChinookDemoDb.sqlite) is able to treat dates as such (e.g., Invoices table). When I added my database file as a connection, the date columns are being detected as strings.

vs

Are there special annotations that are being utilized to do this? What can I do to have it be detected as a DateTime?

My entity is defined and configured:

    public class ArchivedVideo
    {
        public long ArchivedVideoId { get; set; }
        public long Revision { get; set; }
        public DateTime DateRetrievedUtc { get; set; }
        public string VideoId { get; set; } = "";
        public string Title { get; set; } = "";
        public string? Description { get; set; }
        public DateTime? ReleaseDate { get; set; }
        public DateTime UploadDate  { get; set; }
        public TimeSpan Duration { get; set; }
        public VideoType VideoType { get; set; }
    }

            modelBuilder.Entity<ArchivedVideo>(e =>
            {
                e.ToTable(nameof(ArchivedVideo));
                e.HasKey(x => x.ArchivedVideoId);
                e.Property(x => x.ArchivedVideoId).ValueGeneratedOnAdd();
                e.Property(x => x.Revision);
                e.Property(x => x.DateRetrievedUtc);
                e.Property(x => x.VideoId);
                e.Property(x => x.Title);
                e.Property(x => x.Description);
                e.Property(x => x.ReleaseDate);
                e.Property(x => x.UploadDate);
                e.Property(x => x.Duration);
                e.Property(x => x.VideoType);
            });

sgmoore · November 2023

There is a property on the Connection

JeffMercado · November 2023

I do have those checked so perhaps there's something missing from the mappings. On database creation, I see the command being executed and the columns still have string types:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "ArchivedVideo" (
          "ArchivedVideoId" INTEGER NOT NULL CONSTRAINT "PK_ArchivedVideo" PRIMARY KEY AUTOINCREMENT,
          "Revision" INTEGER NOT NULL,
          "DateRetrievedUtc" TEXT NOT NULL,
          "VideoId" TEXT NOT NULL,
          "Title" TEXT NOT NULL,
          "Description" TEXT NULL,
          "ReleaseDate" TEXT NULL,
          "UploadDate" TEXT NOT NULL,
          "Duration" TEXT NOT NULL,
          "VideoType" INTEGER NOT NULL
      );

Explicitly setting the column types for the date fields as DATETIME does fix the issue however. I guess I was expecting that to be handled automatically.

            modelBuilder.Entity<ArchivedVideo>(e =>
            {
                e.ToTable(nameof(ArchivedVideo));
                e.HasKey(x => x.ArchivedVideoId);
                e.Property(x => x.ArchivedVideoId).ValueGeneratedOnAdd();
                e.Property(x => x.Revision);
                e.Property(x => x.DateRetrievedUtc).HasColumnType("DATETIME");
                e.Property(x => x.VideoId);
                e.Property(x => x.Title);
                e.Property(x => x.Description);
                e.Property(x => x.ReleaseDate).HasColumnType("DATETIME");
                e.Property(x => x.UploadDate).HasColumnType("DATETIME");
                e.Property(x => x.Duration);
                e.Property(x => x.VideoType);
            });

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "ArchivedVideo" (
          "ArchivedVideoId" INTEGER NOT NULL CONSTRAINT "PK_ArchivedVideo" PRIMARY KEY AUTOINCREMENT,
          "Revision" INTEGER NOT NULL,
          "DateRetrievedUtc" DATETIME NOT NULL,
          "VideoId" TEXT NOT NULL,
          "Title" TEXT NOT NULL,
          "Description" TEXT NULL,
          "ReleaseDate" DATETIME NULL,
          "UploadDate" DATETIME NOT NULL,
          "Duration" TEXT NOT NULL,
          "VideoType" INTEGER NOT NULL
      );

I'll have to remember to explicitly set the type for dates and booleans I suppose.

JoeAlbahari · December 2023

You could lodge a feature request on the EF Core repository - it seems like a reasonable suggestion. Technically, "DATETIME" and "BOOLEAN" aren't real data types in SQLite, but you could argue that this labeling is (or should be) a defacto standard annotation.

How can I have LINQPad detect a SQLite column as a DateTime?

Comments