ADO.NET 3.5 Cookbook, 2nd Edition

3.28. Querying a SQL Server Database Using LINQ

Problem

You need to query a SQL Server database using a LINQ query.

Solution

Use LINQ to SQL as shown in this solution.

You first need to create a LINQ object model. Follow these steps to use the O/R Designer to generate a simple C# object model:

1. Open Visual Studio and create a console application named LinqToSql.

2. Right-click the project in the Solution Explorer pane and select Add →New Item … from the context menu to open the Add New Item dialog. Select the LINQ to SQL file template in the Data category. Change the Name to MyDataClasses.dbml. Click the Add button to close the Add New Item dialog. The empty design surface for the new dbml file appears representing the DataContext that you will configure next. LINQ to SQL files have the extension .dbml.

3. In Server Explorer, right-click on the Data Connections node and select Add Connection from the context menu to open the Add Connection dialog. Complete the dialog to create a connection to the AdventureWorks database on your SQL Server. Click OK to close the dialog and create the connection.

4. Expand the new data connection in the Data Connections node. Drag the Product (Production) table from the table subnode onto the O/R Designer design surface to create the entity class Product—the new class contains properties corresponding to columns in the Product table. Next, drag the ProductInventory (Production) table onto the design surface to create the entity class ProductInventory. Notice that an ...