Using LINQ to SQL ORM
Written on June 3, 2010, by Mentor Ibrahimi.

One of the hardest things i encounter when i do write LINQ queris, is that i always tend to approach results as i would in T-SQL. Well, recently i read an article about LINQ and the author said that one of the major steps a developer find hard to do, is working with LINQ without thinking of the problem in T-SQL way. Well that’s what i run yesterday, i started writing the query, and debugging, and over analyzing without giving it a clear view of what i wanted. To demonstrate that, let’s first show the model i was working against:



What i wanted to achieve is to get three most sold pizzas and three most active clients. Although the result is easier that i supposed, as i said, i tended to get them as i think from the T-SQL world. My first approach was like this:

var topPizzas = from pizza in dataContext.OrderDetails
 
.GroupBy(p => p.PizzaTypeID)
 
select new {pizza.Key, Total = pizza.Sum(p => p.Quantity)};
 
var top3Pizzas = topPizzas.Take(3);
 
var topCustomers = from customer in dataContext.Orders
 
.GroupBy(p => p.CustomerID)
 
select new {customer.Key, Total = customer.Count()};
 
var top3Customers = topCustomers.Take(3);

This way we get the right result but we get just the id of the pizza and the sum of quantity orders it was ordered. Whenever we try to project to a pizza type we will hit the wall on full speed asking our selves where is the problem, it “MUST” work this way.

But this time must come, “Wait a minute, what do i want?” and yeah the answer is top pizzas and customers and probably not some order… thing :( and the following query really must work:

var topPizzas = from pizza in dataContext.PizzaTypes
 
orderby pizza.OrderDetails.Sum(p => p.Quantity) descending
 
select pizza;
 
var top3Pizzas = topPizzas.Take(3);
 
var topCustomers = from customer in dataContext.Customers
 
orderby customer.Orders.Count() descending
 
select customer;
 
var top3Customers = topCustomers.Take(3);
 
And even a shorter version with lambda expressions looks like this:
 
var topPizzas = dataContext.PizzaTypes
 
.OrderByDescending(pizza => pizza.OrderDetails.Sum(p => p.Quantity));
 
var top3Pizzas = topPizzas.Take(3);
 
var topCustomers = dataContext.PizzaTypes
 
.OrderByDescending(pizza => pizza.OrderDetails.Sum(p => p.Quantity));
 
var top3Customers = topCustomers.Take(3);

This is probably just a first step to forgetting T-SQL queries (perhaps not DDL :P ) and looking ahead! Don’t hesitate to ask yourself what you want, the answer is always inside your self… hahaha and no i’m not from social sciences, just joking.

  • veprim

    Like it:)

blog comments powered by Disqus

1,956 views

© Copyright Phalanx Blogosphere - Powered by Wordpress - Phalanx logo is designed by Leopard Cana