Sql – LINQ to SQL : Delete a new added child entity before submitchanges – fails

linq-to-sql

I have a parent child relationship very similar to Northwind Customer/Orders but with one differnece , CustomerID in Orders table can not accept NULL , it is a FK , the child has one column PKey called OrderID

I use this model in a windows forms databinding , upper area is having customer info in text boxes , lower area is having a grid with orders listed

I retrieve a customer using LINQ from DB to allow the user to edit customer info or order details , when i delete a row from the order grid then submit changes , i receive error
"An Attempt was made to remove a relationship Between a Customer and Orders, However , one of the relationship's foreign keys (CustomerID) can't be set to Null"

after a long search on the internet , i used the DeleteOnNull=True attribute and it now works

What is not working however and still give same exact error is when i add a new order to the grid then delete it before SubmitChanges

I understand that when i delete a new order row in the grid i only remove the association to customer, so it sets CustomerID=NULL for that entity , fine , now how do it remove it entirely from the entity set before submit changes to avoid the error , i can't use DeleteOnSubmit because it is a new entity not came from db , i will receive an exception if i tried , so deleting the row by hand using the grid does not work which is calling "Remove" method i guess , and i can't use DeleteOnSubmit , what can i do ?

why it has to be that difficult? at ADO.NET datatable is much easier, if i delete a row and it happends to come from db then it marks it for deletion, if it is a new row then it removes it from the collection, Done! i don't want to go back to Datasets after investing many efforts learning LINQ to SQL now

any help is very appreciated

Thank you
Bassam

Best Answer

I discovered what the problem is and solved it. I want to share the info here if someone else is having the same issue

First i want to mention an important fact: If Cascade Delete is enabled in the DB , then

DeleteOnNull:=True , DeleteRule:="CASCADE"

will be auto inserted in the code behind the .dbml file without the need to manually add DeleteOnNull=True for a specific association.

The problem is, there was another FKey in the child entity for the OrderType, which is OrderTypeID. I set this value like this:

Dim NormalOrderType = (From ot in db.OrderTypes Where ot.OrderTypeID=1 Select ot).Single
NewOrder.OrderType = NormalOrderType          
OrdersBindingSource.Add(NewOrder)

When a user deletes an order from the grid for a specific customer, LINQ to SQL sets the CustomerID FKey to NOTHING, breaking the association to the parent entity. BUT, it does not set OrderTypeID to NOTHING, keeping the other association active to OrderTypes entity. With an OrderType entity already loaded in memory, LINQ to SQL will think that i still want to insert the child entity but with CustomerID=Nothing, and since this is not allowed (as from the dbml file, Nullable=False), it will raise an exception before it tries to save to DB in the first place.

This problem is solved by doing this:

NewOrder.OrderType = NOTHING

This manually removes the association for the other FKey. Then, simply removing the child entity will cause LINQ to SQL to ignore it when submitChanges is called.

Thank you
Bassam Muhammad.

Related Topic