Microsoft introduced LINQ to SQL with .NET Framework 3.5 to reduce the work that a developer must perform when accessing a database. LINQ to SQL makes it much easier to write both simple and complex database-driven websites.Writing data access code is difficult because it forces you to bridge two very different universes: the object universe and the relational universe. Your application logic (your C# or Visual
Basic .NET code) inhabits the object universe, whose basic elements are classes and objects. The basic elements of the relational universe, on the other hand, are tables and rows.You interact with the two universes by using two very different languages.
The C# and Visual Basic .NET languages are used for working with objects, and the SQL language is designed for working with tables. If you want to communicate from the object universe to the relational universe, you’re forced to embed SQL strings in your C# or Visual Basic .NET code. These SQL strings are complete gibberish from the point of view of your C# or Visual Basic .NET application. So how do you bridge this divide? LINQ to SQL bridges this divide by enabling a developer to pretend that the relational universe doesn’t exist. LINQ to SQL enables you to write all of your data access code by using C# or Visual Basic .NET. You let C# or Visual Basic .NET worry about how to translate your code into SQL in the background.
- LINQ to Objects
- LINQ to Amazon
- LINQ to Entities
- LINQ over DataSets
- LINQ to XML
- LINQ to Flickr
- LINQ to LDAP
- LINQ to SQL
These various flavors of LINQ enable you to communicate with different data sources. For example, LINQ to Flickr enables you to perform queries against photos stored at the Flickr website. In this article, we’re concerned with LINQ to SQL, which is the flavor of LINQ that you’ll most likely use when communicating with a Microsoft SQL Server database. (Currently, LINQ to SQL works with Microsoft SQL Server only, and not with other databases such as Oracle or Access.) To use LINQ to SQL, your web project must target .NET Framework 3.5. New websites created with Visual Studio 2008 target .NET Framework 3.5 by default, but you can target an existing application to use .NET Framework 3.5 within Visual Studio:
- Right-click the name of your project in the Solution Explorer window.
- Select Property Pages.
- Click Build.
- Select .NET Framework 3.5 from the drop-down Target Framework list (see Figure 1). Behind the scenes, performing this action modifies your web.config file so that it contains references to the right assemblies and uses the correct version of either the C# or Visual Basic .NET language.
Creating LINQ to SQL Entities Before you can start using LINQ to SQL in your ASP.NET application, you must first create your LINQ to SQL entities. A LINQ to SQL entity is a C# or Visual Basic .NET class that represents an entity from your database. For example, if your database contains a table named Products, you’ll create a LINQ to SQL entity named Product that represents each product from the Products database table.
The Product class will include a property that corresponds to each column in your database table. Visual Studio 2008 makes it easy to create LINQ to SQL entities. You create LINQ to SQL entities by using the Visual Studio Object Relational Designer (see Figure 2). To create new entities, simply drag database tables from the Server Explorer/Database Explorer window onto the Object Relational Designer.
Let’s assume that your database contains the following table named Products:
Column Name Column Type
Id Int (identity, primary key)
Name Nvarchar(50)
Price Money
Follow these steps to create a new LINQ to SQL entity that represents this database table:
- From the menu, select Website > Add New Item.
- In the Add New Item dialog box, select LINQ to SQL Classes.
- In the Name text box, type Store.dbml (see Figure 3).
- Click Add.
- When a warning message appears, suggesting that the LINQ to SQL classes be added to your App_Code folder, succumb to the suggestion and click Yes.
- When the Object Relational Designer appears, drag one or more tables onto the Designer surface from the Server Explorer/Database Explorer. After you drag the Products table onto the Designer surface, you’ll have a new entity named Product. (Visual Studio 2008 changes the name from Products to Product automatically.)
- The new Product entity includes a property for each of the columns in the underlying database table. You can view information about each entity property by selecting it and looking in the Properties window. For example, Figure 4 shows the values for the Id property.
Notice that Visual Studio has detected that the Id property represents a primary key and identity value automatically. In the property sheet in Figure 4, both the Primary Key property and the Auto Generated Value property have the value True. If Visual Studio ever gets this setting wrong, you can change these properties manually. For example, I add a column to all of my database tables that has a default value of GetDate(). That way, every time I add a new row to the table, the row gets a date and time stamp automatically. However, the Object Relational Designer doesn’t recognize columns with a default value as being auto-generated. Therefore, I always end up changing the Auto Generated Value property manually for these types of columns.
Behind the scenes, the Object Relational Designer is generating classes that represent the LINQ to SQL entities. You can view these classes by expanding the Store.dbml file and opening the Store.designer.cs or Store.designer.vb file.
Executing LINQ to SQL Queries After you create one or more LINQ to SQL entities by using the Object Relational Designer, you can start performing LINQ to SQL queries. You can write a LINQ to SQL query using either method syntax or query syntax. Let’s start with method syntax. Suppose that you want to retrieve a set of entities representing all of the rows from the Products database table. You can use the code in Listing 1.
Listing 1 Product.cs (method syntax).
using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
public partial class Product
{
public IEnumerable<product> Select()
{
StoreDataContext db = new StoreDataContext();
return db.Products;
}
}
The Select() method in Listing 1 returns all of the products from the Products database
table. The method consists of two lines of code. The first line of code instantiates
an instance of the StoreDataContext class. You created the StoreDataContext class
when you created the LINQ to SQL entities. Next, the Products property of this class
is used to return the products.Notice that Listing 1 contains the definition for a class named Product and that this class is declared as a partial class. The other half of the partial class is contained in the Store.Designer.cs file that’s generated by the Object Relational Designer.
When using LINQ to SQL, you must be careful to import all of the necessary namespaces. You should always import the System.Linq and System.Data.Linq namespaces. If you don’t import these namespaces, the LINQ methods won’t be available.
If you want your data access code to resemble SQL code more closely, you can use query syntax instead of method syntax. The class in Listing 2 does the same thing as the class in Listing 1. However, this new class uses query syntax instead of method syntax.
Listing 2 Product.cs (query syntax).
using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
public partial class Product
{
public IEnumerable<product> Select()
{
StoreDataContext db = new StoreDataContext();
return from p in db.Products select p;
}
}
The class in Listing 2 is very similar to the previous class. The only difference
is that this new class uses the expression from p in db.Products select p to retrieve
the products. Whether you use method syntax or query syntax is entirely a matter
of personal preference. There’s no performance difference between the two methods.
If one type of syntax seems more natural to you, use it.
If you want to use either the class in Listing 1 or the class in Listing 2 to display
the products in an ASP.NET page, you can use an ObjectDataSource control to represent
the class. For example, the page in Listing 3 displays all of the products in a
GridView control by binding the GridView to an ObjectDataSource control that represents
the Product class.
Listing 3 ShowProducts.aspx.Listing 3 ShowProducts.aspx.you view the page in Listing 3 in a web browser, you’ll see the rendered content contained in Figure 5.
It’s important to pause here for a moment in order to notice how simple LINQ to SQL makes accessing database data. You didn’t need to open a database connection or set up a command object. In fact, you didn’t write any ADO.NET or SQL code at all. LINQ to SQL reduced your data access code to its bare essentials.
Think of how much time you could save by taking advantage of LINQ to SQL when writing a database-driven web application!
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).
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 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.
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.
Inserting Data with LINQ to SQLIn this final section, we’ll examine how you can use LINQ to SQL to modify database data. In this section, you’ll build a page that you can use to insert new records into the Products database table.
When using LINQ to SQL, you insert new records by calling the InsertOnSubmit() method. After calling the InsertOnSubmit() method, you must call SubmitChanges() to make the insertion happen. The SubmitChanges() method executes all of the database commands that have been queued. The modified Product class in Listing 7 illustrates how to write a method that inserts new products into the Products database table.
Listing 7 Product.cs with Insert method.
using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
public partial class Product{
public IEnumerable
Select() {
StoreDataContext db = new StoreDataContext();
return from p in db.Products select p;
}
public void Insert(Product newProduct) {
StoreDataContext db = new StoreDataContext();
db.Products.InsertOnSubmit(newProduct);
db.SubmitChanges();
}
}
The ASP.NET page in Listing 8 uses the Product class. The page contains a GridView
control and a FormView control. When you enter a new product with the FormView control,
the product is added to the database and displayed in the GridView (see Figure 8).Listing 8 ShowInsert.aspx.
<asp:formview id="Formview1" datasourceid="srcProducts" defaultmode="Insert" runat="server">
<InsertItemTemplate>
<asp:Label ID="lblName" Text="Name:" AssociatedControlID="txtName" runat="server" />
<br />
<asp:TextBox ID="txtName" Text='<%# Bind("Name") %>' runat="server" />
<br />
<br />
<asp:Label ID="lblPrice" Text="Price:" AssociatedControlID="txtPrice" runat="server" />
<br />
<asp:TextBox ID="txtPrice" Text='<%# Bind("Price") %>' runat="server" />
<br />
<br />
<asp:DropDownList ID="ddlCategories" DataSourceID="srcCategories" DataTextField="Name"
DataValueField="Id" AutoPostBack="true" runat="server" />
<asp:ObjectDataSource ID="srcCategories" TypeName="KannanTestApplication.LINQSamples.Category"
SelectMethod="Select" runat="server" />
<asp:Button ID="btnInsert" Text="Insert Product" CommandName="Insert" runat="server" />
</InsertItemTemplate>
</asp:formview>
<asp:gridview id="Gridview1" datasourceid="srcProducts" runat="server" />
<asp:objectdatasource id="srcProducts" typename="KannanTestApplication.LINQSamples.Product"
dataobjecttypename="KannanTestApplication.LINQSamples.Product" selectmethod="Select"
insertmethod="Insert" runat="server" />
To keep things simple, I’ve left out any validation from the page in Listing 8. In real life, at the very least, you would want to associate RequiredFieldValidator controls with both the txtName and txtPrice TextBox controls.
Notice how much code you avoid writing when using LINQ to SQL. You don’t need to get your hands dirty by touching any ADO.NET objects. You never need to set up a database connection or command. All of the low-level plumbing is handled for you in the background by LINQ to SQL.
Conclusion The purpose of this article was to provide a very brief introduction to using LINQ to SQL when building a database-driven ASP.NET application. My hope is that the sample code in this article has convinced you that using LINQ to SQL can dramatically reduce the amount of code you need to write when building
database-driven web applications
Happy Programming!!!