My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
for
  Advanced search   Search tips   Subscriptions
Issue 246: Error when inserting refencing objects
1 person starred this issue and may be notified of changes. Back to list
Status:  Accepted
Owner:  ----


Sign in to add a comment
 
Project Member Reported by anders...@gmail.com, Apr 24, 2010
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);"
Apr 24, 2010
Project Member #1 anders...@gmail.com
This issue is based on the discussion here:
http://groups.google.com/group/dblinq/browse_thread/thread/14860511004baf4c
Apr 24, 2010
Project Member #2 anders...@gmail.com
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
May 9, 2010
Project Member #3 anders...@gmail.com
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?
DataContext.patch
2.8 KB   View   Download
Sign in to add a comment

Powered by Google Project Hosting