SQLite - ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

[Arslan007]

I am using 3.2.5 in UWP app with SQLite. I cannot upgrade to latest version due to .NET Standard 2.1 is not fully supported to UWP so we cannot upgrade to EFCore 5.

I am getting this error on InsertOrUpdate statement.

SQLite Error 1: 'ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint'.

using (var localContext = new TechnicianContext(identityService))
{
    var bulkConfig = new BulkConfig { BatchSize = 5000, SetOutputIdentity = true };
    await localContext.BulkInsertOrUpdateAsync(items.ToList(), bulkConfig);
}

AFAIK there is no duplicate on items list for PKID and no unique constraint at all so I have no idea why its erroring out for this.

Let me know if you need any further info

[Arslan007]

@borisdj have you considered my question. Thanks?

[borisdj]

Took a look but not sure what causes the issue. Can you upgrade and try with 6.0, it's just published. If v6 does not supports UVW, then in source with latest v3, try make a test where your issue would be reproducible.

[Arslan007]

@borisdj I cannot upgrade to 6.0 due to UWP limitation as it doesn't support .NET Standard 2.1 or later. I tried with source code and its throwing error on this place

Let me know if you need more info

[borisdj]

Still not able to reproduce it. If you are already have reproducible environment try to isolate the issue and if you manage to find a fix you can make a PR.

[Arslan007]

@borisdj Is there anyway I can print the SQL Query log. I have enabled Enabled logging for EF Context but somehow all other queries are logged but nothing from this Source code. Just wanted to see what query is being executed since everything is fine till LoadSqliteValues method and throws error after first query execution

[borisdj]

The ON CONFLICT Clause is in SqlQueryBuilderSqlite in method InsertIntoTable which is called from GetSqliteCommand in Adapter. So it's more likely that exception is thrown there at command.Prepare(); or in Merge at command.ExecuteNonQuery. In debug you can check CommandText before execution and even copy it and run it manualy in some Query editor on Db.

AS for EF logging: https://eamonkeane.dev/3-ways-to-view-sql-generated-by-entity-framework-core-5/ Also DbCommandInterceptor could be used as well, there are examples in Tests.

[Gopichandar]

I can relate this to my issue. Its a bug where sqlite requires primary key for UPSERT. #648