Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

LINQ provides a straightforward yet expressive SQL-like syntax with which you can query an Entity Data Model and uses anonymous types to return strongly-typed results.


advertisement
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap