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.
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.
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:
-
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.
- 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.
|