On Apr 22, 12:56 pm, mabu <[email protected]> wrote:
> Some Code may help:
>[...]
> In the database i following:
> * p is inserted twice with correct reportid
> * c is inserted three times with the first product-id
> * c2 is inserted once but without a product id
Just noticed: How did p get inserted twice? Don't you have a primary
(or unique) key on the column? And if you don't, do you have foreign
keys?
I created a simplified test, that uses the northwind schema.
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();
}
This fails with a primary key violation on both Oracle and SQLite. In
the below lines starting with " Call ", ".. " and "-- Context: " have
been removed and non primary-/foreign-key or name columns have been
removed for readability. Some things look odd to me:
- The selects from OrderDetails and Categories. Putting a breakpoint
in WriteLog(IDbCommand) shows that they come from the loop over
members in UpdateReferencedObjects(object). Is it really necessary to
go to the database (on insert)?
- The same selects uses literal values rather than parameters in the
where-clause.
- 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 while running InsertEntity(object, QueryContext) for the
category, UpdateReferencedObjects(entity) will insert the product. So
when the second EntityTrack is processed, the Product is inserted
again.
---- Oracle ----
INSERT INTO "NORTHWIND"."Categories" ("CategoryID",
"CategoryName", ...) VALUES
(Categories_seq.NEXTVAL, :CategoryName, ...)
-- :CategoryName: Input AnsiString (Size = 4; Prec = 0; Scale = 0)
[cat1]
SELECT Categories_seq.CurrVal INTO :CategoryID FROM DUAL
INSERT INTO "NORTHWIND"."Products" ("CategoryID", ..., "ProductID",
"ProductName", ...) VALUES (:CategoryID, ...,
Products_seq.NEXTVAL, :ProductName, ...)
-- :CategoryID: Input Decimal (Size = 0; Prec = 0; Scale = 0) [7]
-- :ProductName: Input AnsiString (Size = 5; Prec = 0; Scale = 0)
[prod1]
SELECT Products_seq.CurrVal INTO :ProductID FROM DUAL
SELECT "Discount", "OrderID", "ProductID", "Quantity", "UnitPrice"
FROM "NORTHWIND"."OrderDetails" WHERE ("ProductID" = 10)
SELECT "CategoryID", "CategoryName", "Description", "Picture" FROM
"NORTHWIND"."Categories" WHERE ("CategoryID" = 7)
INSERT INTO "NORTHWIND"."Products" ("CategoryID", ..., "ProductID",
"ProductName", ...) VALUES
(:CategoryID, ..., :ProductID, :ProductName, ...)
-- :CategoryID: Input Decimal (Size = 0; Prec = 0; Scale = 0) [7]
-- :ProductID: Input Decimal (Size = 0; Prec = 0; Scale = 0) [10]
-- :ProductName: Input AnsiString (Size = 5; Prec = 0; Scale = 0)
[prod1]
---- SQLite ----
INSERT INTO "main"."Categories" ("CategoryName", "Description",
"Picture") VALUES (:CategoryName, ...)
-- :CategoryName: Input String (Size = 0; Prec = 0; Scale = 0) [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]
--
Anders
--
You received this message because you are subscribed to the Google Groups
"DbLinq" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/dblinq?hl=en.
