Note: Requires SQL Server Express 2008 and .NET 3.5 to run.

Book Catalog Application

Introduction

This is the second part of a three-part series on using LINQ to SQL:

Part 1: Mapping Tables to Objects
Part 2: Adding/Updating/Deleting Data
Part 3: WPF Data Binding with LINQ to SQL

These tutorials describe how to map your classes to your tables manually (rather than with an automated tool like SqlMetal) so that you can have support for M:M relationships and data binding against your entity classes. Even if you do choose to auto-generate your classes, understanding how these techniques work will allow you to expand the code to better fit your application's needs and to better troubleshoot issues when they arise.

The purpose of this second article is to continue introducing LINQ to SQL by showing how to:

Add/Update/Delete data in your database through your classes using LINQ to SQL.
Ensure that your M:M, 1:M, and M:1 relationships are kept in synch* as you perform these adds/updates/deletes.

The article walks through each of these steps to explain how you can do this in your own applications. The attached Book Catalog application provides an example of everything described here plus a simple WPF GUI to allow you to browse the data. Part 3 will extend this to show how to update data through the GUI with WPF data binding.

*The techniques for handling 1:M and M:1 relationships are those described by MSDN's LINQ to SQL whitepaper. The techniques for handling M:M relationships are my own extensions on those.

Getting Started

This article builds on top of A LINQ Tutorial: Mapping Tables to Objects to add data persistence functionality via LINQ to SQL. Please refer to the latest version of that article to see how the application has been setup and how its classes have been mapped to their corresponding database tables.

The Book Catalog example uses the same database. That is, a simple SQL Server Express 2008 database that contains Books, Authors, and Categories:

Database Diagram

It has a DELETE CASCADE from Books to BookAuthors so deleting a book deletes its authors, but no other CASCADEs so you can't delete an Author or Category if it has any books. All fields require values except for Books.Category, which can be null.

Persisting Changes to the Database

Let's start by persisting changes that only impact a single class/table.

Note: The application is set up to only copy the database into the bin directory "if newer". As you make changes to the database, if you ever want to revert back to the initial database, simply do a Clean Build.

Data changes in LINQ are persisted through your DataContext.

In A LINQ Tutorial (Part 1), we created a strongly typed DataContext called BookCatalog that knows how to connect to our database and holds all of our public "entity" classes as Table collections:

[Database]
public class BookCatalog : DataContext
{
    public BookCatalog( ) : base( ... );
 
    public Table<Author> Authors;
    public Table<Book> Books;
    public Table<Category> Categories;
}

Updating Existing Data

When you retrieve objects from your DataContext, LINQ to SQL tracks all changes made to them, and you can persist those changes by calling SubmitChanges().

We'll walk through this using Category as an example, and update it by renaming its "Programming Practices" category to "Technical Practices".

1. Retrieve the object to be changed from the DataContext

Use LINQ to retrieve the category you want to update from your DataContext.

LINQ's Single method provides an easy way to do this by taking a lambda expression that returns a single object. In this case, the category (c) whose Name == "Programming Practices":

BookCatalog bookCatalog = new BookCatalog( );
Category category = 
  bookCatalog.Categories.Single( c => c.Name == "Programming Practices" );

2. Update the object

category.Name = "Technical Practices";

3. Submit changes to the DataContext

bookCatalog.SubmitChanges( );

Your update has now been persisted to the database.

Adding New Data

You can add new records to the database by creating new objects and adding them to the appropriate Table collection in your DataContext.

If your table uses an Identity Column for its primary key, as Category does, LINQ to SQL automatically updates your object with that ID once it's been added to the database.

As an example, we'll add a new Category for Java.

1. Create new object

If your primary ID column is an Identity column, leave it blank for the database to set for you.

Category category = new Category( ) { Name = "Java" };

2. Add object to the appropriate Table collection in your DataContext

This is done with the InsertOnSubmit() method on your Table collection:

BookCatalog bookCatalog = new BookCatalog( );
bookCatalog.Categories.InsertOnSubmit( category );

3. Submit changes to the DataContext

The data will exist locally in your DataContext, but will not be persisted to the database until you call SubmitChanges().

bookCatalog.SubmitChanges( );

4. Database generated IDs are automatically set for you

If your class has an Identity column and is flagged as IsDbGenerated in its Column attribute, as Category is:

[Table( Name = "BookCategories" )] 
public class Category : IBookCollection
{
    [Column( IsPrimaryKey = true, IsDbGenerated = true )] public int Id { get; set; }
    ...

then on SubmitChanges(), LINQ to SQL will automatically update that column's value for you to the ID set by the database.

Deleting Data

Similarly, you can remove records from the database by removing them from the appropriate Table collection in your DataContext.

As an example, we'll remove the Java Category we just created.

1. Retrieve object to be changed from the DataContext

Retrieve the object you want to delete from your DataContext:

BookCatalog bookCatalog = new BookCatalog( );
Category category = bookCatalog.Categories.Single( c => c.Name == "Java" );

2. Remove the object from the appropriate Table collection in your DataContext

This is done with the DeleteOnSubmit() method on your Table collection:

bookCatalog.Categories.DeleteOnSubmit( category );

3. Submit changes to the DataContext

The data will have been removed locally from your DataContext, but will not be deleted from the database until you call SubmitChanges().

bookCatalog.SubmitChanges( );

Note that, for any given transaction, each of the steps must be done using the same DataContext. You cannot, for example, retrieve an object from one DataContext instance and then pass it to the DeleteOnSubmit() method from a different instance of your DataContext.

Updating Relationships

LINQ to SQL will not automatically manage the consistency between related objects. Therefore, you need to add logic to your classes so they know how to update their relationships themselves.

Updating a M:1 Relationship

In our M:1 classes, we want to be able to traverse the object model from both directions. For example, each Book should contain an instance of the Category it belongs to, and each Category should contain a collection of its Books:

Book to Catalog Relationship

Imagine you notice that the book C# in Depth's category has been set to Java and you want to fix it. When you update C# in Depth to the category C#, three things should happen:

The Book C# in Depth's Category is updated to C#
The Category C#'s list of Books is updated to include C# in Depth.
The Category Java's list of Books is updated to no longer include C# in Depth.

LINQ to SQL will handle step #1 for you when you call SubmitChanges(), but your classes are responsible for updating the other side of their relationship (steps 2 and 3).

In A LINQ Tutorial (Part 1), we set Book's Category property up as an Association so that we can retrieve the Category instance that this book is associated with (not just its ID, as is stored in the database):

[Table( Name = "Books" )]
public class Book
{
    ...

    [Column( Name = "Category" )] private int? categoryId;
    private EntityRef<Category> _category = new EntityRef<Category>( );

    [Association( Name = "FK_Books_BookCategories", 
      IsForeignKey = true, Storage = "_category", 
      ThisKey = "categoryId" )]
    public Category Category {
        get { return _category.Entity; }
        set { _category.Entity = value; }
    }

The Association attribute tells LINQ to SQL to place an instance of the appropriate Category inside the EntityRef _category, which we then use as the backing field for our Category property.

Update the property's set method to synchronize the other side of the relationship

When a Book's Category property is updated, we need to do two things:

Remove the book from the old category's list of books.

Category priorCategory = _category.Entity;
if( priorCategory != null )
    priorCategory.Books.Remove( this );

Add this book to the new category's list of books.

Category newCategory = value;
if( newCategory != null )
    newCategory.Books.Add( this );

But, and this is the tricky part, we're going to be instructing each side of the relationship to inform the other side when it's updated. So, we need to add some additional checks to this method to prevent it from looping:

Don't do anything if the new category is the same as the old category.
Prior to calling priorCategory.Books.Remove(), set our category to null.
Prior to calling newCategory.Books.Add(), set our category to its new value.

The final version of Category's set method incorporates these checks:

public Category Category {
    ...
    set {
          Category priorCategory = _category.Entity;
          Category newCategory = value;

          if( newCategory != priorCategory ) {

              // remove this book from our prior category's list of books
              _category.Entity = null;
             if( priorCategory != null ){
                  priorCategory.Books.Remove( this );
             }

             // set category to the new value
              _category.Entity = newCategory;

              // add this book to the new category's list of books
             if( newCategory != null ){
                  newCategory.Books.Add( this );
             }
          }
       }
}

Try it Out: An M:1 Update

Let's update the Category for the two PRO LINQ in C# books to C# (they're currently in the LINQ category). There are two ways to view this relationship in our classes:

Both of these Book instances has a Category of LINQ:

Book Details

The LINQ Category includes these books in it's Books property (and the C# category does not):

In our Book Catalog application, a book can only belong to a single category, so when we update the book's category, it should get automatically moved from the LINQ category to the C# one.

To do this, retrieve both books from the DataContext and set their Category to the C# category. Then, call SubmitChanges() to persist the changes to the database.

BookCatalog bookCatalog = new BookCatalog( );
Category csharpCategory = 
  bookCatalog.Categories.Single( cat => cat.Name == "C#" );

var books = bookCatalog.Books.Where(
 b => b.Title.Contains("Pro LINQ: Language Integrated Query in C#"));
foreach( var book in books ) {
     book.Category = csharpCategory;
}

bookCatalog.SubmitChanges();

Even though only the Book objects were updated, the synchronization code we added caused the Category objects to be updated as well:

Both of these Book instances now have a Category of C#:

Book Details

The C# Category includes these two in its Books property (and the LINQ Category does not):

C# and LINQ books

Updating M:1 Relationships in a Join Table

Now, let's synchronize the M:1 relationships in our M:M Join tables. Whenever we have a M:M Join table, as we do with BookAuthors, the join table is, of course, just two M:1 relationships, so we can mirror what we did for Book.Category.

As an example, BookAuthors contains a M:1 relationship to Book and a M:1 relationship to Author in order to bridge their M:M relationship:

BookAuthors Relationships

We want our Join tables to provide a central location for ensuring both sides of the M:M relationship are kept synchronized. So, regardless of whether someone updates a Book to have a new author, or if they update an Author to add that person's books - the central class BookAuthor will ensure both sides are synchronized accordingly.

In our example, we'll update BookAuthor's set() method for Author just like we updated Book.Category's set():

[Table( Name = "BookAuthors" )]
internal class BookAuthor
{
    ...

    public Author Author {
        ...
        set {
            Author priorAuthor = _author.Entity;
            Author newAuthor = value;

            if( newAuthor != priorAuthor ) {
                _author.Entity = null;
                if( priorAuthor != null )
                   priorAuthor.BookAuthors.Remove( this );

                _author.Entity = newAuthor;
                newAuthor.BookAuthors.Add( this );
            }
        }
    }

and for BookAuthor's Book:

public Book Book {
    ...
    set {
       Book priorBook = _book.Entity;
       Book newBook = value;

        if( newBook != priorBook ) {
            _book.Entity = null;
            if( priorBook != null )
                priorBook.BookAuthors.Remove( this );

            _book.Entity = newBook;
            newBook.BookAuthors.Add( this );
        }
    }
}

We can't see this in action just yet. First, we have to see how to handle the other side of our M:M relationships: the 1:M relationship.

Updating a 1:M Relationship

Just as we can update a Book's category and have it automatically synchronize the old and the new Category for us, we should be able to add or remove books from a Category and have them update the Book instances accordingly.

This section will walk through this using category.Books as an example.

In A LINQ Tutorial (Part 1), we set Category's Books property up as the other side of the Association -- the one which holds a collection of books:

[Table( Name = "BookCategories" )] 
public class Category
{
    ...

    private EntitySet<book> _books = new EntitySet<book>();

    [Association( Name = "FK_Books_BookCategories", 
      Storage = "_books", OtherKey = "categoryId", 
      ThisKey = "Id" )]
    public ICollection<book> Books {
        get { return _books; }
        set { _books.Assign( value ); }
    }
}

To synchronize this relationship:

Whenever a book is added to a category, set that book's Category to this.
Whenever a book is removed from a category, set that book's Category to null.

Here's the cool part. In LINQ to SQL, the collection of books is backed by an EntitySet. And, EntitySets let you specify delegates to be called whenever an item is added to or removed from its collection. So, create two delegate methods to handle this synchronization:

On add, set the book's Category to this:

private void OnBookAdded(Book addedBook ){
    addedBook.Category = this;
}

On remove, set the book's Category to null:

private void OnBookRemoved(Book removedBook ){
    removedBook.Category = null;
}

Then, construct the EntitySet by passing in these two delegate methods. Since the methods are instance methods, you'll need to do this in Category's constructor:

public Category( ){
    _books = new EntitySet<Book>( OnBookAdded, OnBookRemoved );
}

Adding Data in a 1:M Relationship

To demonstrate adding new 1:M relationships, add a couple of new books without a category. Use InsertAllOnSubmit() to add multiple records at once, and SubmitChanges() to persist them to the database:

IEnumerable<Book> books = new List<Book>( ){
      new Book( ){ Title = 
        "Essential Windows Presentation Foundation", Price = 44.99m },
      new Book( ){ Title = "WPF In Action", Price = 40.99m } };
 
BookCatalog bookCatalog = new BookCatalog( );
bookCatalog.Books.InsertAllOnSubmit( books );
bookCatalog.SubmitChanges( );

Now, create a new WPF Category and add these two books to it:

Category category = new Category( ) { Name = "WPF" };
foreach( var wpfBook in books ){
     category.Books.Add( wpfBook );
}

bookCatalog.Categories.InsertOnSubmit( category );
bookCatalog.SubmitChanges( );

Notice that we never set the Category on the books directly, but the synchronization code we added takes care of that for us. So now, if you view the details for the books, you'll see their Category is set to WPF:

WPF Books

"Moving" Data in a 1:M Relationship

In a 1:M relationship, such as Category:Books, a Book can only belong to a single Category. Therefore, if you take a book that's in one category and add it to a different category, you are effectively moving it to that new category (even though you didn't explicitly ask for this).

For example, we might decide to move those two PRO LINQ in C# books back to the LINQ category after all. However, instead of setting each book's Category property, we can simply add them to the LINQ Category:

BookCatalog bookCatalog = new BookCatalog( );
var books = bookCatalog.Books.Where( 
  b => b.Title.Contains( "Pro LINQ: Language Integrated Query in C#" ) );

Category linqCategory = 
  bookCatalog.Categories.Single( cat => cat.Name == "LINQ" );
foreach( var book in books ) {
    linqCategory.Books.Add( book );
}

bookCatalog.SubmitChanges( );

Now, when you view the book's details, you'll see each has its Category set back to LINQ:

Book Details

Removing Data in a 1:M Relationship

When we remove a book from a category, our OnBookRemoved delegate clears the Category property from that Book instance.

We, for some reason, have two copies of Programming Ruby, so let's remove the Programming Ruby 1.9 version from the Ruby Category.

BookCatalog bookCatalog = new BookCatalog( );
Book book = bookCatalog.Books.Single( 
  b => b.Title.Contains( "Programming Ruby 1.9" ) );

Category rubyCategory = bookCatalog.Categories.Single( cat => cat.Name == "Ruby" );
rubyCategory.Books.Remove( book );

bookCatalog.SubmitChanges( );

Now, when you view the book's details, you'll see its Category is empty:

Ruby Book

Updating M:M Relationships

Finally, we can complete our M:M relationships. These are a little trickier because we want to keep our Join table (BookAuthor) out of our public interface so callers can work directly with Books and Authors and not have to care how they're joined together.

M:M Relationship between Books and Authors

If you're coding along, then you already added logic into BookAuthor so that if either side updates a BookAuthor relationship, it handles synchronizing both the Book and the Author for you. What remains is providing a mechanism for users to set a Book's authors, and an Author's books.

We'll walk through how to do this, starting on the Book side.

In A LINQ Tutorial (Part 1), we made Book's association to BookAuthors internal so it's not part of our public interface:

private EntitySet<BookAuthor> _bookAuthors = new EntitySet<BookAuthor>( );
[Association( Name = "FK_BookAuthors_Books", 
 Storage = "_bookAuthors", 
 OtherKey = "bookId", ThisKey = "Id" )]
internal ICollection<BookAuthor> BookAuthors {
    get { return _bookAuthors; }
    set { _bookAuthors.Assign( value ); }
}

And, we created a public Authors property that serves as a proxy, pulling this book's authors from BookAuthors:

public ICollection<Author> Authors { 
  get { return ( from ba in BookAuthors select ba.Author ).ToList( ); } }

The problem with this approach is we now have no way to tell when a caller adds or removes Authors from our Book. What we need is something akin to the EntityRef delegates for OnAdd and OnRemove so that we can perform our synchronization whenever a caller adds or removes an author.

1. Make Authors an ObservableCollection

Since our data is not in an EntryRef, we need another way to get notifications when an author is added or deleted. We can do this by making Authors return an ObservableCollection that will notify us when changes are made to it:

public ICollection<author> Authors {
    get {
          var authors = new ObservableCollection<Author>( 
                  from ba in BookAuthors select ba.Author );
          authors.CollectionChanged += AuthorCollectionChanged;
          return authors;
        }
    }

The second line (authors.CollectionChanged += AuthorCollectionChanged) registers our AuthorCollectionChanged method (added below) to receive notifications whenever the collection is changed.

2. Create OnAdd and OnRemove notification methods

To synchronize our relationship with BookAuthor:

When an author is added to a book, we need to add a BookAuthor (Join) record to store this relationship.
When an author is removed from a book, we need to remove its BookAuthor (Join) relationship record.

Create the delegate methods to handle this synchronization:

On add, add a BookAuthor record to store this relationship:

private void OnAuthorAdded( Author addedAuthor ){
    BookAuthor ba = new BookAuthor( ) { Author = addedAuthor, Book = this };
}

On remove, remove the BookAuthor record to delete this relationship (we'll add the Remove() method to BookAuthor further down):

private void OnAuthorRemoved( Author removedAuthor ) {
    BookAuthor baRecord = BookAuthors.SingleOrDefault( ba => 
       ba.Book == this && ba.Author == removedAuthor );
    if( baRecord != null )
        baRecord.Remove( );
}

3. Create a AuthorCollectionChanged method to receive update notifications

This is the delegate method we registered to be notified of all changes made to our Authors collection.

Create the method to respond to the Add and Remove events, and use its NotifyCollectionChangedEventArgs to gain access to the items that were added (e.NewItems) or removed (e.OldItems):

private void AuthorCollectionChanged( object sender, 
                NotifyCollectionChangedEventArgs e ) {
    if( NotifyCollectionChangedAction.Add == e.Action ) {
        foreach( Author addedAuthor in e.NewItems )
            OnAuthorAdded( addedAuthor );
    }

    if( NotifyCollectionChangedAction.Remove == e.Action ) {
        foreach( Author removedAuthor in e.OldItems )
            OnAuthorRemoved( removedAuthor );
    }
}

4. Attach new (added) BookAuthors records to the DataContext

If you've been coding along, then you already did this when you added logic to BookAuthor's set() methods for Book to add the new BookAuthor instance to the Book:

internal class BookAuthor
{
   ...
   public Book Book {
      ...
      set {
             ...
             Book newBook = value;
             newBook.BookAuthors.Add( this );

The way this works is that when you attach (add) the BookAuthor instance to your Book, LINQ to SQL notices the change (because it's monitoring Book for changes), and so it will automatically insert this attached BookAuthor record into the database the next time you call SubmitChanges(). Voila, a new BookAuthor relationship.

5. Create a Remove method for BookAuthor

Add a Remove() method to BookAuthor that will:

Delete the record from the database.
Remove the BookAuthor instance from Book.
Remove the BookAuthor instance from Author.

public void Remove( ){
    BookCatalog.RemoveRecord( this );

    Author priorAuthor = Author;
    priorAuthor.BookAuthors.Remove( this );

    Book priorBook = Book;
    priorBook.BookAuthors.Remove( this );
}

Step 1 calls a RemoveRecord() method on the DataContext (which we'll add below). The reason it does this is because, unlike any of our other changes made so far, deleting a record always requires direct access to a DataContext. But, we don't want to have to pass the DataContext to our entity classes.

We can instead handle this with a static method on the DataContext that:

Retrieves the DataContext instance to use*
Deletes the record from that DataContext instance.

The method uses Generics so that it can take any record. The example below handles Step #1 by creating a new DataContext instance to handle the deletion:*

[Database]
public class BookCatalog : DataContext
{
    public static void RemoveRecord<T>( T recordToRemove ) where T : class {
        // Retrieve BookCatalog instance in way
        // that's consistent with your DataContext strategy
        BookCatalog bookCatalog = new BookCatalog();

        Table<T> tableData = bookCatalog.GetTable<T>( );
        var deleteRecord = tableData.SingleOrDefault( record => record == recordToRemove );
        if( deleteRecord != null ) {
            tableData.DeleteOnSubmit( deleteRecord );
        }

        // If you created a new BookCatalog instance, submit it's changes here
        bookCatalog.SubmitChanges( );
    }

* In some applications, it might be more appropriate to use an existing DataContext than creating a new one. See LINQ to SQL DataContext Lifetime Management by Rick Strahl for the various DataContext handling strategies and then update the way this method retrieves its DataContext to be consistent with that strategy (e.g., if you use a single global DataContext, have this method use that global DataContext instead of creating a new one).

Warning: If you do choose to create a new DataContext instance for RemoveRecord(), you'll have to either:

Submit the changes in the method. This is the simplest solution; however it can be problematic if you're removing these records as part of a larger transaction because if you choose to cancel (not Submit) that larger transaction, then this deletion will have already gone through.
Hold onto the DataContext instance so that you can submit its changes at a more appropriate time.

Option #1 is shown above. Option #2 might look something like below:

public class BookCatalog : DataContext
{
  // Create static DataContext for removing M:M Join records 
  private static DataContext contextForRemovedRecords = null;

  public static void RemoveRecord<T>( T recordToRemove ) where T : class {
      // Use the static contextForRemovedRecords
      if( contextForRemovedRecords == null ) 
          contextForRemovedRecords = new BookCatalog( );

      Table<T> tableData = contextForRemovedRecords.GetTable<T>( );
      var deleteRecord = tableData.SingleOrDefault( record => record == recordToRemove );
      if( deleteRecord != null ) {
          tableData.DeleteOnSubmit( deleteRecord );
      }
  }

  // NEW method (not part of LINQ to SQL) to cancel changes
  public void CancelChanges( ) {
      contextForRemovedRecords = null;
  }

  // Override DataContext's SubmitChanges() to handle any removed records
  public new void SubmitChanges( ) {
      if( contextForRemovedRecords != null ) {
          contextForRemovedRecords.SubmitChanges( );
      }
      base.SubmitChanges( );
  }

This does three things:

Creates a static DataContext instance (contextForRemovedRecords) and uses it in RemoveRecord().
Defers submitting the changes for contextForRemovedRecords until the next time SubmitChanges() is called on a BookCatalog instance*.
Adds a new method CancelChanges() (not part of standard LINQ to SQL) to allow users to cancel the changes.

* Obviously, if you're handling concurrent changes, then this needs to be built out to handle that.

5. Update the other side of the M:M Relationship

Finally, update the code for the other side of your M:M relationship. Here is how that will look for Author:

public ICollection<Book> Books {
    get {
        var books = new ObservableCollection<Book>( from ba in BookAuthors select ba.Book );
        books.CollectionChanged += BookCollectionChanged;
        return books;
    }
}

private void BookCollectionChanged( object sender, NotifyCollectionChangedEventArgs e ) {
    if( NotifyCollectionChangedAction.Add == e.Action ) {
        foreach( Book addedBook in e.NewItems ) 
            OnBookAdded( addedBook );
    }

    if( NotifyCollectionChangedAction.Remove == e.Action ) {
        foreach( Book removedBook in e.OldItems )
            OnBookRemoved( removedBook );
    }
}

private void OnBookAdded( Book addedBook ){
    BookAuthor ba = new BookAuthor( ) {Author = this, Book = addedBook};
}

private void OnBookRemoved( Book removedBook ) {
    BookAuthor baRecord = BookAuthors.SingleOrDefault( 
        ba => ba.Author == this && ba.Book == removedBook );
    if( baRecord != null ) {
        baRecord.Remove( );
    }
}

Adding Data in a M:M Relationship

We're now done updating our classes to handle synchronization. Let's see what it looks like to use those classes to perform M:M updates.

Add Bob Martin as an author of Extreme Programming (XP) Explained. To do this, retrieve Bob Martin and the book from your DataContext, add BobMartin to the book's authors, and SubmitChanges():

BookCatalog bookCatalog = new BookCatalog( );
Author bobMartin = bookCatalog.Authors.Single( 
  author => author.Name == "Bob Martin" );
Book xpExplained = bookCatalog.Books.Single( 
  book => book.Title.Contains("Extreme Programming Explained") );

xpExplained.Authors.Add( bobMartin );
bookCatalog.SubmitChanges();

Voila, Bob Martin is now an author of XP Explained, and it shows up in his list of books:

Bob Martin has been added to XP Explained

Removing Data in a M:M Relationship

Of course, Bob Martin isn't really an author of XP Explained, and we don't want to piss off Kent Beck, so let's remove him. We can do this from the other side of the relationship to validate that our updates work in both directions.

Once again, retrieve Bob Martin and the book from your DataContext, but this time, remove the book from Bob Martin:

BookCatalog bookCatalog = new BookCatalog( );
Author bobMartin = 
  bookCatalog.Authors.Single( author => author.Name == "Bob Martin" );
Book xpExplained = bookCatalog.Books.Single( 
  book => book.Title.Contains( "Extreme Programming Explained" ) );

bobMartin.Books.Remove( xpExplained );
bookCatalog.SubmitChanges( );

And the data is set right again; Kent Beck is the sole author of XP Explained, and it no longer shows up in Bob Martin's list of books:

Bob Martin removed from XP Explained

Deleting Data in a M:M Relationship with DELETE CASCADE

Finally, let's delete a book that has authors associated with it.

Our BookCatalog application has a DELETE CASCADE setup between Book and BookAuthors so the database will delete those without LINQ's knowledge, but the logic we've added to our entity classes should automatically handle the synchronization for us.

We'll delete the second copy of Programming Ruby that's in the BookCatalog. This book currently has three authors, so deleting the book should automatically delete all three BookAuthor (Join) records as well as update each author's list of books to no longer include this book:

Programming Ruby 1.9 has 3 Authors

To do this, retrieve the book from your DataContext, delete it from the Books Table collection, and SubmitChanges():

BookCatalog bookCatalog = new BookCatalog( );
Book rubyBook = bookCatalog.Books.Single( 
  book => book.Title.Contains( "Programming Ruby 1.9" ) );
bookCatalog.Books.DeleteOnSubmit( rubyBook );
bookCatalog.SubmitChanges( );

This removes the book, and updates each author's list of books accordingly.

What's Next?

You can find the code in the attached application for everything covered here as well as the example update queries (located in LinqTutorialSampleUpdates.cs). It also includes a WPF application that displays the data and allows you to traverse its relationships via WPF data binding. Part 3 will extend this to show how to update data through the GUI with WPF data binding.

And, check out the rest of this series to take it to the next level in your applications:

Part 1: Mapping Tables to Objects
Part 2: Adding/Updating/Deleting Data
Part 3: WPF Data Binding with LINQ to SQL

History

12/11/2009: Added explanation to the Introduction (why do manually vs. auto-generating). Built out the method for deleting M:M Join records to handle more scenarios (see 5. Create a Remove method for BookAuthor).
12/06/2009: Initial version.

A LINQ Tutorial: Adding/Updating/Deleting Data