- Introduction
- Defining an Entity Data Model
- Querying Customer Orders
- Saving Changes
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Querying Customer Orders
LINQ is a query language that looks like SQL. The biggest difference is that from comes first and select comes last. The from clause sets the context to aid with Intellisense. To query the Customers table, you'll need to create an instance of the NorthwindEntities class (that was code-generated), and request data by referring to the objects—Customers, Orders, Order_Details, and Products—defined by your EDM.
The following fragment queries Customers that start with TEST as the CompanyName.
var customers = from customer in northwind.Customers
where customer.CompanyName.StartsWith("TEST")
select customer;
The query is understood to mean "Define a range variable customer from northwind.Customers. Select only those customers whose first four characters in the CompanyName are TEST." The last part of the query is the select clause, which accumulates the results into an ObjectQuery collection of NorthwindModel.Customers; that is, a collection of Customers. The left-hand side of the equals (=) operator beginning with var customers is referred to as an anonymous type. (Anonymous types are strong types whose actual type is emitted by the compiler at compile time.
You could write the CustomerIDs using the Array.ForEach statement and a Lambda expression as follows:
Array.ForEach(customers.ToArray(), c => Console.WriteLine(c.CustomerID));
Array.ForEach is shorthand notation for a foreach statement, and the Lambda expression c=> Console.WriteLine(c.CustomerID) is shorthand for an anonymous function. The c => (goes-to) part represents the function header, and the statement on the right-hand side of the goes-to operator represents the function body.
Loading Customers and Orders
Because you included Customers, Orders, Order_Details, and Products, the Entity Framework engine generated relationships. For example, Orders are represented in generated code as a property of Customers whose type is an EntityCollection of Orders. This means that you can query Customers and request that Orders be loaded as well (see Listing 1).
Listing 1 All the code you would need to load the Alfreds Futterkiste orders and display the OrderIDs.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NorthwindModel;
namespace LinqToEntities
{
class Program
{
static void Main(string[] args)
{
NorthwindEntities northwind = new NorthwindEntities();
var alfkiOrders = from customer in northwind.Customers
where customer.CustomerID == "ALFKI"
select customer;
Array.ForEach(alfkiOrders.ToArray(), c=>
{
c.Orders.Load();
Array.ForEach(c.Orders.ToArray(),
o => Console.WriteLine(o.OrderID));
});
Console.ReadLine();
}
}
}
Notice that a reference to the NorthwindModel namespace was added. Next, the NorthwindEntities class is created. This, in conjunction with the connection string in the App.Config, represent your connection to the underlying database. You don't have to write the SQL or ADO.NET 2.0 code. Next, you write a very natural-looking query with the where clause, filtering only those customers you want. Finally, the Array.ForEach statement calls Orders.Load, loading the orders for each customer, and the OrderIDs are written to the console. (In this instance, there is only one customer, Alfreds Futterkiste.)
That's all there is to it.
Using SPAN Technology
SPAN is a capability of the ADO.NET Entity Framework to get related data in one roundtrip. Instead of calling Load after the LINQ query, you could rewrite the query in Listing 1 to include the Orders and drop the independent Load in the for loop. Here is the rewritten LINQ query:
var alfkiOrders = from customer in northwind.Customers.Include("Orders")
where customer.CustomerID == "ALFKI"
select customer;
In the revised query, the Orders are queued up to be retrieved with the Customers.