Using LINQ to delete child records automatically

Question

I am somewhat new to LINQ and have a quick question regarding deleting.

Say, for example I have 2 tables, Orders and OrderItems. Using LINQ, I can easily create a new child record by using

order.Items.Add(new OrderItem());

and this will create the child record in the database and update its foreign key to the orderId. This is great, I like it! However when I want to remove a child record

order.Items.Remove(orderItem);

I get an error when I sumbit the changes (because its not actually deleting the child row (order item), just removing the foreign keyId). Is it possible to do this the way I would like to? I don't want to have to create a whole bunch of repositories and if ladders to delete all child rows for a large database.

Thanks in advance.

E

Answer 1

You can achieve that in the DB itself by configuring the Foreign key relationship to delete child records on deletion of the parent's key.

Note that this is transparent to Linq2SQL and it will not be aware of it, so it's best to make sure you do not keep the datacontexts around after that, since the OrderItem objects will still be present.

Answer 2

Set ON DELETE CASCADE for the table in question which will let the SQL Server handle this for you.