Deleting a LINQ to SQL EntitySet Member in a Bound DataGridView Orphans Table Records - by Roger Jennings

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
0
Sign in
to vote
ID 306378 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 10/23/2007 12:28:45 PM
Access Restriction Public

Description

In a three-level hierarchy (Northwind Customer, Order, and Order_Detail) of DataGridViews with BindingSources bound to NorthwindDataContext.Customers and Orders and OrderDetails EntitySets with default code generation, deleting an Order member sets the foreign key value (CustomerID) to NULL instead of removing the corresponding row. This action leaves orphaned rows for the Order and its Order_Details rows in the corresponding table.

Here's the T-SQL generated:

UPDATE [dbo].[Orders]
SET [CustomerID] = @p13
WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] IS NULL) AND ([ShipVia] = @p5) AND ([Freight] = @p6) AND ([ShipName] = @p7) AND ([ShipAddress] = @p8) AND ([ShipCity] = @p9) AND ([ShipRegion] = @p10) AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11123]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p2: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/22/2007 12:00:00 AM]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/5/2007 12:00:00 AM]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p6: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p7: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p8: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p9: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p10: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p11: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p12: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p13: Input String (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 

The workaround is to remove any Order_Details and the Orders EntitySet member(s) from the DataContext by executing a LINQ query to obtain a reference(s) and use them as the arguments of a Remove(member) method call.

This isn't a blocking bug, but it's certainly an inconvenience.

For more details, see LINQ to SQL's WinForms Databinding Story (http://oakleafblog.blogspot.com/2007/10/linq-to-sql-winforms-databinding-story.html).
Sign in to post a comment.
Posted by Roger Jennings on 7/21/2008 at 3:10 PM
Wow! Eight months for a response!

You guys must be busy.

--rj
Posted by Microsoft on 7/21/2008 at 2:51 PM
Hi,

LINQ to SQL doesn't support cascade delete, so in order for the child rows to be deleted along with the parent, you need to set up the cascade delete on the SQL Server. Also, if you want to be able to remove the relationship on the client side and have the child automatically be deleted, you can use the DeleteOnNull property in the Association attribute.

Thanks,
LINQ to SQL Team
Posted by Roger Jennings on 11/3/2007 at 3:02 PM
The Relation dialog of the preceding comment is that of the DataSet Designer, not the O/R Designer.

--rj
Posted by Roger Jennings on 10/30/2007 at 4:13 PM
Additional problem mentioned in http://oakleafblog.blogspot.com/2007/10/linq-to-sql-winforms-databinding-story.html:

The Relation dialog has the ability to select the Update and Delete Rules as None, SetNull, SetDefault, or Cascade. However, selection dropdowns in the Beta 2 version of the Relation dialog always show None and don't display the actual current setting. I.e., the Rules setting doesn't round-trip.

--rj
Posted by Roger Jennings on 10/30/2007 at 4:12 PM
Additional problem mentioned in http://oakleafblog.blogspot.com/2007/10/linq-to-sql-winforms-databinding-story.html:

The Relation dialog has the ability to select the Update and Delete Rules as None, SetNull, SetDefault, or Cascade. However, selection dropdowns in the Beta 2 version of the Relation dialog always show None and don't display the actual current setting. I.e., the Rules setting doesn't round-trip.

--rj