Introduction to LINQ - Page 3
       by Granville Barnett  |  15 September 2007

In the previous page, you created two variations of a simple application using LINQ. While those examples serve to give you a quick intro to what LINQ has to offer, in this page, let's look at a more involved example that is more representative of a real-world situation.

LINQ for SQL
Formerly named LINQ for Relational Data (DLINQ), LINQ for SQL allows us to interact with a conceptual view of our database. Before we look at LINQ for SQL we must first talk a little about entities, and the tools that exist to automate the generation of our conceptual database model.

Defining Entities
An entity by definition is something that is distinct and exists as a separate existence, in LINQ for SQL, entities are defined using custom types and attributing those types with a special new set of attributes included in the LINQ for SQL namespace, System.Data.DLinq.

Using attributes we can associate an entity with a table in our database, we can also use attributes to define relationships and entity hierarchies amongst other things:

[ Simple books database schema (can be downloaded from gbarnett.org) ]

In the code snippet below we use attributes to associate a type of Author with the Authors table in our database (see above image); we also define a property AuthorID and associate it with the corresponding column in the Authors table. Of particular significance is the use of DBType in the Column attribute – this has been introduced because not all data types in SQL Server map directly to a CLR type.

To associate an entity with a relation in our database, use the following:

[Table(Name="Authors")]

public partial class Author

{

// ...

[Column(Storage="_AuthorID", DBType="Int NOT NULL IDENTITY", Id=true, AutoGen=true)]

public int AuthorID

{

// ...

}

// ...

}

The demo database schema shown in the following describes a few relationships; we can define these exact relationships at our conceptual layer:

[ DLINQ Objects designer ]

In the System.Data.DLinq namespace there are two generic types which allow us to express relationships between entities, these are:

1. EntityRef<TEntity>
   In the Books table we have a 1:1 relationship with a record in the Publishers table; to define this at our conceptual layer we create an attribute of type EntityRef<Publishers>, the implication being that each Book entity has a single reference to a Publisher entity.
2. EntitySet<TEntity>
   In the sample schema, one book can have many authors (1..*). To define this at our conceptual layer we create an attribute of type EntitySet<Authors>. We imply that for every Book entity there is an associated set of Author entities.

Like properties we use attributes to associate any relationships in our conceptual layer with relationships at the database schema level, the only difference being that we associate the property in our entity with the relationship constraint in our database schema:

// ...

[Association(Name="FK_Books_Publishers",

             Storage="_Publisher",

             ThisKey="PublisherID",

             IsParent=true)]
 

public Publisher Publisher

{

// ...

}

// ...

For more information on attributes used to define relationships between entities refer, again, to the official LINQ project site. In the next (and last!) page, let's look at using Visual Studio's built-in tools to make our tasks a bit easier.

Onwards to the next page.

1 | 2 | 3 | 4