MSDN Magazine - March 2008 - (Page 23)

does not change when using it with different LINQ providers—in this case LINQ to Objects and LINQ to Entities. The First operator is very useful in the context of LINQ to Entities, especially when you know a single record will be returned from a query. For example, you might have a query that always gets a customer record when given the CustomerID. In this situation you would always have 0 or 1 records returned, so a sequence does not help you nearly as much as the entity itself. In other words, you would rather just fetch the Customer entity instead of a sequence of 1 Customer entity. This is a case where the First method can help, as shown in the code snippet that follows. (Because the Entity Framework does not try to distribute the execution of a single query across client and server, the single method is not supported in LINQ to Entities, so using the First method is an easy alternative.) using (Entities entities = new Entities()) { var query = (from c in entities.Customers where c.CustomerID.Equals(“BOLID”) select c).First(); Console.WriteLine(query.CompanyName); } Figure 1 Aggregates, Projections, and Ordering using (Entities entities = new Entities()) { var query = from c in entities.Customers where c.Orders.Sum( o => o.OrderDetails.Sum(od => od.UnitPrice)) > 0 select new { c.CustomerID, Total = c.Orders.Sum( o => o.OrderDetails.Sum(od => od.UnitPrice)) }; foreach (var item in query.OrderByDescending(x => x.Total)) Console.WriteLine(item.CustomerID + “ == “ + item.Total); } sum the total of the line items for each order, and sum the order totals for each customer. The Count operator is another aggregate standard query operator. You can determine how many customers spent more than $25,000 by using the following code: using (Entities entities = new Entities()) { var query = (from c in entities.Customers where c.Orders.Sum( o => o.OrderDetails.Sum( od => od.UnitPrice * od.Quantity)) >= 25000 select c).Count(); Console.WriteLine(query); } Aggregates, Hierarchies, and Projections Using an aggregate operator such as Sum on a LINQ to Entities query can help simplify a query. For example, the following code retrieves a sequence of orders where the order total is more than $10,000: using (Entities entities = new Entities()) { var query = from o in entities.Orders where o.OrderDetails.Sum( od => od.UnitPrice * od.Quantity) >= 10000 select o; foreach (Orders order in query) Console.WriteLine(order.OrderID); } The Max operator can be used to determine the best customer. The following code sample will return the amount that the highestspending Customer spent. It uses a combination of the Sum and the Max aggregators over multiple levels of a hierarchy: using (Entities entities = new Entities()) { var query = (from c in entities.Customers select new { c.CustomerID, Total = c.Orders.Sum( o => o.OrderDetails.Sum(od => od.UnitPrice)) }).Max(c2 => c2.Total); Console.WriteLine(query); } Because LINQ can query hierarchical entity collections, standard query operators can also be used to perform operations over the nested sequences of entities. This can be very useful when derived data must be calculated or interrogated. The derived data may only exist in its base form, such as in the case where the details of the customer orders have only values for unit price and quantity. In this case, aggregated data representing the total amount for an order is not provided at any point in the model. By applying the Sum operator to your LINQ query, however, you can still retrieve all customers who have spent more than $20,000, as shown here: using (Entities entities = new Entities()) { var query = from c in entities.Customers where c.Orders.Sum( o => o.OrderDetails.Sum( od => od.UnitPrice * od.Quantity)) >= 25000 select c; foreach (Customers customer in query) Console.WriteLine(customer.CompanyName); } This example demonstrates how you can apply the standard query operators at multiple levels of a LINQ query. The query eventually returns a sequence of Customers entities, but to get there it must first dive into each customer’s orders and each order’s order details to get the data it needs so that it can calculate each line item’s price, You also may have noticed that I snuck in a projection in the previous example. Before using the Max operator, the LINQ query does not return a list of customers. Instead, it returns a projection, which creates a new entity that contains a property for the CustomerID and a property for the Total (the customer’s entire spending amount). Projections are integral to LINQ, and when they are projected into sequences, as in the previous example just shown, they can be further processed using standard query operators. Figure 1 shows how to create a projection of a new entity that contains a CustomerID and the sum of that customer’s order totals (using the Sum operator discussed previously). Figure 1 also uses the OrderByDescending operator to order the sequence of projected entities by the calculated total. If two customers had the same total, an additional ordering operator could be used to further define the ordering. For example, the foreach statement Data Points march2008 23 Projections and Ordering

Table of Contents Feed for the Digital Edition of MSDN Magazine - March 2008

MSDN Magazine - March 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Introducing ASP.NET MVC Loosen Up CI Server Performance Office Development Test Run Security Briefs Extreme ASP.NET Foundations .NET Matters {End Bracket}

MSDN Magazine - March 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.