ADO.NET vNext Part 3: Querying with LINQ : Page 2

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<ProductSubcategory> categories = 
         context.GetQuery<ProductSubcategory>(
         "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<ProductSubcategory> categories = 
       context.GetQuery<ProductSubcategory>(
       "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.

Previous Page

Next Page