RepoDB: Bug: Inserting only specific columns does not work on SQLite

This problem is present on SQLite, all the tries work on MS SQL Server

Here and here are shown samples of how to execute .Insert/.InsertAsync specifying data for some columns only, using the fields parameter.

I tried to use this feature providing all the columns info but the Id (in order to let the db generate it) but it does not work: every time I specify the fields parameter I get the following exception:

“The non-identity primary field must be present during insert operation.”

If I provide the Id the .Insert/.InsertAsync doesn’t fail but the Id is not generated by the DB, it keeps the provided value, also accepting 0 as a valid Id.

The only version that correctly lets the DB generate the Id is the hard-coded INSERT INTO <table> (<columns>) VALUES (<values>), which also returns the correct generated Id.

This is my sample:

  • RepoDb (1.12.0-beta3)
  • RepoDb.SqLite (1.1.0-beta1)

Just copy this code in the Program.cs of a .NET Core 3.1 Console App

Source Code
using System;
using System.Data.SQLite;
using System.Threading.Tasks;
using RepoDb;

namespace RPT.UI.WinConsole2
{
	static class Program
	{
		static async Task Main(string[] args)
		{
			SqLiteBootstrap.Initialize();

			var conn = new SQLiteConnection(@"Data Source=:memory:;Version=3;");
			await conn.OpenAsync();
			await conn.ExecuteNonQueryAsync(CreateDatabaseScripts.EnsurePersonTable);

			var person = new Person { Name = "Cal", Surname = "Crutchlow" };
			var addedFields = Field.Parse<Person>(x => new
			{
				x.Name,
				x.Surname
			});
			var insertPersonSql = $"insert into [{nameof(Person)}] " +
				$"(" +
				$"  [{nameof(Person.Name)}]" +
				$", [{nameof(Person.Surname)}]" +
				$") values (" +
				$"  '{person.Name}'" +
				$", '{person.Surname}'" +
				$")";

			person.Name += "_2";
			uint id;
			ushort i = 1;

			var affectedRows = await conn.ExecuteNonQueryAsync(insertPersonSql).TryCatch(i++);
			// works, inserts correct data, db generates Id

			var dynamicPerson_no_Id = new { Name = "Cal", Surname = "Crutchlow" };
			id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_no_Id).TryCatch(i++);
			// {"The non-identity primary field must be present during insert operation."}

			var dynamicPerson_with_Id = new { Id = 13, Name = "Jack", Surname = "Brown" };
			id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_with_Id).TryCatch(i++);
			// works, inserts the Id into the dynamic, returns correct Id, but it is the provided Id, it is not generated by the DB

			dynamicPerson_with_Id = new { Id = 17, Name = "Bruce", Surname = "Wayne" };
			id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_with_Id, fields: addedFields).TryCatch(i++);
			// {"The non-identity primary field must be present during insert operation."}

			person.Id = 0;
			id = await conn.InsertAsync<Person, uint>(nameof(Person), entity: person).TryCatch(i++);
			// inserts Id = 0, returns correct inserted Id

			person.Id = 37;
			id = await conn.InsertAsync<Person, uint>(entity: person).TryCatch(i++);
			// inserts id = 37, returns correct inserted Id

			id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_no_Id, fields: addedFields).TryCatch(i++);
			// {"The non-identity primary field must be present during insert operation."}

			id = await conn.InsertAsync<uint>(nameof(Person), entity: dynamicPerson_no_Id, fields: addedFields).TryCatch(i++);
			// {"The non-identity primary field must be present during insert operation."}

			id = await conn.InsertAsync<Person, uint>(nameof(Person), entity: person, fields: addedFields).TryCatch(i++);
			// {"The non-identity primary field must be present during insert operation."}

			id = await conn.InsertAsync<Person, uint>(person, fields: addedFields).TryCatch(i++);
			// {"The non-identity primary field must be present during insert operation."}

			Console.ReadLine();
		}
	}

	public class Person
	{
		public uint Id { get; set; }
		public string Name { get; set; }
		public string Surname { get; set; }
	}

	public static class CreateDatabaseScripts
	{
		public static string EnsurePersonTable = $"create table if not exists [{nameof(Person)}] (" +
			string.Join(",",
				$"[{nameof(Person.Id)}] integer primary key",
				$"[{nameof(Person.Name)}] text not null",
				$"[{nameof(Person.Surname)}] text not null") +
			$");";
	}

	public static class TaskMixins
	{
		public static async Task TryCatch(this Task task, ushort i)
		{
			Console.WriteLine($"#{i++}");

			try
			{
				await task;
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
			}
			finally
			{
				Console.WriteLine("-----------------------");
			}
		}

		public static async Task<T> TryCatch<T>(this Task<T> task, ushort i)
		{
			Console.WriteLine($"#{i++}");

			try
			{
				return await task;
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
			}
			finally
			{
				Console.WriteLine("-----------------------");
			}

			return default;
		}
	}
}

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 1
  • Comments: 16 (11 by maintainers)

Commits related to this issue

Most upvoted comments

It seems not properly parsed. I will issue you a new beta release for this. When do you need the fix?

@snalesso - Cool, never thought the item 3 in the link you provided. Thank you for leading us there. Then this as a bug!

The IDbHelper object of RepoDb.SqLite is using the AUTOINCREMENT keyword as an identification for the identity, which I think we can simply be replaced or modified by adding an additional ‘OR’ condition for the PRIMARY KEY / INTEGER. See this code line.

Anyway, I will fix this on the next upcoming release. But if you are adventurous enough, you can advancely test it by simply injecting your own DbHelper.

Follow these steps:

  1. Copy the entire class from here and place it on your project solution. Maybe name it CustomSqLiteDbHelper.
  2. Replace the AUTOINCREMENT with PRIMARY KEY. Note: Ignore the data type for now, just for your own use-case.
  3. Then, simply inject your customized database helper class like below. Ensure to call it after the RepoDb.SqLiteBootstrap.Initialize().
DbHelperMapper.Add(typeof(SQLiteConnection), new CustomSqLiteDbHelper(), true);