Chaining Calls in Linqpad Reply

Joseph DotsonOne the great benefits of Linqpad is the ability to iterate through a problem, taking things one step at a time through the use of chaining calls. The below query is one such example. The problem is to get a list of the best sales people per year. The database used is the adventureworks sample database.

I set the connection in Linqpad to not pluralize tables as well as to not capitalize field names:

var salesPeople =
Person
.Select(x => new { Name = x.FirstName + + x.LastName, x.BusinessEntityID });
SalesOrderHeader
.Where(x => x.OnlineOrderFlag == false)
.GroupBy(x => new { x.OrderDate.Year, x.SalesPersonID })
.Select(x => new {
Key = x.Key,
Sales = x.Sum(y => y.SubTotal)
})
.Select(x => new { Year = x.Key.Year, SalesPerson = salesPeople.FirstOrDefault(y => y.BusinessEntityID == x.Key.SalesPersonID).Name, Sales = x.Sales })
.OrderByDescending(x => x.Sales)
.GroupBy(x => x.Year)
.OrderBy(x => x.Key)
.Dump();

The first portion of the code sets the salesPeople variable with the ID and name of all people in the database. I am using a select to return an anonymous object with **only** the fields that I want.

The second statement starts off with a where statement to filter out all online orders, as these orders do not have a sales person. The next portion is my grouping statement, by using an anonymous object I can group by multiple values. This line does the most heavy lifting in the query, the rest of the query is aggregation and mapping. My select statement next does the totaling for each sales person per year, after which I need to actually flatten the data and link the sales person’s name. Once that is done the left over is cleanup, doing sorting, and grouping just to make things look a little nicer in the dump.

The important thing to notice with this query, is that every step of the way I can examine the data and make decisions about how I want to move forward. I can also branch off to an entirely different direction if I want to, just by modifying the chain. As an example, if I insert a ToList after line 7 it causes the system to build and execute the sql right there, and anything after is run on the local computer, if I do it after the first Select statement, it makes the sql server run the aggregation instead of the local computer. As it is however, the sql query is built until the Dump and the end.

Using the iterative nature of linq allows us to do exploratory analysis much easier, however once a conclusion is reached, it is beneficial to rewrite the problem in a more traditional manor. As an example, below is the sql which will give the same results as the linq query”:

SELECT data.[Year], p.FirstName + + p.LastName, data.Amount FROM
(
SELECT DATEPART(Year, OrderDate) as [Year], SalesPersonID, SUM(subtotal) as Amount FROM
Sales.SalesOrderHeader
WHERE OnlineOrderFlag = 0
GROUP BY DATEPART(Year, OrderDate), SalesPersonID
) data
INNER JOIN Person.Person as [p] ON data.SalesPersonID = p.BusinessEntityID
ORDER BY [Year], Amount DESC

This gives you the best of both worlds. The flexibility of iterateable linq queries, and the performance of pure sql, once you have a result you can use.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s