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
- Fixes for #568. — committed to mikependon/RepoDB by mikependon 4 years ago
- Merge pull request #572 from mikependon/repodb-adhocs Fixes for #568. — committed to mikependon/RepoDB by mikependon 4 years ago
- Additional fixes and Integration Tests updates. #568 — committed to mikependon/RepoDB by mikependon 4 years ago
- Merge pull request #576 from mikependon/repodb-adhocs Additional fixes and Integration Tests updates. #568 — committed to mikependon/RepoDB by mikependon 4 years ago
- #568 - Added the Integration Tests for reusable identity value. — committed to mikependon/RepoDB by mikependon 4 years ago
- Merge pull request #579 from mikependon/repodb-adhocs #568 - Added the Integration Tests for reusable identity value. — committed to mikependon/RepoDB by mikependon 4 years ago
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
IDbHelperobject of RepoDb.SqLite is using theAUTOINCREMENTkeyword as an identification for the identity, which I think we can simply be replaced or modified by adding an additional ‘OR’ condition for thePRIMARY 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:
CustomSqLiteDbHelper.AUTOINCREMENTwithPRIMARY KEY. Note: Ignore the data type for now, just for your own use-case.RepoDb.SqLiteBootstrap.Initialize().