Executing Aggregates on SQL Server using Linq To SQL

Linq to SQL support many aggregates such as Sum,Avg, Min, Max and few others but they may not execute on SQL server. Depending upon how you write your queries, you would be surprised the same syntax would actually bring the entire data from the server and execute the aggregate operations in memory. Below is an example where the entire query executes on SQL server and hence very efficient in terms of performance. While the same exact query just written in two steps causes the records to be fetched from SQL server and aggregate be performed in memory.

In the above code, I am demonstrating two examples for displaying the same data. Basically I am displaying the Company Name, TotalOrders and Total Quantity ordered. In the first query, the Count operation for orders and sum operations on the quantity for each Order Detail item gets executed on SQL server. This is because the Sum and Count aggregate operators are part of  a bigger Linq query. If the aggregate operators are not part of the query, than Linq to SQL brings the data down and executes the aggregate operators on the in memory collection.  Here is the SQL generated by the first query.

Looking at the above code you can confirm that our Count and Sum operations got converted into appropriate SQL syntax.

On the second query, I am first retrieving the customer and than Counting its Orders  and also summing the quantity ordered for each Order Detail Item.  The syntax is exactly the same as above query but since the aggregate operators are not part of the Linq query they use Linq to objects implementation. In order to execute get the Count of Orders, Linq to SQL brings all the orders for the Customer and Than Count operator is called on the Collection retrieved. To Sum the quantity, we are making seprate database calls to retrieve the OrderDetails for each order.  Since we have 6 orders we are making 6 database calls to retrieve OrderDetails for 6 orders. Once we have retrieved the OrderDetails for all the orders, the sum operator is applied on the OrderDetails Collection. Here is SQL generated to bring the OrderDetails in memory.