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

JeffMercado

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

There is a property on the Connection

JeffMercado

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

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.

JeffMercado

So, coming back to this again as I've run into this problem again. I did find out how it was supposed to work.

EF is looking for specific attributes to be used for certain mappings and I wasn't using the right ones.

I needed to use the System.Diagnostics.CodeAnalysis.ColumnAttribute attribute to specify the expected database type name. I would have to use DATETIME in this case (DATETIME2 will not work). reference

I should have set it as:

    public class ArchivedVideo
    {
        public long ArchivedVideoId { get; set; }
        public long Revision { get; set; }
        [Column(TypeName="DATETIME")]
        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; }
    }

Then having those options to map dates and booleans in the options will fix up the mapping.

---

My current use case was creating/analyzing some log databases I wanted to add in LINQPad, and I was able to set it up from scratch with the built in connections. Not the most straightforward, but maybe something that could be better streamlined in the future?

1. Created DB file
2. Added connection (no tables found)
3. Create new script using the connection
4. Defines the entity to map (attributes and all)
5. Add a placeholder DbSet property at the class level
6. Run Database.EnsureCreated()
7. Refresh the connection (should find the table as configured)

JoeAlbahari

Is there any reason that you don't use LINQPad's dynamic EF Core driver (with the Map DateTime option enabled), and then execute a SQL script to create the table?

CREATE TABLE ArchivedVideo (
    ArchivedVideoId   INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
    Revision          INTEGER  NOT NULL,
    DateRetrievedUtc  DATETIME NOT NULL,
    VideoId           TEXT     NOT NULL,
    Title             TEXT     NOT NULL,
    Description       TEXT     NULL,
    ReleaseDate       TEXT     NULL,
    UploadDate        TEXT     NOT NULL,
    Duration          INTEGER  NOT NULL,
    VideoType         INTEGER  NOT NULL
);

JeffMercado

I had the code already in the same script, when I was processing the log files directly, I wanted to make a script to put them into a database for easier processing. I wanted to use the code first features mostly.

JoeAlbahari

Understood.

I think SQLite was designed specifically to inflict pain every time you need a DateTime