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
