Walkthrough: Create Adventure Works Sales Model and Query (C#)

This walkthrough provides a fundamental end-to-end LINQ to Entities scenario with minimal complexities. You will create the AdventureWorks Sales Model, which contains entities and associations based on the Address, Contact, Product, SalesOrderHeader, and SalesOrderDetail tables in the sample AdventureWorks database. You will then create a simple query to list all the red products in the AdventureWorks inventory.

Prerequisites

This walkthrough requires the AdventureWorks sample database. If you do not have this database on your development computer, you can download it from the Microsoft SQL Server Community & Samples page on the CodePlex site.

Overview

This walkthrough consists of four main tasks:

• Creating a LINQ to Entities solution in Visual Studio 2008.

• Creating the AdventureWorks Sales Model.

• Creating a simple query to run against the Sales model.

• Executing the query and observing the results.

Creating a LINQ to Entities Solution

In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to Entities project.

To create a C# LINQ to Entities solution

1. On the Visual Studio File menu, point to New, and then click Project.

2. In the Project types pane of the New Project dialog box, click Visual C#.

3. Select .NET Framework 3.5 from the drop-down menu.

4. In the Templates pane, click Console Application.

5. In the Name box, type LinqToEntitesConsoleApp.

6. In the Location box, select a location for your project files, and then click OK.

7. On the Project menu, click Add Reference, click the .NET tab, click the System.Data.Entity assembly, and then click OK.

8. Add a using directive for the System.Data.Objects namespace to your source code file.

Creating the AdventureWorks Sales Model

In this task, you create the AdventureWorks Sales Model using the Entity Data Model Wizard and reference the model in your project.

To create and reference the AdventureWorks Sales Model

1. On the Project menu, click Add new item.

2. In the Templates pane of the Add New Item dialog box, select ADO.NET Entity Data Model. Name the model AdventureWorksSalesModel and then click Add.

3. in the Choose Model Contents dialog box, select Generate from database and then click Next.

4. In the Choose Your Data Connection window, either select an existing AdventureWorks connection from the list or create a new connection to an instance of SQL Server that has the AdventureWorks sample database.

5. Save the entity connection settings in App.Config as AdventureWorksEntities and then click Next.

6. In the Choose Your Database Objects dialog box, clear all objects, expand Tables, and select the following table objects:

   • Address (Person)

   • Contact (Person)

   • Product (Production)

   • SalesOrderDetail (Sales)

   • SalesOrderHeader (Sales)

7. Name the model namespace AdventureSalesWorksModel and then click Finish.

8. The Model Browser view opens, displaying the entities in the AdventureWorks Sales Model. Click the save button and select the Program.cs tab to navigate back to the source code.

9. Add a using statement for AdventureWorksSalesModel to your source file.

Creating a Simple Query

In this step, you create a query to find the red products from the AdventureWorks inventory. The query variable only stores the query commands if the query is designed to return a sequence of values. The actual execution of the query is deferred until you iterate over the query variable in a foreach loop. This approach is known as deferred execution. For more information, see Query Execution.

To create a simple query

• Type or paste the following code into the Main method.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;

    IQueryable<Product> query =
        from product in products
        where product.Color == "Red"
        select product;

    foreach (Product product in query)
    {
        Console.WriteLine("Name: {0}", product.Name);
        Console.WriteLine("Product number: {0}", product.ProductNumber);
        Console.WriteLine("List price: ${0}", product.ListPrice);
        Console.WriteLine("");
    }
}

//Prevent the console window from closing.
Console.WriteLine("Hit Enter...");
Console.Read();

Executing the Query

In this step, you actually execute the query. The query expression you created in the previous step is not evaluated until the results are needed. When you start the foreach iteration, the query is executed against the AdventureWorks Sales Model and the results are materialized.

To execute the query

1. Press F5 to execute the application in debug mode.

2. The query results should appear in the console window.

3. Press ENTER in the console window to close the application.

See Also

Tasks

How to: Use the Entity Data Model Wizard (Entity Framework)

Concepts

LINQ to Entities Overview