- Understanding LINQ
- Creating LINQ to SQL Entities
- Executing LINQ to SQL Queries
- Creating a Master/Detail Page with LINQ to SQL
- Inserting Data with LINQ to SQL
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Creating a Master/Detail Page with LINQ to SQL
The database query examined in the previous section was very simple; we just grabbed all of the rows from the table. But what if you need to perform a more complicated query? For example, how do you filter and order the results of a LINQ to SQL query in the same way as when performing a traditional SQL query? In this section, we’ll create a single-page master/detail form that illustrates how you can both filter and sort the results of a LINQ to SQL query.
- The first step is to add a new database table to our project. I’m
going to assume that the project contains a database table named
Categories that looks like this:
Column Name
Column Type
Id
Int (identity, primary key)
Name
Nvarchar(50)
The Categories table contains product category names such as Beverages, Meat, Cheese, and Other.
- To create a relationship between the Categories and
Products tables, add a new column to the Products table that
associates each product with a category. The modified Products table
looks like this:
Column Name
Column Type
Id
Int (identity, primary key)
Name
Nvarchar(50)
Price
Money
CategoryId
Int (NULL)
- Next, we need to re-create the LINQ to SQL entities so that they correctly reflect the modified database objects. Start by opening the Store.dbml file in the Object Relational Designer by double-clicking the Store.dbml file in the Solution Explorer window.
- Delete the Product entity from the Object Relational Designer so that you can start with a blank slate.
- Drag both the Products and Categories database tables onto the Object Relational Designer from the Server Explorer/Database Explorer window.
After you complete these steps, the Object Relational Designer displays two entities corresponding to the Products and Categories table (see Figure 6).
)
Figure 6 The Product and Category LINQ to SQL entities.
Now that we’ve modified the database and updated the entities, we’re ready to write some code to retrieve the categories and products. The code in Listing 4 contains a partial class named Category that includes a method for retrieving all of the categories.
Listing 4 Category.cs.
using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
public partial class Category
{
public IEnumerable<Category> Select()
{
StoreDataContext db = new StoreDataContext();
return from c in db.Categories select c;
}
}
There’s nothing new in the Category.cs class. The class contains a method named Select() that uses LINQ to SQL query syntax to retrieve all of the categories from the underlying Categories database table.
Listing 5 contains a class for retrieving products. This class contains a method named SelectByCategory() that retrieves products matching a certain category. The products are returned in order of price.
Listing 5 Product.cs with SelectByCategory method.
using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
public partial class Product
{
public IEnumerable<Product> SelectByCategory(int categoryId)
{
StoreDataContext db = new StoreDataContext();
return from p in db.Products
where p.CategoryId == categoryId
orderby p.Price
select p;
}
}
The LINQ to SQL query in Listing 5 contains from, where, orderby, and select clauses. You should be familiar with these clauses from writing traditional SQL queries. The only weird thing is the order of these clauses. When writing LINQ to SQL queries, you must get used to adding the select clause at the end of the query rather than at the beginning of the query.
Finally, Listing 6 contains an ASP.NET page that takes advantage of both the Category and Product classes. The page displays a drop-down list of categories. When you select a category from the list, any matching products are displayed by a GridView control.
Listing 6 MasterDetail.aspx.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Master Detail</title>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList
id="ddlCategories"
DataSourceID="srcCategories"
DataTextField="Name"
DataValueField="Id"
AutoPostBack="true"
Runat="server" />
<asp:ObjectDataSource
id="srcCategories"
TypeName="Category"
SelectMethod="Select"
Runat="server" />
<br /><br />
<asp:GridView
id="grd"
DataSourceID="srcProducts"
Runat="server" />
<asp:ObjectDataSource
id="srcProducts"
TypeName="Product"
SelectMethod="SelectByCategory"
Runat="server">
<SelectParameters>
<asp:ControlParameter
Name="categoryId"
ControlID="ddlCategories" />
</SelectParameters>
</asp:ObjectDataSource>
</form>
</body>
</html>
The page in Listing 6 enables you to select a category and display a list of matching products (see Figure 7). In other words, it contains a simple master/detail form.
)
Figure 7 A simple master/detail form.