C# 2008 Programmer's Reference

};

Then, add a new titleauthor row by associating its author and title properties with the new title and author row you just created:

titleauthor ta = new titleauthor() {
 author = a,
 title = t
};

Finally, save the changes to the object model and submit the changes to the database:

//---record is saved to object model---
database.titleauthors.InsertOnSubmit(ta);
//---send changes to database---
database.SubmitChanges();

Notice that you do not need to worry about indicating the title_id and author_id fields in the titleauthors table; LINQ to SQL does those for you automatically.

Updating Rows

Updating rows using LINQ to SQL is straightforward — you retrieve the record you need to modify:

DataClasses1DataContext database = new DataClasses1DataContext();
title bookTitle = (from t in database.titles
 where (t.title_id == 'BU5555')
 select t).Single();

The Single() method returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.

Modify the field you want to change:

bookTitle.title1 = 'How to Motivate Your Staff';

And submit the changes using the SubmitChanges() method:

database.SubmitChanges();

The query can alternatively be written using the method syntax, like this:

title bookTitle =
 database.titles.Single(t => t.title_id == 'BU5555');

Deleting Rows

To delete a row, you first retrieve the row to delete:

DataClasses1DataContext database =
 new DataClasses1DataContext(); //---find author ---
var author = from a in database.authors
 where a.au_id == '789-12-3456'
 select a;

Then, locate the row to delete by using the First() method, and finally call the DeleteOnSubmit() method to delete the row:

if (author.Count() > 0) {
 database.authors.DeleteOnSubmit(author.First());
 database.SubmitChanges();
}

The First() method returns the first element of a sequence.

If you have multiple rows to delete, you need to delete each row individually, like this:

//---find author---
var authors = from a in database.authors
 where a.au_id == '111-11-1111' ||
  a.au_id == '222-22-1111'
 select a;
foreach (author a in authors) {
 database.authors.DeleteOnSubmit(a);
}
database.SubmitChanges();

So far the deletion works only if the author to be deleted has no related rows in the titleauthors and titles tables. If the author has associated rows in the titleauthors and titles tables, these examples cause an exception to be thrown because the deletions violate the referential integrity of the database (see Figure 14-21).

Figure 14-21

Because LINQ to SQL does not support cascade-delete operations, you need to make sure that rows in related tables are also deleted when you delete a row. The following code example shows how to delete a title from the titles and titleauthors tables:

DataClasses1DataContext database = new DataClasses1DataContext(); string titleid_to_remove = 'BU5555';

//---find all associated row in Titles table---
var title = from t in database.titles
where t.title_id == titleid_to_remove select t;
//---delete the row in the Titles table---
foreach (var t in title)
 database.titles.DeleteOnSubmit(t);
//---find all associated row in TitleAuthors table---
var titleauthor = from ta in database.titleauthors
where ta.title_id == titleid_to_remove select ta;
//---delete the row in the TitleAuthors table---
foreach (var ta in titleauthor)
 database.titleauthors.DeleteOnSubmit(ta);
//---submit changes to database---
database.SubmitChanges();

Summary

This chapter, provides a quick introduction to the Language Integrated Query (LINQ) feature, which is new