Note: Requires SQL Server Express 2008 and .NET 3.5 to run.
Introduction
This is the first part of a three-part series on using 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 first article and the Book Catalog application is to provide an introduction for beginners to LINQ on how to:
- Map your SQL Server database tables and their relationships to your objects using LINQ to SQL.
- Perform some simple LINQ queries against those objects to retrieve your data.
The article walks through each of these steps to explain how you can do this in your own applications. The attached Book Catalog application includes all of the mappings described here, along with a simple WPF GUI to display the results through data binding.
Getting Started
LINQ to SQL is an Object Relational Mapping (ORM) tool that allows you to map SQL Server databases to your classes with the .NET 3.5 framework.
Database
The Book Catalog uses a simple SQL Server Express database that contains Books, Authors, and Categories. Each book can only belong to a single category, but can have multiple authors. Each author can have multiple books. The BookAuthors table simply joins the M:M relationships between books and authors. For simplicity, none of the columns allow null values except for the Books.Category column.
This set of tables allows us to map each of the primary types of relationships (1:M, M:1, and M:M).
The rest of this article describes the steps to map an application's tables and their relationships to your classes and then use LINQ to retrieve the results. It uses these tables to provide an example of the concepts.
Application
To use LINQ to SQL, create a .NET 3.5 project and add a reference to System.Data.Linq.
Mapping a DataContext to Your Database
If you create a strongly typed DataContext to your database, it gives you a single point of entry that makes it easy to access your data. This class will handle connecting to the database and declaring each of the tables* you'll be connecting to.
*Note: You can omit the M:M join tables (e.g., BookAuthor) from your DataContext as they're only used behind the scenes to hook up M:M relationships (covered later in this article).
1. Create a class with the [Database] attribute that extends DataContext
Create a class for your database that extends DataContext, and give it the [Database] attribute to denote that this class maps to a database.
If you use a class name different than your database name, specify your database's name using the attribute's Name parameter ([Database (Name="BookCatalog")]). If the names are the same, as they are here, you can omit the Name parameter:
using System.Data.Linq.Mapping;
namespace LINQDemo
{
[Database]
public class BookCatalog : DataContext{}
}
2. Add a constructor with connection string
Add a constructor that calls base() with the database connection string to tell it how to connect to your database.
The constructor might take the connection string as a parameter, read it from a properties file, or simply hard code it, as was done in the Book Catalog demo (the string used here says to connect to the SQL Server Compact database, BookCatalog.sdf, which is in the same directory as the executable):
public BookCatalog( ) : base( "Data Source=.\\SQLEXPRESS;" +
"AttachDbFilename=|DataDirectory|\\BookCatalog.mdf;" +
"Integrated Security=True;User Instance=True" ) { }
Note that the Book Catalog includes its database, BookCatalog.sdf, inside of the project itself. This is not necessary for using LINQ to SQL, it is only done here to simplify distribution.
3. Declare your tables
Finally, represent each table* you want to connect to as a Table<T> collection of the class type that you (are about to) create for it.
Normally, you might create the classes first, but let's see what this looks like. We'll be creating three classes (Author, Book, and Category) -- one for each table. So, we'll add a Table<T> collection to each class type and name the collections something meaningful. Note that it is not necessary for the class names or the collection names to match the database table names. We'll see in the next section how to specify the table names:
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LINQDemo
{
[Database]
public class BookCatalog : DataContext
{
public BookCatalog( ) : base( ... );
public Table<Author> Authors;
public Table<Book> Books;
public Table<Category> Categories;
}
}
Mapping Entity Classes to Your Tables
Create one class for each table* you want to connect to in your application. We'll walk through this using Book as an example.
1. Create a class with the [Table] attribute
Create a Book class with the Table attribute to indicate that it maps to a database table.
Specify the database table's name in the Name parameter ([Table( Name = "Books" )]). We do this here since the table name (Books) differs from our class name (Book). If they were the same, you could omit the Name parameter.
using System.Data.Linq.Mapping;
namespace LINQDemo
{
[Table( Name = "Books" )]
public class Book{}
}
2. Add a field for the table's Primary Key using the [Column( IsPrimaryKey = true )] attribute
You can also assign Column attributes to automatic properties if you prefer, which is what the Book Catalog application does.
If your primary key is set to Identity in the database, indicate this with the parameter IsDbGenerated = true.
If you want to give your field/property a different name from the database column, use the (Name="") tag as we did for the table. Otherwise, it will take the column name from your field or property's name, as we do here with Book's primary key: Id.
[Column( IsPrimaryKey = true, IsDbGenerated = true )]
public int Id { get; set; }
3. Add your table's other non-relationship columns using the [Column] attribute
We'll come back to the relationships later. For now, let's start with the columns that are not foreign keys to other tables.
Book has two such columns: Title and Price. Use decimal for money columns and string for varchars, LINQ will automatically handle these conversions for you.
[Column] public string Title { get; set; }
[Column] public decimal Price { get; set; }
The Book Catalog application does this for Author and Category as well so that we have three classes that each map to one of our main tables:
using System.Data.Linq.Mapping;
namespace LINQDemo
{
[Table( Name = "Books" )]
public class Book
{
[Column( IsPrimaryKey = true, IsDbGenerated = true )] public int Id { get; set; }
[Column] public string Title { get; set; }
[Column] public decimal Price { get; set; }
}
[Table (Name="Authors")]
public class Author
{
[Column (IsPrimaryKey = true, IsDbGenerated = true )] public int Id { get; set; }
[Column] public string Name { get; set; }
}
[Table (Name="BookCategories")]
public class Category
{
[Column (IsPrimaryKey = true, IsDbGenerated = true )] public int Id { get; set; }
[Column] public string Name { get; set; }
}
}
Retrieving Your Data with LINQ
Once you've mapped your tables to your classes and declared them in a DataContext mapped to your database, you're able to access your data without a single line of database code.
By simply instantiating BookCatalog (the strongly typed DataContext), you get access to each book, author, and category from the database by simply using the collections defined in it: Authors, Books, and Categories.
For example, to retrieve the catalog's books, all you have to do is iterate through bookCatalog.Books and LINQ will auto-magically go out to the underlying database (via your mappings) and fill that collection for you - using the Book class to create an instance for each book it finds:
BookCatalog bookCatalog = new BookCatalog( );
foreach( Book book in bookCatalog.Books){
string title = book.Title;
decimal price = book.Price;
}
You can also use LINQ to filter these results, because quite often, you won't need all the books or want the performance hit of retrieving them all.
LINQ provides a complete set of SQL-like query syntax that has been integrated into the .NET languages (C#, Visual Basic). While describing that syntax is beyond the scope of this article, let's take a look at a couple examples to see how it's used.
Since BookCatalog returns a collection of objects, the query syntax you use will be strictly typed against those objects (meaning that you'll use the names from your objects and their fields/properties rather than the names from your database tables). So, for example, if you only want books that are less than $30, you can use a LINQ where clause to only select those books less than $30 from bookCatalog.Books:
IEnumerable<Book> cheapBooks = from book in bookCatalog.Books
where book.Price.CompareTo( 30m ) < 0
select book;
LINQ's deferred execution means the list of books is not retrieved from the database until you actually try to access the results (e.g., with a foreach loop), so you can keep applying filters, and that will limit the query that is ultimately performed against the database.
You could, for example, use LINQ's orderby to refine the above results even further, to be sorted by book title:
IEnumerable<Book> sortedBooks = from book in cheapBooks
orderby book.Title
select book;
Now, if you iterate through sortedBooks after performing this chain of LINQ queries, only a single query will be performed against the database. It will return all books that are under $30, sorted by title:
BookCatalog bookCatalog = new BookCatalog( );
IEnumerable<Book> cheapBooks = from book in bookCatalog.Books
where book.Price.CompareTo( 30m ) < 0
select book;
IEnumerable<Book> sortedBooks = from book in cheapBooks
orderby book.Title
select book;
foreach( Book book in sortedBooks ) { ... }
Let's now look at how to map relationships so that you can traverse your objects (e.g., book.Author.Name).
Adding Relationships to Your Entity Classes
This section describes how to map each type of relationship: M:1, 1:M, and then M:M. While it doesn't cover 1:1 relationships, you can map 1:1 relationships in the same way you do M:1. So, let's start there. Once again, we'll walk through these steps using Book as an example.
Mapping M:1 Relationships
Book has a many-to-one (M:1) relationship to Category because each book can only have One category (and each category can be used by Many books):
In the database, Book.catalog simply holds an ID that is a foreign key into the Category table. In your object model, however, you probably want book.Catalog to represent the actual Catalog object (not just an ID). You can do this by creating a couple of private fields that handle the behind-the scenes mapping, and then make a public property that holds the actual Category object.
1.Add a private field to hold the foreign key to the other table
Add a private field that maps to the Book.category database foreign key column.
If the field allows nulls (as this field does), use a nullable type (e.g., int?).
I named this field categoryId (to reserve the name Category for the public Category property we'll create below). This means I had to set the Name parameter in the Column attribute since my field name differs from the database column name:
[Column( Name = "Category" )] private int? categoryId;
2. Add a private EntityRef<T> backing field that references the other table
Add a private EntityRef<T> field to hold a reference to the book's actual Category instance. This will act as the backing field for our public Category property, but by using an EntityRef, it will have deferred loading (meaning that LINQ will not go against the database to retrieve the category until we ask for it).
Initialize the field to a new EntityRef instance to prevent NullReferenceExceptions in cases where you need an instance on one side of the relationship (e.g., Category) but don't have any instances on the other side (Book).
private EntityRef<Category> _category = new EntityRef<Category>( );
3. Add a public property to the associated class with the [Association] attribute
Finally, create the public Category property that will hold the actual Category instance. Give this property an Association attribute with the following parameters:
Name of the database's relationship between the two tables (in this case, FK_Books_BookCategories).IsForeignKey = true flag indicating that this class' table holds the foreign key (in the ThisKey param specified below).- Set the two parameters on this attribute that use the fields you just created:
ThisKey specifies your foreign key to the other table: categoryId.Storage specifies your EntityRef<T> that will hold the instance of the other class: _category.
Inside of the property, code the getter/setter to use _category's Entity property, which contains the actual Category instance:
[Association( Name = "FK_Books_BookCategories",
IsForeignKey = true, Storage = "_category", ThisKey = "categoryId" )]
public Category Category{
get { return _category.Entity; }
set { _category.Entity = value; }
}
Accessing Data from an M:1 Relationship
You can now access Category's data in an object oriented manner through this relationship - e.g., book.Category.Name:
foreach( var book in bookCatalog.Books ) {
string categoryName = book.Category.Name;
}
M:1 Relationships in our Join Table
Now, let's take a look at how to map the M:1 relationships in M:M Join tables. Whenever we have an M:M Join table, as we do with BookAuthors, the Join table is, of course, just two M:1 relationships.
For example, BookAuthor contains a M:1 relationship to Book and a M:1 relationship to Author in order to bridge their M:M relationship:
Unfortunately, you still need to create a class to map to this Join table. However, as with foreign keys, since it's only used for behind the scenes mapping, you can keep it out of your public interface by making the class internal.
1. Create an internal class with a [Table] attribute that maps to the Join table
Create a class for BookAuthors the same way you created the other entity classes, but don't mark this one as public:
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LINQDemo
{
[Table( Name = "BookAuthors" )]
class BookAuthor{}
}
2. Map both of the tables' M:1 relationships, denoting them as the PrimaryKey
Create an M:1 relationship to Book and an M:1 relationship to Author the same way you did for the Book:Catalog relationship above. Note that the database relationships the BookAuthors table holds are named as follows in the database:
- The BookAuthor:Authors relationship is named
FK_BookAuthors_Authors - The BookAuthor:Books relationship is named
FK_BookAuthors_Books
Add the IsPrimaryKey = true attribute on its two Column attributes to denote that the primary key for BookAuthors is made up of these two values:
[Table( Name = "BookAuthors" )]
class BookAuthor
{
[Column( IsPrimaryKey = true, Name = "Author" )] private int authorId;
private EntityRef<Author> _author = new EntityRef<Author>( );
[Association( Name = "FK_BookAuthors_Authors", IsForeignKey = true,
Storage = "_author", ThisKey = "authorId" )]
public Author Author {
get { return _author.Entity; }
set { _author.Entity = value; }
}
Column( IsPrimaryKey = true, Name = "Book" )] private int bookId;
private EntityRef<Book> _book = new EntityRef<Book>( );
[Association( Name = "FK_BookAuthors_Books", IsForeignKey = true,
Storage = "_book", ThisKey = "bookId" )]
public Book Book {
get { return _book.Entity; }
set { _book.Entity = value; }
}
}
That covers mapping M:1 relationships. Unfortunately, you can't do anything very interesting with BookAuthor yet, but we'll come back to Join tables when we discuss M:M relationships below. But first, let's take a look at the other side of our Book:Catalog relationship by seeing how to map 1:M relationships, as we'll need to know this to complete our M:M relationships.
Mapping 1:M Relationships
Adding a 1:M relationship will allow you to get a list of all the books that are in a particular Category.
1. Map the Foreign Key in the other class
Even though you're adding the association to Category, it still needs to know how to associate itself back to Book. So, you just need to make sure that your Book class has mapped its column that holds the foreign key to Category. If you're following along, then you have already added this in your 1:M mapping, so you're good to go - just make a note of the field name: categoryId, as you'll need that to make the association:
[Table( Name = "Books" )]
public class Book
{
...
[Column( Name = "Category" )] private int? categoryId;
2. Map your own primary key
LINQ will compare the Book's foreign key against Category's primary key, so you need to map Category.Id and identify it as the primary key ([Column (IsPrimaryKey = true)]). Again, if you're following along, you already did this in your entity class creation. So, just make a note of this property name: Id, as you'll also need that to make the association:
[Table (Name="BookCategories")]
public class Category
{
[Column ( IsPrimaryKey = true, IsDbGenerated = true )] public int Id { get; set; }
...
3. Add a private EntitySet<T> backing field that references the other table
Add a private EntitySet<Book> field to hold the set of books that belong to this Category. This will act as the backing field for our public Books property. Similar to EntityRef, EntitySet will cause the loading of Books to be deferred until we actually access it (so we don't have to get the list of books every time we just look at a category).
Initialize field to a new EntitySet to avoid NullReferenceExceptions in cases you don't have both sides of the relationship (e.g., a category that has no books).
private EntitySet<Book> _books = new EntitySet<Book>();
4. Add a property to the associated class
Finally, create the public Books property that will hold the books in this category. Give this property an Association attribute, and set the database relationship Name (FK_Books_BookCategories) and three parameters on this attribute to use the fields you just created:
OtherKey specifies the field in the other class (Book) that holds the foreign key to us: categoryId.ThisKey specifies your primary key (what OtherKey should match up against): Id.Storage specifies your EntitySet<T> that will be used for storing the collection of Books that the association returns: _books.
Inside of the property, code the getter/setter to use _books, which contains an ICollection of the actual Book instances:
[Association( Name = "FK_Books_BookCategories",
Storage = "_books", OtherKey = "categoryId", ThisKey = "Id" )]
public ICollection<Book> Books {
get { return _books; }
set { _books.Assign( value ); }
}
Accessing Data from a 1:M Relationship
You can now access the list of Books within each category by simply using the category.Books property:
foreach( var category in bookCatalog.Categories ){
foreach( Book book in category.Books ){
string bookTitle = book.Title;
}
}
Mapping M:M Relationships
Finally, adding M:M relationships will allow you to access all of the authors of each book directly from the Book instance, and all of the books an author has written directly from the Author instance.
You've already covered most of the work for this by creating the BookAuthor class. And, you've also already learned how to do this when you created the 1:M relationships in the prior section. Now, it's just a matter of putting it all together. Once again, we'll walk through this using Book as an example.
1. Add a private 1:M relationship from the class to the Join table
Book has an 1:M relationship to BookAuthor. That is, each book can have Many authors, but each book author references only One book. So, you just have to follow the four steps from the previous section:
- Map the Foreign Key to
Book in the BookAuthor class. You already did this, it's called: bookId. - Define the Primary Key for
Book. You already did this too, it's called: Id. - Add an
EntitySet<T> that references the other table: _bookAuthors. - Add a
BookAuthors property (make this private because it's only there to help us to get to the list of authors) with the Association attribute that sets parameters pointing back to the prior three fields.
The code to add to Book for steps 3 and 4 is as follows:
[Table( Name = "Books" )]
public class Book
{
...
private EntitySet<BookAuthor> _bookAuthors = new EntitySet<BookAuthor>( );
[Association( Name = "FK_BookAuthors_Books",
Storage = "_bookAuthors", OtherKey = "bookId",
ThisKey = "Id" )]
private ICollection<BookAuthor> BookAuthors {
get { return _bookAuthors; }
set { _bookAuthors.Assign( value ); }
}
You can do the same thing in Author (since each author can have multiple books), but replace bookId with authorId:
[Table( Name = "Authors" )]
public class Author
{
...
private EntitySet<BookAuthor> _bookAuthors = new EntitySet<BookAuthor>( );
[Association( Name = "FK_BookAuthors_Authors",
Storage = "_bookAuthors",
OtherKey = "authorId", ThisKey = "Id" )]
private ICollection<BookAuthor> BookAuthors {
get { return _bookAuthors; }
set { _bookAuthors.Assign( value ); }
}
2. Add a public property that uses LINQ to retrieve the enumeration of data through the 1:M relationship
Finally, create your public Authors property by having it retrieve the authors from your private list of book authors for this book. So, for example, if you have a LINQ In Action book that has three authors: Fabrice Marguerie, Steve Eichert, and Jim Wooley, then the LINQ In Action Book instance will contain a list of three BookAuthors:
| Book | Author |
|---|
| LINQ In Action | Fabrice Marguerie |
| LINQ In Action | Steve Eichert |
| LINQ In Action | Jim Wooley |
What you want to do is retrieve the list of authors, so the caller doesn't have to even know about this intermediate table. You can do this by using LINQ to query your private BookAuthors property and tell it to only return the authors that it has:
IEnumerable<Author> authors = from ba in BookAuthors
select ba.Author;
You can now wrap this in a public, read-only property in Book, named Authors, and (optionally) return as an ICollection by calling toList() on the results, as I've done here for the purpose of providing a more consistent public interface:
public ICollection<Author> Authors { get {
return ( from ba in BookAuthors select ba.Author ).ToList( ); } }
And, of course, you can do the same in Author to return the author's list of books:
public ICollection<Book> Books { get {
return ( from ba in BookAuthors select ba.Book ).ToList( ); } }
Accessing Data from an M:M Relationship
You can now fully traverse your object model just as you'd expect. Below are a few examples:
foreach( var book in bookCatalog.Books ) {
string title = book.Title;
decimal price = book.Price;
string category = book.Category.Name;
ICollection<Author> authors = book.Authors;
ICollection<Book> otherBooksInCategory = book.Category.Books;
}
foreach( var author in bookCatalog.Authors ) {
string name = author.Name;
ICollection<Book> books = author.Books;
}
foreach( var category in bookCatalog.Categories ){
string name = category.Name;
foreach( var book in category.Books ){
string bookTitle = book.Title;
ICollection<Author> bookAuthors = book.Authors;
}
}
Never Used/Assigned Warnings
One thing you may notice is that the interim variables you create for LINQ (the ones that only get referenced as parameters to Association attributes, but otherwise never used) will cause Visual Studio to give you warnings that these values are never used or never assigned. They are, of course, both used and assigned, but it is done behind the scenes so the compiler is unable to detect this. You can suppress these warnings, as I did in the attached code, with the pragma preprocessor directive, by placing this above each class (or field) that maps back to the database:
#pragma warning disable 0169, 0649
What's Next?
You can find the code in the attached application for all of the mappings covered here as well as the sample queries (located in LinqTutorialSampleQueries.cs). It also includes a WPF application that displays the data and allows you to traverse its relationships via WPF data binding.
And, check out parts 2 and 3 of this series to take it to the next level in your applications:
History
- 12/11/09: Added links to Parts 2 & 3 and an explanation to the Introduction (why do manually vs. auto-generating).
- 12/05/09: Updated code to make it easier to extend to handling updates/inserts/deletes:
Database:
- Set unique ID columns to be Identity.
- Renamed relationships to use a consistent naming convention.
- Set the Book.Category column to allow null.
- Changed Visual Studio project settings on DB to "Copy if Newer" so it won't overwrite data changes.
Code:
- Added params to the unique ID
[Column] attributes and the [Association] attributes to provide additional info. - Changed class properties from
IEnumerables to ICollections to provide a more flexible interface. - Changed
Book.BookAuthors and Author.BookAuthors properties to be internal. - Initializes
EntitySet and EntityRef private fields to avoid NullReferenceExceptions.
- 10/12/2009: Initial version.
