Dapper, Insert record in SQLite table

Question

I am trying to insert a record in a SQLite table, but the first fields stays NULL.

First I created a table in Ssqlite:

CREATE TABLE Tests(id INTEGER, nr INTEGER);

Then I wrote this code (Target Framework: .NET 6.0):

using Dapper.Contrib.Extensions;
using System.Data.SQLite;


namespace ConsoleApp48
{
    public class Program
    {
        static void Main(string[] args)
        {
            SQLiteConnection sqlite_conn;
            sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True; ");
            sqlite_conn.Open();

            Test t;
            t = new Test() { id = 1, nr = 1 };
            sqlite_conn.Insert<Test>(t);
            t = new Test() { id = 5, nr = 5 };
            sqlite_conn.Insert<Test>(t);

            sqlite_conn.Close();
        }
    }

    public class Test
    {
        public int id { get; set; }
        public int nr { get; set; }
    }
}

After this the contents of my database is:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Test(id INTEGER, nr INTEGER);
CREATE TABLE Tests(id INTEGER, nr INTEGER);
INSERT INTO Tests VALUES(NULL,1);
INSERT INTO Tests VALUES(NULL,5);
COMMIT;
sqlite>

Table Test also exists, but that is a minor issue. I thought table should have the same name as the class, so I did create the table as Test, but on failure I created table Tests with same structure.

The real question is the two NULL values in the created records. Why is the field id not updated with the correct value (1 or 5) ?

I tried finding if I am missing something obvious, but failing to find it, I created this minimal reproduceable example.

Info on the usings:

• Dapper.Contrib: version 2.0.78
• System.Data.Sqlite.Core: version 1.0.116

Answer 1

Adding a primary key solved the problem:

CREATE TABLE Tests(id INTEGER PRIMARY KEY NOT NULL, nr INTEGER);

(Which reminds me I should always do define such a thing, even when I am having only a small number of records..... 😉