Solution to Question 21-1. LINQ allows you to query several different types of data sources, including SQL Server databases, XML files, and in-memory collections.

Solution to Question 21-2. A LINQ query returns a collection that implements IEnumerable. The type of the objects in the collection is irrelevant.

Solution to Question 21-3. The select keyword returns the result of a LINQ query.

Solution to Question 21-4. You don’t need to use any special syntax to return a complex type from a LINQ query. The compiler can infer the type, even if it’s unnamed.

Solution to Question 21-5. The range variable in a LINQ query doesn’t have to be any type; it just has to be a valid C# name. The compiler will infer its type.

Solution to Question 21-6. The lambda expression in a LINQ query returns a method used to evaluate the data set. That data is projected onto the range variable.

Solution to Question 21-7. When you use LINQ to SQL, you need to add a reference to the System.Data.Linq namespace, not the System.Linq namespace that’s added by default and supports all the basic LINQ functions.

Solution to Question 21-8. Use the [Table] attribute, with the Name of the table to define a class as representing a SQL table.

Solution to Question 21-9. To add table classes in the Object Relational Designer, you must establish a connection to the database, and then simply drag the tables onto the design surface. The classes will be generated for you automatically.

Solution to Question 21-10. The constructor of the data context class using the Object Relational Designer doesn’t require any parameters; it’s generated automatically.

Solution to Exercise 21-1. For the first exercise in this chapter, we’re going to bring back the good old Box class from earlier in the book. It’s a quick and easy class, with Length, Width, and Height properties, and a quick method to display a box. Here’s the code for the class:

public class Box
{
    public int Length { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }

    public void DisplayBox( )
    {
        Console.WriteLine("{0}x{1}x{2}",
                  Length, Width, Height);
    }
}

Create a List of Box objects, at least five, with dimensions and colors of whatever you like, and then use a LINQ query to extract all those boxes with a Length and Width greater than 3.

The challenge here isn’t in creating the List; that’s simple enough. Once you have that created, you need to issue the correct query. Here’s one that works:

IEnumerable<Box> resultList =
    from myBox in boxList
    where myBox.Length > 3 && myBox.Width > 3
    select myBox;

You know you’re dealing with a collection of Box objects, so you don’t need to use an anonymous type here. Example A-62 shows the solution to this exercise.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Exercise_21_1
{
    public class Box
    {
        public int Length { get; set; }
        public int Width { get; set; }
        public int Height { get; set; }

        public void DisplayBox( )
        {
            Console.WriteLine("{0}x{1}x{2}", Length, Width, Height);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            List<Box> boxList = new List<Box>
            {
                new Box { Length = 4,
                          Width = 6,
                          Height = 2 },
                new Box { Length = 3,
                          Width = 1,
                          Height = 4 },
                new Box { Length = 5,
                          Width = 12,
                          Height = 3 },
                new Box { Length = 4,
                          Width = 7,
                          Height = 5 },
                new Box { Length = 3,
                          Width = 7,
                          Height = 1 }
            };

            IEnumerable<Box> resultList =
                from myBox in boxList
                where myBox.Length > 3 && myBox.Width > 3
                select myBox;

            Console.WriteLine("Boxes greater than 3 units in
                              length or width:");
            foreach (Box b in resultList)
            {
                b.DisplayBox( );
            }
        }
    }
}

Solution to Exercise 21-2. Use LINQ to SQL, but not the Object Relational Designer, to retrieve all the orders from the Order Details table where the quantity ordered is greater than 100. (You’ll have to use a short type for the quantity, or else you’ll get an error.)

You’re using LINQ to SQL here, but without the O/R Designer, there are some extra steps you’ll need to take. First, be sure to add a reference to the System.Data.Linq namespace, and add the appropriate using statements.

Then you’ll need to define the OrderDetails class, and give it the correct attributes:

[Table(Name = "Order Details")]
public class OrderDetails
{
    [Column] public int OrderID { get; set; }
    [Column] public int ProductID { get; set; }
    [Column] public short Quantity { get; set; }
}

Define the data context (on one line):

DataContext db = new DataContext("Data Source = .\SQLExpress;
Initial Catalog=Northwind;Integrated Security=True");

And get the table data:

Table<OrderDetails> orderDetails = db.GetTable<OrderDetails>( );

Finally, you need to create the query, which isn’t too difficult:

var dbQuery = from od in orderDetails
              where od.Quantity > 100
              select od;

Example A-63 shows the full code for this exercise.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace Exercise_21_2
{
    [Table(Name = "Order Details")]
    public class OrderDetails
    {
        [Column] public int OrderID { get; set; }
        [Column] public int ProductID { get; set; }
        [Column] public short Quantity { get; set; }
    }

    class Program
    {
        static void Main( )
        {
            DataContext db = new DataContext("Data Source = .\SQLExpress;
                    Initial Catalog=Northwind;Integrated Security=True");

            Table<OrderDetails> orderDetails = db.GetTable<OrderDetails>( );
            var dbQuery = from od in orderDetails
                          where od.Quantity > 100
                          select od;
            Console.WriteLine("Products ordered in quantities of more than 100:");
            foreach (OrderDetails od in dbQuery)
            {
                Console.WriteLine("Order #{0}	Qty: {1}	Product:{2}",
                             od.OrderID, od.Quantity, od.ProductID);
            }
        }
    }
}

Solution to Exercise 21-3. Using the Object Relational Designer, find out which employees (first and last names) have serviced orders placed by the customer named Ernst Handel.

The first thing you need to do in this exercise is right-click the project, select Add → New Item, and add the LINQ to SQL classes. In the O/R Designer, the Customer name is in the Customers table, the Employee name is in the Employees table, and they’re joined by the Orders table, so add all three of those tables to the designer.

The first thing you need to is to add the default constructor for the data context in Main( ):

DataClasses1DataContext myContext = new DataClasses1DataContext( );

Now it’s just a matter of crafting the right query. You want to work with the Orders table, so start there. Remember that from the Orders table (call it o), you can access the fields of the related tables, so o.Customer.CompanyName will let you check for all orders placed by Ernst Handel. From there, o.Employee.FirstName and o.Employee.LastName give you the names you want. The query looks like this:

var orderList =
    from o in db.Orders
    where o.Customer.CompanyName == "Ernst Handel"
    select new { o.Employee.FirstName, o.Employee.LastName };

You need to use an anonymous type for the collection because you’re returning two strings. The full code for this example is shown in Example A-64.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Exercise_21_3
{
    class Program
    {
        static void Main( )
        {
            DataClasses1DataContext db = new DataClasses1DataContext( );

            var orderList =
                from o in db.Orders
                where o.Customer.CompanyName == "Ernst Handel"
                select new { o.Employee.FirstName, o.Employee.LastName };

            Console.WriteLine("Employees who've contacted Ernst Handel:");
            foreach(var order in orderList)
            {
                Console.WriteLine("{0} {1}", order.FirstName, order.LastName );
            }
        }
    }
}