Lab 1: LINQ Project: Unified Language Features for Object and

Relational Queries
This lab provides an introduction to the LINQ Project. The language integrated query framework for
.NET (“LINQ”) is a set of language extensions to C# and Visual Basic and a unified programming model
that extends the .NET Framework to offer integrated querying for objects, databases, and XML.

In this lab, you will see how LINQ features can be used against in-memory collections, XML
documents, and connected databases. The lab ends with an optional exercise that looks at the various
standard query operators available for data manipulation and extraction.

Lab Objective
Estimated time to complete this lab: 60 minutes
The objective of this lab is to provide a clear understanding of the LINQ project. You will see how data
manipulation can occur on objects in memory, XML files, datasets, and relational databases. The new
LINQ APIs benefit from IntelliSense™ and full compile-time checking without resorting to string-based
queries. This lab touches on basic LINQ technologies, along with database-specific LINQ to SQL, XML-
specific LINQ to XML, and dataset-specific LINQ to DataSets. A brief look at query operators is also
included.

Page 1
Exercise 1 –LINQ for In-Memory Collections
In this exercise, you learn how to query over object sequences. Any collection supporting the
System.Collections.Generic.IEnumerable interface or the generic interface
IEnumerable<T> is considered a sequence and can be operated on using the new LINQ standard
query operators. Standard query operators allow programmers to construct queries including
projections that create new types on the fly. This goes hand-in-hand with type inference, a new feature
that allows local variables to be automatically typed by their initialization expression.

Task 1 – Creating the “LINQ Overview” Solution

1. Open Microsoft Visual Studio 2015
2. Click the File | New | Project… menu command.
3. In the New Project dialog box select the Visual C# Windows project type.
4. Select the Console Application template.
5. Provide a name for the new project by entering “LINQ Overview” in the Name box.
6. Click OK.

Task 2 – Querying a Generic List of Integers

1. In Solution Explorer, double click Program.cs
2. Create a new method that declares a populated collection of integers (put this method in the
Program class):
class Program
{
static void Main(string[] args)
{
}
static void NumQuery()
{
var numbers = new int[] { 1, 4, 9, 16, 25, 36 };
}
}

3. Add the following code to query the collection for even numbers

static void NumQuery()

{
var numbers = new int[] { 1, 4, 9, 16, 25, 36 };

var evenNumbers = from p in numbers

where (p % 2) == 0
select p;
}

4. Add the following code to display the results:

static void NumQuery()
{

Page 2
 var numbers = new int[] { 1, 4, 9, 16, 25, 36 };

var evenNumbers = from p in numbers

where (p % 2) == 0
select p;

Console.WriteLine("Result:");
foreach (var val in evenNumbers)
Console.WriteLine(val);
}

Notice that the foreach statement has been extended to use type inference as
well.

5. Finally, add a call to the NumQuery method from the Main method:
static void Main(string[] args)
{
NumQuery();
}

6. Press Ctrl+F5 to build and run the application. A console window appears. As expected all even
numbers are displayed (the numbers 4, 16, and 36 appear in the console output).
7. Press any key to terminate the application.

Task 3 – Querying Structured Types

1. In this task, you move beyond primitive types and apply query features to custom structured
types. Above the Program class declaration, add the following code to create a Customer
class:

public class Customer

{
public string CustomerID { get; set; }
public string City { get; set; }

public override string ToString()

{
return CustomerID + "\t" + City;
}
}
class Program
…

2. Within the Program class declaration, create the following new method, which creates a list of
customers (taken from the Northwind database):
static void Main(string[] args)
{
NumQuery();
}

Page 3
 static IEnumerable<Customer> CreateCustomers()
{
return new List<Customer>
{
new Customer { CustomerID = "ALFKI", City = "Berlin" },
new Customer { CustomerID = "BONAP", City = "Marseille" },
new Customer { CustomerID = "CONSH", City = "London" },
new Customer { CustomerID = "EASTC", City = "London" },
new Customer { CustomerID = "FRANS", City = "Torino" },
new Customer { CustomerID = "LONEP", City = "Portland" },
new Customer { CustomerID = "NORTS", City = "London" },
new Customer { CustomerID = "THEBI", City = "Portland" }
};
}

3. Next query the collection for customers that live in London. Add the following query method
ObjectQuery and add a call to it within the Main method (removing the call to StringQuery).
static void ObjectQuery()
{
var results = from c in CreateCustomers()
where c.City == "London"
select c;

foreach (var c in results)

Console.WriteLine(c);
}

static void Main(string[] args)

{
ObjectQuery();
}

Notice that again the compiler is using type inference to strongly type the results
variable in the foreach loop.

4. Press Ctrl+F5 to build and run the application. After viewing the results, press any key to
terminate the application.

Page 4
Exercise 2 – LINQ to SQL: LINQ for Connected Databases
This exercise begins by demonstrating that the same features available for querying in-memory
collections, xml files, and data sets, can also be applied to databases. The exercise then continues to
show more advanced features available in LINQ to SQL.
LINQ to SQL is part of the LINQ project and allows you to query and manipulate objects associated
with database tables. It eliminates the traditional mismatch between database tables and your
application’s domain specific object model, freeing you to work with data as objects while the
framework manages retrieving and updating your objects.
To create an object model of a given database, classes must be mapped to database entities. There
are three ways to create object mappings: adding attributes to an existing object, using the provided
designer to auto-generate the objects and mappings, and using the command line SQLMetal tool. This
exercise walks through the first two of these three methods.

This exercise requires the Northwind database. Please follow the instructions in the LINQ to
SQL section of the https://raw.githubusercontent.com/Microsoft/sql-server-
samples/master/samples/databases/northwind-pubs/instnwnd.sql paper to get set up with
Northwind before proceeding.

Task 1 – Adding LINQ to SQL Support

1. You need to add support for LINQ to SQL to your project. The support for this functionality is
available in the System.Data.Linq assembly. In Solution Explorer, right-click LINQ Overview |
References and choose Add Reference.
2. Choose System.Data.Linq from the list that appears.
3. Click OK.
4. In Program.cs add the following directive to use the Configuration namespace:
using System.Data.Linq;
using System.Data.Linq.Mapping;

Task 2 – Creating Object Mapping – Creating an Object and Providing Attributes

1. Add the following attributes for Customer to create the mapping to the database Customers table
that includes columns named CustomerID and City. Here you will only map two columns in the
single Customers table in Northwind.

[Table(Name = "Customers")]
public class Customer
{
[Column]
public string CustomerID { get; set; }
[Column]
public string City { get; set; }

public override string ToString()

{

Page 5
 return CustomerID + "\t" + City;
}
}

2. Return to the ObjectQuery method. As you did for in-memory collections, XML, and DataSet,
again query to find customers that live in London. Notice that minimal changes are required.
After creating a data connection you are able to get rows out of the Customers table and select
those rows for customers that live in London, returning them as IEnumerable<Customer>.

static void ObjectQuery()

{
var db = new DataContext
(@"Data Source=.\sqlexpress;Initial Catalog=Northwind");
var results = from c in db.GetTable<Customer>()
where c.City == "London"
select c;
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);
}

The DataContext object used in the ObjectQuery method is the main conduit
through which objects are retrieved from the database and changes are submitted.

3. You need to replace the connection string here with the correct string for your specific
connection to Northwind. If you’ve done the previous exercise about LINQ to DataSet, you can
find this string in the generated app.config file in the project. You will see later that after
generating strongly typed classes with the designer, it is not necessary to embed the connection
string directly in your code like this.
4. Press Ctrl+F5 to build and run the application. After viewing the results press any key to
terminate the application.
5. Now add the following line to print the generated SQL query that runs on the database:
static void ObjectQuery()
{
DataContext db = new DataContext(
@"Data Source=.\sqlexpress;Initial Catalog=Northwind");
db.Log = Console.Out;
var results = from c in db.GetTable<Customer>()
where c.City == "London"
select c;
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);

6. Press Ctrl+F5 to build and run the application. After viewing the results and the generated SQL
query, press any key to terminate the application.

Page 6
Task 3 – Creating Object Mapping – Using the Designer – Add Designer File
1. First remove the old mapping. Delete the entire Customer class.
2. Next, create objects to model the tables. Right click the LINQ Overview project and click Add |
New Item.
3. In Templates click LINQ To SQL Classes.
4. Provide a name for the new item by entering “Northwind” in the Name box
5. Click OK.

Task 4 – Creating Object Mapping – Using the Designer – Create the Object View
1. Expand Data Connections in Server Explorer.
2. Open the Northwind server (NORTHWND.MDF if you are using SQL Server Express).
3. Open the Northwind.dbml file by double clicking it in Solution Explorer.
4. From the Tables folder drag the Customers table onto the design surface.
5. From the Tables folder drag the Products table onto the design surface.
6. From the Tables folder drag the Employees table onto the design surface.
7. From the Tables folder drag the Orders table onto the design surface.
8. Press Ctrl+Shift+B to build the application. Take a look at the auto-generated mapping class,
Northwind.designer.cs. Notice a similar use of the attributes on the generated Customer class.

Task 5 – Querying using Expressions

1. Return to the program code file by double clicking on the Program.cs file in Solution Explorer.
Find the ObjectQuery method. Each table can now be accessed as a property of the db
variable. At this point, querying is almost identical to the previous exercises. Add the following
code to retrieve customers in London:
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
where c.City == "London"
select c;
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);
}

This creates a NorthwindDataContext object (which extends the DataContext class

previously used in the first task) that represents the strongly typed connection to the

Page 7
 database. As such it is important to note that there is no connection string specified
and intellisense shows all the tables specified by the designer.

2. Press Ctrl+F5 to build and run the application. After viewing the results, press any key to
terminate the application.

Six results are shown. These are customers in the Northwind Customers table with
a City value of London.

3. You also created mappings to other tables when using the designer. The Customer class has a
one-to-many mapping to Orders. This next query selects from multiple tables.
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
from o in c.Orders
where c.City == "London"
select new { c.ContactName, o.OrderID };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.ContactName, c.OrderID);
}

The select statement creates a new object with an anonymous type. The type
created holds two pieces of data, both strings with the names of the properties of the
original data (in this case ContactName and OrderID). Anonymous types are quite
powerful when used in queries. By using these types it saves the work of creating
classes to hold every type of result created by various queries.

In the preceding example, the object model can easily be seen by noticing the
object relationship shown by writing c.Orders. This relationship was defined in the
designer as a one-to-many relationship and now can be accessed in this manner.

4. Press Ctrl+F5 to build and run the application to view the results. Then press any key to
terminate the application.

Task 6 – Modifying Database Data

In this task, you move beyond data retrieval and see how to manipulate the data. The four basic data

Page 8
operations are Create, Retrieve, Update, and Delete, collectively referred to as CRUD. You see how
LINQ to SQL makes CRUD operations simple and intuitive. This task shows how to use the create and
update operations.

5. Create a new method that modifies the database data as well as a call from Main:
static void ModifyData()
{
var db = new NorthwindDataContext();

Console.WriteLine("Number Created Before: {0}",

db.Customers.Where( c => c.CustomerID == "ADVCA" ).Count());

var newCustomer = new Customer

{
CompanyName = "AdventureWorks Cafe",
CustomerID = "ADVCA"
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();

Console.WriteLine("Number Created After: {0}",

db.Customers.Where( c => c.CustomerID == "ADVCA" ).Count());
}

static void Main(string[] args)

{
ModifyData();
}

6. Press Ctrl+F5 to build and run the application. Notice that the two lines written to the screen are
different after the database is updated. Now press any key to terminate the application.

Notice that after the Add method is called, the changes are then submitted to the
database using the SubmitChanges method. Note that once the customer has been
inserted, it cannot be inserted again due to the primary key uniqueness constraint.
Therefore this program can only be run once.

7. Now update and modify data in the database. Change ModifyData to the following code that
updates the contact name for the first customer retrieved.

static void ModifyData()

{
var db = new NorthwindDataContext();

Console.WriteLine("Number Updated Before: {0}",

Page 9
 db.Customers.Where( c => c.ContactName == "New Contact" ).Count());

var existingCustomer = db.Customers.First();

existingCustomer.ContactName = "New Contact";
db.SubmitChanges();

Console.WriteLine("Number Updated After: {0}",

db.Customers.Where( c => c.ContactName == "New Contact" ).Count());

8. Now press Ctrl+F5 to build and run the application. Notice again the number of contacts with the
name “New Contact” changes. Press any key to terminate the application.

Task 7 – Expanding Query Expressions

1. So far, the queries demonstrated in this lab have been primarily based on filtering. However,
LINQ supports many options for querying data that go beyond simple filtering. For example, to
sort customers in London by ContactName, you can use the orderby clause (also be sure to set
the Main method to call ObjectQuery again):

static void ObjectQuery()

{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
where c.City == "London"
orderby c.ContactName descending
select new { c.ContactName, c.CustomerID };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.ContactName);
}

static void Main(string[] args)

{
ObjectQuery();
}

2. Press Ctrl+F5 to build and run the application. Notice the customers are sorted by the second
column, the name column, in a descending manner. Press any key to terminate the application.
3. Continue with different types of queries: write a query that finds the number of customers located
in each city. To do this make use of the group by expression.

static void ObjectQuery()

{
var db = new NorthwindDataContext();

Page 10
 db.Log = Console.Out;
var results = from c in db.Customers
group c by c.City into g
orderby g.Count() ascending
select new { City = g.Key, Count = g.Count() };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.City, c.Count);
}

4. Press Ctrl+F5 to run the application. After viewing the results , press any key to terminate the
application.
5. Often when writing queries you want to search through two tables. This is usually performed
using a join operation. In ObjectQuery replace the previous query with this one. Recall your
query printed out all orders for each customer that lives in London. This time, instead of printing
all the orders, print the number of orders per customer.

static void ObjectQuery()

{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
join e in db.Employees on c.City equals e.City
group e by e.City into g
select new { City = g.Key, Count = g.Count() };

foreach (var c in results)

Console.WriteLine("{0}\t{1}", c.City, c.Count);
}

6. Press Ctrl+F5 to run the application. Taking a look at the output, the SQL query generated can
also be seen. Press any key to terminate the application.

This example illustrates how a SQL style join can be used when there is no explicit
relationship to navigate.

Page 11