LINQ to SQL (Part 3 – Querying our Database)

The third part from Scott Guthrie’s series LINQ to SQL:

Last month I started a blog post series covering LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapping) framework that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes. You can then use LINQ expressions to query the database with them, as well as update/insert/delete data from it.

Below are the first two parts of my LINQ to SQL series:

In today’s blog post I’ll be going into more detail on how to use the data model we created in the Part 2 post, and show how to use it to query data within an ASP.NET project.

Northwind Database Modeled using LINQ to SQL

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 the class model that we created for the Northwind sample database:

step1 LINQ to SQL (Part 3   Querying our Database)

Retrieving Products

Once we have defined our data model classes above, we can easily query and retrieve data from our database. LINQ to SQL enables you to do this by writing LINQ syntax queries against the NorthwindDataContext class that we created using the LINQ to SQL designer above.

For example, to retrieve and iterate over a sequence of Product objects I could write code like below:

step1 LINQ to SQL (Part 3   Querying our Database)

In the query above I have used a “where” clause in my LINQ syntax query to only return those products within a specific category. I am using the CategoryID of the Product to perform the filter.

One of the nice things above LINQ to SQL is that I have a lot of flexibility in how I query my data, and I can take advantage of the associations I’ve setup when modeling my LINQ to SQL data classes to perform richer and more natural queries against the database. For example, I could modify the query to filter by the product’s CategoryName instead of its CategoryID by writing my LINQ query like so:

step2 LINQ to SQL (Part 3   Querying our Database)

Notice above how I’m using the “Category” property that is on each of the Product objects to filter by the CategoryName of the Category that the Product belongs to. This property was automatically created for us by LINQ to SQL because we modeled the Category and Product classes as having a many to one relationship with each other in the database.

read original

Leave a Reply

Your email address will not be published. Required fields are marked *