How to perform deletes with LINQ to SQL

When I first started working with LINQ to SQL I was very impressed by how easy it was to delete records from the db with the new API's. The cool thing is that when you need delete records that have child records the way you go about it is straight-forward and object-oriented. For example, say you have a Customers with Orders which would constitute a foreign-key relationship, if you needed to delete that Customer you would have to delete their records first or you would violate referential integrity rules. With this example being pretty straight-forward let's look at some code examples of this.

C# Example:
DataContext = new DataContext("server=localhost;database=MyDb");

Customer c = ctx.Customers.Single(o => o.CustomerId == "12345");

//Customers has a entity set on it called Orders which has a collection of Orders for that
//Customer
foreach(Order o in c.Orders)

{

//Do something with order object o...
}


ctx.Customers.DeleteOnSubmit(c);
ctx.SubmitChanges();
^^^^^^
//this would cause a run time error because there are child //records present

So, what we need to do is delete the customers orders first and then delete the customer record.Now, as one might expect you can do this a number of ways. Also, one might say that if you were doing this with stored procedures or in-line sql that you would have to delete the child records first and then delete the parent record. Well, this is not neccessarily true with LINQ to SQL. With our datacontext remembering all of objects and their relationships, we can delete the records in any order we want because the datacontext will know what needs to be deleted first because of the FK relationship. Pretty cool, huh? I will show how we can accomplish this below.

DataContext = new DataContext("server=localhost;database=MyDb");

Customer c = ctx.Customers.Single(o => o.CustomerId == "12345");

//You can do it this way...
c.Orders.Clear(); <---secretly sets values in the FK field to NULL for this customer...
ctx.Customers.DeleteOnSubmit(c);

ctx.SubmitChanges();

//Or you can do it this way...
ctx.Customers.DeleteOnSubmit(c);
c.Orders.Clear(); <---secretly sets values in the FK field to NULL for this customer...

ctx.SubmitChanges();

It does not matter which order we the code is processed because when the SubmitChanges() method executes the data context will take care of the FK relationship for us. I was a little curious about what LINQ to SQL what doing under the covers to achieve this. So what I did was to open a trace and monitor the generated SQL from the .NET Framework. By examing the trace generated from SQL Profiler you can see that what is really happening behind the scenes is that LINQ to SQL is going the Customer's orders and setting the value of the foreign-key field to a NULL value. Then, the datacontext issues the delete statement for that customer.

By allowing us to work with objects and collections instead of datasets and datatables performing deletes on parent and child data is relativily simple. If you have any questions about the code, you can comment on this post or send me an email at jclark434175@gmail.com