he previous article in this series introduced the core concepts of Object Services Layer, including the steps to perform CRUD (Create, Read, Update and Delete) operations against the Entity Data Model (EDM). With that foundation, this installment focuses on using LINQ to query the EDM. Specifically, this article focuses on the expressive query capabilities of LINQ in querying the EDM through the object services layer. By using LINQ on top of the EDM, you can perform operations such as sorting, joining multiple tables, projecting result sets and so on.

	?
Figure 1. Using LINQ on top of ADO.NET Object Services Layer: The figure shows the role of the LINQ framework in the overall architecture of ADO.NET vNext.

Figure 1 shows how client applications can work with the object services layer either through LINQ or Entity SQL.

Using LINQ vs. Entity SQL
If you are using the map provider to connect to the database, the only way you can execute queries is through Entity SQL. However when using the object services layer, you can use either Entity SQL or LINQ to work with the object services layer as shown in Figure 1. So when should you use LINQ as opposed to Entity SQL? The answer depends on your requirements. For example, if you need to construct and execute queries dynamically, Entity SQL will fit the bill. Otherwise, LINQ might be a better choice because its strongly typed nature provides you with compile-time checking and Intelliesense. Note that LINQ and Entity SQL are not mutually exclusive and you can seamlessly mix-and-match LINQ and Entity SQL with no restrictions.

Retrieving Data using LINQ
In Part 2 of this series, you saw how to use the object services layer to retrieve data from the EDM. You can download the sample code to follow along. For example, the following code retrieves data from the ProductSubcategory entity in EDM:

   private void btnGetCategories_Click(object sender, EventArgs e)   {      string connectionString =         Properties.Settings.Default.AdventureWorksConnectionString;      ObjectContext context = new ObjectContext(connectionString,          "AdventureWorksModel.AdventureWorks");                  Query categories =          context.GetQuery(         "SELECT VALUE p FROM AdventureWorksModel.AdventureWorks." +         "ProductSubcategory AS p ");      lstCategories.Items.Clear();      foreach (ProductSubcategory category in categories)      {         lstCategories.Items.Add(category.Name);      }   }

One shortcoming of this approach is the late-bound manner in which the query executes against the EDM.

   Query categories =        context.GetQuery(       "SELECT VALUE p FROM AdventureWorksModel.AdventureWorks." +       "ProductSubcategory AS p ");

A brief examination of the Entity SQL in the preceding code reveals the error-prone late-bound execution of the query, meaning that the query validity is not checked at compile time. LINQ overcomes this shortcoming by letting you retrieve the data using strongly-typed object notation. Here’s the same code implemented using LINQ:

   private void btnGetCategories_Click(object sender, EventArgs e)   {      string connectionString =          Properties.Settings.Default.AdventureWorksConnectionString;                  using(AdventureWorks adventureWorks = new          AdventureWorks(connectionString))       {         var categories = from cate in adventureWorks.ProductCategory           select cate;         foreach(ProductCategory category in categories)          {            lstCategories.Items.Add(category.Name);         }      }   }   

The preceding code starts by retrieving the connection string from the app.config file.

   string connectionString =       Properties.Settings.Default.AdventureWorksConnectionString;            

	?
Figure 2. Categories from the ProductCategory Entity in the EDM: The list box displays the names of all the categories retrieved by executing the LINQ query.

Next, it specifies the LINQ query using the from..in..select syntax. The from..in..select syntax is functionally similar to a T-SQL statement in that it allows you to query the ProductCategory object. The main difference is the sequence of from..in in the beginning and select at the end, whereas in T-SQL the SELECT clause appears at the beginning.

   var categories = from cate in adventureWorks.ProductCategory      select cate;

In the preceding code, when the compiler sees the var keyword, it examines the value assigned to the variable and then dynamically determines the type. Note that var is a new keyword introduced in C# 3.0 that signals the compiler that you are using the new Local Variable Type Inference feature in C# 3.0. Figure 2 shows the output produced by the form.

Passing Parameters to a LINQ Query
With most data sources, you need to be able to filter query results based on some criteria. Just as in SQL, you use a where construct in the LINQ query to filter the resultset. Here’s an example that accepts a user-entered Product ID, then uses it to retrieve a specific product and display the product details in a list box.

   private void btnGetProductDetails_Click(      object sender, EventArgs e)   {      string connectionString =          Properties.Settings.Default.AdventureWorksConnectionString;                  int productID = System.Convert.ToInt32(txtProductID.Text);      using(AdventureWorks adventureWorks = new          AdventureWorks(connectionString))       {         var products = from prod in adventureWorks.Product                          where prod.ProductID == productID
?
Figure 3. Specific Product Details: The list box displays various attributes of a specific product including the ProductID, Name, and Product Number as the result of a parameterized query.
                        select prod;         foreach(Product prod in products)          {            lstProductDetails.Items.Add(prod.ProductID);            lstProductDetails.Items.Add(prod.Name);            lstProductDetails.Items.Add(prod.ProductNumber);                             }      }              }      

Figure 3 shows the output generated by the preceding query.

In this example, a user enters a ProductID parameter in the textbox named txtProductID, then clicks the “Get Product Details” button, which executes the query. The code stores query results in the products variable and then displays the product details in the list box.

Using Anonymous Types to Retrieve Data
The previous example used the query output to display all the attributes of the product that matched the input criteria. However you might want to retrieve only a subset of the available columns. You can accomplish this in LINQ using the new feature named “anonymous types” introduced with C# 3.0. Essentially, anonymous types let you dynamically create a new “unknown” object without a name, to which you can then add the relevant fields as properties. Suppose you want to return only the Name and ProductNumber columns from the previous query. To accomplish this, you modify the LINQ query as follows:

   private void btnGetProductDetails_Click(object sender,       EventArgs e)   {      string connectionString =          Properties.Settings.Default.AdventureWorksConnectionString;                  int productID = System.Convert.ToInt32(txtProductID.Text);      using(AdventureWorks adventureWorks = new          AdventureWorks(connectionString))       {         var products = from prod in adventureWorks.Product                          where prod.ProductID == productID                         select new                        {                          ID = prod.ProductID,                          Name = prod.Name,                          Number = prod.ProductNumber                        };          foreach(var prod in products)          {            lstProductDetails.Items.Add(prod.ID);            lstProductDetails.Items.Add(prod.Name);            lstProductDetails.Items.Add(prod.Number);          }      }    }

Look at the select clause in the preceding example, which now reads select new. Here, the new keyword causes the query to store the results in a dynamically declared, anonymous type, which has three properties: ProductID, Name and ProductNumber.

When the compiler sees the new keyword, it dynamically creates a new type. The pseudocode for this new type might look like this:

   public class ????    {      private string name;      private string productNumber;           public string Name      {          get { return name; }         set { name= value; }      }         public string ProductNumber      {         get { return productNumber; }         set { productNumber = value; }      }   }

As you can see, this dynamically created class is nameless?meaning that it is an anonymous type. Here are some key advantages of using anonymous types:

Anonymous types let you create as many domain-specific representations as you need. They also obviate the need to create a fully populated data object when you only want to access a subset of its attributes, resulting in a smaller memory footprint.

Anonymous types are type-safe, meaning that they are still validated at the compile time.

Anonymous types are flexible because you can structure a class based on the data returned from a query. For example, if you have a query that goes against employee and department tables and you want to return only a subset of columns from those tables, you can use anonymous types to define an exact structure that represents the desired query results. You can accomplish all of this without having to define and instantiate a new class explicitly.

The ability to create anonymous types on the fly really brings a whole new dimension when you are working with databases. Anonymous types free you from the restrictions of pre-defined queries inherent in traditional object-relational mappers; instead, you are free to create any type of object on the fly without any restrictions.

Using “LINQ to XML” to Create Hierarchical Data
In addition to working with relational data, LINQ also provides you with a dedicated API to manipulate hierarchical XML data. This API known as “LINQ to XML” (code-named XLinq) can be very useful in converting relational data representations into hierarchical data representations and vice versa. Here’s an example that shows how you can leverage XLinq to convert relational data into an XML (hierarchical) representation.

   private void btnGetCategories_Click(object sender, EventArgs e)   {      string connectionString =          Properties.Settings.Default.AdventureWorksConnectionString;        using(AdventureWorks adventureWorks = new          AdventureWorks(connectionString))       {                         XElement categories = new XElement("Categories",              from category in adventureWorks.ProductSubcategory              select              new XElement("Category",             new XAttribute("ID", category.ProductSubcategoryID),             new XAttribute("Name", category.Name)          ));          MessageBox.Show(categories.Xml);       }              }

The preceding code starts by creating an instance of the XElement class, which is a key object introduced with LINQ to XML. You pass the element’s name to the XElement constructor as well as all the child elements. If you examine the XElement constructor, it look like this:

   public XElement(XName name,       params object[] content);

Because of the params keyword, you can supply a variable number of arguments (as child elements) to XElement object’s constructor.

For more information on XLinq, please refer to my XLinq article series.

Advantages of Using LINQ to Retrieve Data

• Using LINQ gives you the power to execute complicated queries using a simple and probably at least somewhat familiar syntax.
• By representing entities as a set of objects in LINQ, you can work with objects that directly represent the domain model rather than only with data structures in the same format as the relational database (such as DataReaders).
• The LINQ data access mechanism lets you create a high quality, flexible data access layer that supports compile-time checking, is easy to program, debug, and maintain.

This article discussed the role of LINQ in the ADO.NET vNext Framework and showed how to execute LINQ queries against the object services layer from your .NET client applications. Specifically, you saw the LINQ query required to retrieve data from the EDM entities via the object services layer. You have also seen the code required to filter rows and columns returned from the query results. In addition you have also understood the role of anonymous types in creating flexible .NET applications.