Error when inserting refencing objects

[GoogleCodeExporter]

What steps will reproduce the problem?
In e.g. Program2SQLite.cs insert the following code and run it:
using (var context = new ReadTest().CreateDB()) {
  var c = new nwind.Category { CategoryName = "cat1" };
  context.Categories.InsertOnSubmit(c);
  var p = new nwind.Product { Category = c, ProductName = "prod1" };
  context.Products.InsertOnSubmit(p);
  context.SubmitChanges();
}

What is the expected output? What do you see instead?
The expected result is that one rows would be inserted in each of the two
tables. Instead these queries are executed resulting in a primary key
violation:

INSERT INTO "main"."Categories" ("CategoryName", ...)
 VALUES (:CategoryName, ...)
-- :CategoryName: Input String [cat1]

SELECT last_insert_rowid()

INSERT INTO "main"."Products" ("CategoryID", ..., "ProductName", ...)
 VALUES (:CategoryID, ..., :ProductName, ...)
-- :CategoryID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [9]
-- :ProductName: Input String (Size = 0; Prec = 0; Scale = 0) [prod1]

SELECT last_insert_rowid()

SELECT "Discount", "OrderID", "ProductID", "Quantity", "UnitPrice"
FROM "main"."Order Details"
WHERE ("ProductID" = 78)

SELECT "CategoryID", "CategoryName", "Description", "Picture"
FROM "main"."Categories"
WHERE ("CategoryID" = 9)

INSERT INTO "main"."Products" ("CategoryID", ..., "ProductID",
 "ProductName", ...)
 VALUES (:CategoryID, ..., :ProductID, :ProductName, ...)
-- :CategoryID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [9]
-- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [78]
-- :ProductName: Input String (Size = 0; Prec = 0; Scale = 0) [prod1]

The selects from "Order Details" and "Categories" are addressed in issue 245.

The problem is the second insert into Products. When
SubmitChangesImpl(ConflictMode) is called CurrentTransactionEntities holds
two items. The first holds the Category and the second the Product. But
when GetReferencedObjects(object) is called for the first, both the
Category and the Product is returned, such that InsertEntity(object,
QueryContext) is called for both items. This mean that when then second
EntityTrack is handled, Product is inserted a second time.

It seems that MoveToAllTrackedEntities is actually called for each inserted
entity to remove the EntityTrack objects from CurrentTransactionEntities.
This means that when the second EntityTrack is processed,
CurrentTransactionEntities is actually already empty. But it is a copy of
CurrentTransactionEntities that is being iterated over in SubmitChangesImpl.

It seems to be an invariant in the second loop of SubmitChangesImpl that
the EntityTrack being processed is removed from CurrentTransactionEntities
after the call to InsertEntity or UpdateEntity. This means that this loop
could be rewritten to just process the first item of
CurrentTransactionEntities until it is empty (and afterward process the
items that initially was in AllTrackedEntities).

RegisterToDelete can not just be changed to set EntityState to ToDelete
since that will then throw a ArgumentOutOfRangeException in SubmitChangesImpl.

Please use labels and text to provide additional information.

The test works as expected when run using the MS implementation e.g. from
Test_NUnit_MsSql_Strict.

A workaround is to leave out "context.Products.InsertOnSubmit(p);"

Original issue reported on code.google.com by anders...@gmail.com on 24 Apr 2010 at 11:42

[GoogleCodeExporter]

This issue is based on the discussion here:
http://groups.google.com/group/dblinq/browse_thread/thread/14860511004baf4c

Original comment by anders...@gmail.com on 24 Apr 2010 at 12:13

[GoogleCodeExporter]

Perhaps the solution is not to suppress the second insert, but rather to 
suppress the
first insert, i.e. if a entity is handled later, don't handle it via
GetReferencedObjects.

Executing this test:

using (var context = new ReadTest().CreateDB()) {
  var c = new nwind.Category { CategoryName = "cat1" };
  context.Categories.InsertOnSubmit(c);
  var s = new nwind.Supplier { CompanyName = "sup1" };
  context.Suppliers.InsertOnSubmit(s);
  var p = new nwind.Product { Category = c, Supplier = s, ProductName = "prod1" };
  context.Products.InsertOnSubmit(p);
  context.SubmitChanges();
}

These queries are executed:

  INSERT INTO "main"."Categories" ("CategoryName", ...) VALUES (:CategoryName, ...)
  -- :CategoryName: Input String (Size = 0; Prec = 0; Scale = 0) [cat1]

  SELECT last_insert_rowid()

  INSERT INTO "main"."Products" ("CategoryID", ..., "ProductName", ...,
   "SupplierID", ...) VALUES (:CategoryID, ..., :ProductName, ..., :SupplierID, ...)
  -- :CategoryID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [9]
  -- :ProductName: Input String (Size = 0; Prec = 0; Scale = 0) [prod1]
  -- :SupplierID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]

  SELECT last_insert_rowid()

  SELECT "Discount", "OrderID", "ProductID", "Quantity", "UnitPrice"
  FROM "main"."Order Details"
  WHERE ("ProductID" = 78)

  SELECT "Address", "City", "CompanyName", "ContactName", "ContactTitle", "Country",
   "Fax", "HomePage", "Phone", "PostalCode", "Region", "SupplierID"
  FROM "main"."Suppliers"
  WHERE ("SupplierID" = 0)

  SELECT "CategoryID", "CategoryName", "Description", "Picture"
  FROM "main"."Categories"
  WHERE ("CategoryID" = 9)

  INSERT INTO "main"."Suppliers" (..., "CompanyName", ...) VALUES (...,
   :CompanyName, ...)
  -- :CompanyName: Input String (Size = 0; Prec = 0; Scale = 0) [sup1]

  SELECT last_insert_rowid()

  INSERT INTO "main"."Products" ("CategoryID", ..., "ProductID", "ProductName", ...,
   "SupplierID", ...) VALUES (:CategoryID, ..., :ProductID, :ProductName, ...,
   :SupplierID, ...)
  -- :CategoryID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [9]
  -- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [78]
  -- :ProductName: Input String (Size = 0; Prec = 0; Scale = 0) [prod1]
  -- :SupplierID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [30]

Note that the first insert into Products occurs before the insert into 
Suppliers and
and the insert into Product might fail (e.g. in a database there the foreign key
check are not deferred). This would be handle correctly if both these inserts, 
which
occurs be because of GetReferencedObjects, where suppressed and the Product was 
only
handled later when processing the EntityTrack containing the Product.

This comment is based on this thread:
http://groups.google.com/group/dblinq/browse_thread/thread/dcdad3c78c6db8ac

Original comment by anders...@gmail.com on 24 Apr 2010 at 12:30

[GoogleCodeExporter]

A possible patch, that solves the two test cases.
But is the existing tracking flags and data-structures sufficient?
What if the same entity appears twice in the entityTracks list?

Original comment by anders...@gmail.com on 9 May 2010 at 12:14

Attachments:

• DataContext.patch