Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

UpdateReferencedObjects is inefficient and/or unneeded #245

Open
GoogleCodeExporter opened this issue Apr 24, 2015 · 0 comments
Open

UpdateReferencedObjects is inefficient and/or unneeded #245

GoogleCodeExporter opened this issue Apr 24, 2015 · 0 comments

Comments

@GoogleCodeExporter
Copy link

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);
  context.SubmitChanges();
}

using (var context = new ReadTest().CreateDB()) {
  var c = context.Categories.First();
  c.CategoryName += "!";
  context.SubmitChanges();
}

What is the expected output? What do you see instead?

The first will produce this log:

  INSERT INTO "main"."Categories" ("CategoryName", "Description",
   "Picture") VALUES (:CategoryName, :Description, :Picture)
  -- :CategoryName: Input String [cat1]
  -- :Description: Input String []
  -- :Picture: Input Binary []
  SELECT last_insert_rowid()

  SELECT "CategoryID", "Discontinued", "ProductID", "ProductName",
   "QuantityPerUnit", "ReorderLevel", "SupplierID", "UnitPrice",
   "UnitsInStock", "UnitsOnOrder"
  FROM "main"."Products"
  WHERE ("CategoryID" = 9)

While the second will produce this log:

  SELECT "CategoryID", "CategoryName", "Description", "Picture"
  FROM "main"."Categories" LIMIT 1

  UPDATE "main"."Categories" SET "CategoryName" = :CategoryName
   WHERE "CategoryID" = :CategoryID
  -- :CategoryID: Input Int32 [1]
  -- :CategoryName: Input String [Beverages!]

  SELECT "CategoryID", "Discontinued", "ProductID", "ProductName",
   "QuantityPerUnit", "ReorderLevel", "SupplierID", "UnitPrice",
   "UnitsInStock", "UnitsOnOrder"
  FROM "main"."Products"
  WHERE ("CategoryID" = 1)

Note the last select in both cases, which is comes from the quiry for
members in UpdateReferencedObjects:
- The query should use a parameter rather than a literal
- It seems to me that UpdateReferencedObjects tries to do the equivalent of
"on update cascade" annotation for foreign keys (is this understanding
correct?):
- - This assumes that the foreign key constraint is deferred (or ignored as
mysql previously did?), such that the update of the primary key don't fail.
A safer way would be to insert a rows with the new primary key value,
update the references and then delete the old row.
- - During an insert there will not be any referencing rows, so the select
is unnecessary.
- - In the update the primary key was not changed, so the select is also
unnecessary here.
- - In the case where an update is needed, instead of loading the entire
entity and updating the foreign key column, batch updates should be used.

Please use labels and text to provide additional information.
Changing the updated column in the second case to "c.CategoryID = -4;" to
examine the exact case where UpdateReferencedObjects should be needed,
first execute an empty query instead of the expected update of the primary
key and then doesn't persist any of the foreign key columns updated by
UpdateReferencedObjects.

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant