-1

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
Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • if you change the column name from _id_ to something else then it works. Not sure why, still looking for the reason (are you using _dapper-contrib_ right?) – Steve Jul 14 '22 at 16:09
  • A column named `id` may have special semantics to it. Not sure, though, how usage of System.Data.SQLite in conjunction with Dapper may lead to the observed behavior. – Fildor Jul 14 '22 at 16:10
  • 1
    It is something coming from the extension because a bare bone dapper _cnn.Execute("INSERT INTO (id, nr) values (@id, @nr)", t);_ works as expected – Steve Jul 14 '22 at 16:15
  • @steve: "(are you using dapper-contrib right?)" How is right? , But as Fildor said `id` does something special... – Luuk Jul 14 '22 at 16:25
  • I can confirm that changing `id` to (i.e.) `i` solves the problem. – Luuk Jul 14 '22 at 16:28
  • I mean this extension: https://github.com/DapperLib/Dapper.Contrib but I see now you are using this one: https://github.com/tmsmith/Dapper-Extensions – Steve Jul 14 '22 at 16:28
  • I do have version 2.0.78, so I am using: https://github.com/DapperLib/Dapper.Contrib ? – Luuk Jul 14 '22 at 16:35
  • 2
    There, the source code doesn't lie. The KeyPropertiesCache method is used inside the Insert code to find all the computed columns and the key columns. In that code there is a check for _id_ and if they find a column with that name without key attribute they add it to a collection of columns that are excluded from the building of the insert query – Steve Jul 14 '22 at 16:49
  • https://github.com/DapperLib/Dapper.Contrib/blob/cf24f6bdc577b1e071c3764ddfb2cf3382531405/src/Dapper.Contrib/SqlMapperExtensions.cs#L100 – Steve Jul 14 '22 at 16:50
  • I added an issue on Githib (see: https://github.com/DapperLib/Dapper.Contrib/issues/140) maybe some author can explain... ) – Luuk Jul 14 '22 at 17:53

1 Answers1

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.....

Luuk
  • 12,245
  • 5
  • 22
  • 33