I have a SQLite database with the following table:

CREATE TABLE "Orders"
(
    "ID"          INTEGER NOT NULL,
    "Title"       TEXT NOT NULL,
    "Description" TEXT,
    "Price"       REAL NOT NULL,
    "WarehouseID" INTEGER NOT NULL,
    "Category"    TEXT,

    FOREIGN KEY("WarehouseID") 
         REFERENCES "Warehouses"("ID") 
             ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY("ID" AUTOINCREMENT),
    UNIQUE("Title", "Description", "Price", "WarehouseID", "Category") ON CONFLICT IGNORE
)

Using Microsoft.EntityFrameworkCore.Sqlite 9.0.2 I have a List<Order> orders that I add to the context like this:

public static void SaveOrders(IEnumerable<Order> orders)
{
    dbContext.Orders.AddRange(orders);
    dbContext.SaveChanges();
}

The above method is being called several times, being passed a List of orders with about 100 members each time.

There are a bunch of orders that need to be processed/saved to the database. Initially everything works fine and up to 1310 records everything is saved to the database with no hussle.

But then something weird happens.

I get a DbUpdateConcurrencyException (no inner exception) because EF Core expected to affect 1 row, but instead 0 were affected.

I know this can happen if EF Core attempts to insert a record that violates a UNIQUE ON CONFLICT IGNORE constraint since SQLite just ignores it and 0 affected rows are returned, however if I run the insert command by hand (I have EF Core logging enabled) it inserts just fine (a new record IS added).

This is the command EF Core logs as attempting to execute

  Executing DbCommand [Parameters=[@p0='Sales', @p1='', @p2='24.90', @p3='16', @p4='iPhone XS 15 extra features camera, sleeve' (Size = 43)], CommandType='Text', CommandTimeout='30']
  INSERT INTO "Orders" ("Category", "Description", "Price", "WarehouseID", "Title")
  VALUES (@p0, @p1, @p2, @p3, @p4)
  RETURNING "ID";

if I execute the following command:

INSERT INTO "Orders" ("Category", "Description", "Price", "WarehouseID", "Title")
  VALUES ('Sales', '', '24.90', '16', 'iPhone XS 15 extra features camera, sleeve')
  RETURNING "ID";

SQLite returns

Execution finished without errors.
Result: 0 rows returned in 15ms

the record isn't inserted, no matter how many times I run the command.

The command I was referring to running that succeeds is the following:

INSERT INTO "Orders" ("Category", "Description", "Price", "WarehouseID", "Title")
  VALUES ('Sales', '', '24.90', '16', 'iPhone XS 15 extra features camera, sleeve')

i.e. without the RETURNING "ID" part. THIS command succeeds and inserts the record. Subsequent executions of that command or the command WITH the RETURNING "ID" part both fail the UNIQUE constraint.

So, my question is, why is EF Core failing to insert that record and how can I further troubleshoot it to find out why.

EDIT: After trying @pumpkin's recommendation I found out what the reason is for the issue.

In the source data there are TWO identical orders. So when I tell EF to add a range of orders - there are two identical members in that collection. The kicker? EF fails when trying to insert THE FIRST OF THE TWO.

I.e. EF Core reports a UNIQUE constraint violation even when the database does NOT. It's like EF reports a violation on the entities it is yet to insert.

Just to make it triple clear, EF Core says an item already exists in the database, when it does not and inserting that item manually succeeds. No concurrency of any kind is taking place.

The question is: Why does EF report a UNIQUE constraint violation when there is none?

I have a SQLite database with the following table:

CREATE TABLE "Orders"
(
    "ID"          INTEGER NOT NULL,
    "Title"       TEXT NOT NULL,
    "Description" TEXT,
    "Price"       REAL NOT NULL,
    "WarehouseID" INTEGER NOT NULL,
    "Category"    TEXT,

    FOREIGN KEY("WarehouseID") 
         REFERENCES "Warehouses"("ID") 
             ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY("ID" AUTOINCREMENT),
    UNIQUE("Title", "Description", "Price", "WarehouseID", "Category") ON CONFLICT IGNORE
)

Using Microsoft.EntityFrameworkCore.Sqlite 9.0.2 I have a List<Order> orders that I add to the context like this:

public static void SaveOrders(IEnumerable<Order> orders)
{
    dbContext.Orders.AddRange(orders);
    dbContext.SaveChanges();
}

The above method is being called several times, being passed a List of orders with about 100 members each time.

There are a bunch of orders that need to be processed/saved to the database. Initially everything works fine and up to 1310 records everything is saved to the database with no hussle.

But then something weird happens.

I get a DbUpdateConcurrencyException (no inner exception) because EF Core expected to affect 1 row, but instead 0 were affected.

I know this can happen if EF Core attempts to insert a record that violates a UNIQUE ON CONFLICT IGNORE constraint since SQLite just ignores it and 0 affected rows are returned, however if I run the insert command by hand (I have EF Core logging enabled) it inserts just fine (a new record IS added).

This is the command EF Core logs as attempting to execute

  Executing DbCommand [Parameters=[@p0='Sales', @p1='', @p2='24.90', @p3='16', @p4='iPhone XS 15 extra features camera, sleeve' (Size = 43)], CommandType='Text', CommandTimeout='30']
  INSERT INTO "Orders" ("Category", "Description", "Price", "WarehouseID", "Title")
  VALUES (@p0, @p1, @p2, @p3, @p4)
  RETURNING "ID";

if I execute the following command:

INSERT INTO "Orders" ("Category", "Description", "Price", "WarehouseID", "Title")
  VALUES ('Sales', '', '24.90', '16', 'iPhone XS 15 extra features camera, sleeve')
  RETURNING "ID";

SQLite returns

Execution finished without errors.
Result: 0 rows returned in 15ms

the record isn't inserted, no matter how many times I run the command.

The command I was referring to running that succeeds is the following:

INSERT INTO "Orders" ("Category", "Description", "Price", "WarehouseID", "Title")
  VALUES ('Sales', '', '24.90', '16', 'iPhone XS 15 extra features camera, sleeve')

i.e. without the RETURNING "ID" part. THIS command succeeds and inserts the record. Subsequent executions of that command or the command WITH the RETURNING "ID" part both fail the UNIQUE constraint.

So, my question is, why is EF Core failing to insert that record and how can I further troubleshoot it to find out why.

EDIT: After trying @pumpkin's recommendation I found out what the reason is for the issue.

In the source data there are TWO identical orders. So when I tell EF to add a range of orders - there are two identical members in that collection. The kicker? EF fails when trying to insert THE FIRST OF THE TWO.

I.e. EF Core reports a UNIQUE constraint violation even when the database does NOT. It's like EF reports a violation on the entities it is yet to insert.

Just to make it triple clear, EF Core says an item already exists in the database, when it does not and inserting that item manually succeeds. No concurrency of any kind is taking place.

The question is: Why does EF report a UNIQUE constraint violation when there is none?

• c#
• sql
• sqlite
• entity-framework-core

• Can you try dbContext.ChangeTracker.Clear(); before adding records? – Svyatoslav Danyliv Commented Mar 10 at 17:37
• @SvyatoslavDanyliv, exact same thing happens, no change. – user107586 Commented Mar 10 at 18:00
• Do you have any triggers? – Dale K Commented Mar 10 at 18:58
• Duplicates in your orders enumerable? – Ralf Commented Mar 10 at 19:10
• @DaleK, no triggers. – user107586 Commented Mar 10 at 20:25

1 Answer 1

The answer is: Because there IS a violation.

tl;dr I'm stupid.

Even if you add a bunch of items at once with or without AddRange EF Core will still do separate INSERT statements for each one within a transaction. So, if you have 2 identical entities the first will be inserted in the transaction and the 2nd one will cause a constraint violation within that transaction which will be rolled back and you won't see the item in the database, which caused me to think the first duplicate wasn't inserted, at all, and which is why manually running the INSERT command succeeded.

In summary: EF Core was behaving as it should and you should remember that it usually inserts within a transaction and errors in a transaction cause it to be rolled back so you won't see anything in the database.