In Part 2 of this series I walked through how to create a LINQ
to SQL class model using the LINQ to SQL designer that is built-into VS
2008. Below is a class model created for the Northwind sample
database and which I'll be using in this blog post:
Figure 1
When we designed our data model using the LINQ to SQL data
designer above we defined five data model classes: Product, Category, Customer,
Order and OrderDetail. The properties of each class map to the columns of
a corresponding table in the database. Each instance of a class
entity represents a row within the database table.
When we defined our data model, the LINQ to SQL designer
also created a custom DataContext class that provides the main conduit by which
we'll query our database and apply updates/changes. In the example data
model we defined above this class was named
"NorthwindDataContext". The NorthwindDataContext class has
properties that represent each Table we modeled within the database (specifically:
Products, Categories, Customers, Orders, OrderDetails).
As I covered in Part 3 of this blog series, we can easily use LINQ syntax
expressions to query and retrieve data from our database using this
NorthwindDataContext class. LINQ to SQL will then automatically translate
these LINQ query expressions to the appropriate SQL code to execute at runtime.
For example, I could write the below LINQ expression to
retrieve a single Product object by searching on the Product name:
Figure 2
I could then write the LINQ query expression below to
retrieve all products from the database that haven't yet had an order placed
for them, and which also cost more than $100:
Figure 3
Note above how I am using the "OrderDetails"
association for each product as part of the query to only retrieve those
products that have not had any orders placed for them.
