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

Question

Hello,

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

Answer 1

I discovered what is the problem and solved it , i want to share the info here maybe someone else is having same issue

first i want to mention an important fact that , 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 that

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 set the CustomerID FKey to NOTHING , breaking the association to the parent entity , BUT , it does not set OrderTypeID to NOTHING also , keeping the other association active to OrderTypes entity with an OrderType entity already loaded in memory , LINQ to SQL will think then that i still want to insert the child entity but with CustomerID=Nothing , and since this is not allowed as from the dbml file where Nullable=False, it will raise an exception before it tries to save to DB at the first place

problem solved by

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 submit changes

Thank you Bassam Muhammad.