Task 4 – Mapping a stored procedure

We’ve seen how to map tables to objects and how to represent relationships between tables. Now we are going to see how we can map a stored procedure to our object model.

1. In Server Explorer, expand Data Connections.

2. Open the Northwind server (NORTHWND.MDF if you are using SQL Server Express).

3. Open the Northwind.dbml file double clicking it from the solution explorer

4. From the Stored Procedures folder drag the Ten Most Expensive Products into the method pane on the right

5. Make the following changes to the Main method to use the method created by the designer.

static void Main(string[] args)

{

// If we query the db just designed we don’t need a connection string

NorthwindDataContext db = new NorthwindDataContext();

// Attach the log showing generated SQL to console

// This is only for debugging / understanding the working of LINQ to SQL

db.Log = Console.Out;

// Query for customers from London

var custs =

from c in db.Customers

where c.City == "London"

select c;

var p10 = db.Ten_Most_Expensive_Products();

foreach (var p in p10)

{

Console.WriteLine("Product Name={0}, UnitPrice={1}",

p.TenMostExpensiveProducts, p.UnitPrice);

}

Console.ReadLine();

}

6. Press F5 to debug the project

As you type the code in, notice how, in the IDE, IntelliSense is able to show the mapped stored procedure Ten_Most_Expensive_Products as a method of the strongly typed DataContext the designer has generated. Notice also that the designer has created a Ten_Most_Expensive_Product type containing two typed properties that map to the fields returned by the stored procedure.

Generating the database table relationships can be tedious and prone to error. Until Visual Studio was extended to support LINQ, there were a code generation tool, SQLMetal, you can use to create your object model manually. The final result is the same, but you need to explicit execute external code. The easiest and better option is to use the new designer completely integrated with Visual Studio. Code generation is strictly an option – you can always write your own classes or use a different code generator if you prefer.

Task 5 – Retrieving new results

So far we have run queries that retrieve entire objects. But you can also select the properties of interest. It is also possible to create composite results, as in traditional SQL, where an arbitrary collection of columns can be returned as a result set. In LINQ to SQL, this is accomplished through the use of anonymous types.

1. Modify the code in the Main method as shown to create a query that only retrieves the ContactName property:

static void Main(string[] args)

{

// If we query the db just designed we don’t need a connection string

NorthwindDataContext db = new NorthwindDataContext();

// Attach the log showing generated SQL to console

// This is only for debugging / understanding the working of LINQ to SQL

db.Log = Console.Out;

var q =

from c in db.Customers

where c.Region == null

select c.ContactName;

foreach (var c in q) Console.WriteLine(c);

Console.ReadLine();

}

2. Modify the code as shown to create a new object type to return the desired information:

static void Main(string[] args)

{

// If we query the db just designed we don’t need a connection string

NorthwindDataContext db = new NorthwindDataContext();

// Attach the log showing generated SQL to console

// This is only for debugging / understanding the working of LINQ to SQL

db.Log = Console.Out;

var q =

from c in db.Customers

where c.Region == null

select new { Company = c.CompanyName, Contact = c.ContactName };

foreach (var c in q)

Console.WriteLine("{0}/{1}", c.Contact, c.Company);

Console.ReadLine();

}

3. Press F5 to debug the application

Notice that the new operator is invoked with no corresponding type name. This causes the compiler to create a new anonymous type based on the names and types of the selected columns. Also notice that its members are named Contact and Company. Specifying explicit names is optional, the default behavior is to map members based on the source field name. Finally, notice how in the foreach statement, an instance of the new type is referenced and its properties are accessed.

Add the Employees table to our object model:

1. In Server Explorer, expand Data Connections.

2. Open the Northwind server (NORTHWND.MDF if you are using SQL Server Express).

3. Open the Northwind.dbml file double clicking it from the solution explorer

4. From the Tables folder drag the Employees table onto the designer surface

5. Change the code as follows to do a join:

static void Main(string[] args)

{

// If we query the db just designed we don’t need a connection string

NorthwindDataContext db = new NorthwindDataContext();

// Attach the log showing generated SQL to console

// This is only for debugging / understanding the working of LINQ to SQL

db.Log = Console.Out;

var ids = (

from c in db.Customers

join e in db.Employees on c.City equals e.City

select e.EmployeeID)

.Distinct();

foreach (var id in ids)

{

Console.WriteLine(id);

}

Console.ReadLine();

}

6. Press F5 to debug the solution

The above example illustrates how a SQL style join can be used when there is no explicit relationship to navigate. It also shows how a specific property can be selected (projection) instead of the entire object. It also shows how the query expression syntax can be blended with the Standard Query Operators – Distinct() in this case.