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

Sharepoint Webcasts for ASP.NET Developers

Anyone that's interested in learning some useful information about SharePoint you should tune into these MSDN web casts. I know from watching Andrew Connell's session's at a past devconference that he is an awesome presenter and a SharePoint genius. Check it out!

They are every Tuesday & Wednesday form 12p-1pm(GMT -0500) starting next week for the next five weeks. Here's a breakdown of the schedule:

Date (all times EDT)	Topic & Registration URL	Presenter
Tues, May 20 : 12-1p	Web Parts	Rob Bogue
Wed, May 21 : 12-1p	Data Lists	Rob Bogue
Tues, May 27 : 12-1p	Silverlight	Andrew Connell
Wed, May 28 : 12-1p	Event Handlers	Andrew Connell
Tues, June 3 : 12-1p	Site Branding	Andrew Connell
Wed, June 4 : 12-1p	Workflow	Rob Bogue
Tues, June 10 : 12-1p	Web Services	Andrew Connell
Wed, June 11 : 12-1p	Page Navigation	Andrew Connell
Tues, June 17 : 12-1p	User Management	Rob Bogue
Wed, June 18 : 12-1p	Content Types	Rob Bogue

Speaking at the Cleveland Day of .NET

Being that it was my first presentation ever, I was a little nervous about getting up in front of 50 or so other developers and talking about LINQ to SQL. LINQ itself is not that hard to talk about from a 10,000 ft. view but where things start to get blurry is when you try to show people how things work with demos. I had spent most of my time working on getting all the powerpoint material (slides, notes, speaking material) perfect without using notecards or anything like that but what I thought would be a breeze was the code.



Obviously not, I had developed the demos about a week prior and they were all working fine then. However, when I was actually giving the demo I ran into a few problems. My Query Analyzer threw an exeception, my debugger decided to stop working, and I forgot where to go to show everyone how to implment Stored Procedures over manual LINQ code. Anyways, aside from those issues everything else I think went well. I will take what I have learned from this exeperience and make myself better for the next time. I really do enjoy speaking about technology and how it can make our lives more efficient and productive.

You can download the sample code and slides at the link below.
http://cid-e53966b765831664.skydrive.live.com/browse.aspx/Powerpoint%20Slides%20%7C0%20Code



-Josh