Is Your Linq Query being executed on the database?

Depending on the approach you take when writing linq query, you may be surprised that some of the operations are getting performed in memory instead of the call being translated to sql and executed on the database. When you apply aggregate operators on association relationship on an entity in the context of a query syntax, the aggregations is performed on the database. However the same syntax when executed outside of the query syntax would force the entire association relationship to be brought from the database and aggregate operation performed in memory. In figure 30, I have two different version of the same query that returns the customerId, Total number of orders they have placed and TotalAmount they have spent on orders so far. In the first query, I am using association relationship Orders available on customer to get the total orders placed. To get the total amount spent, I first used the association relationship Orders on the customer and for each order, I navigate to its order Details association to calculate the cost of each Order. Since I am using association relationship inside of a linq query, the entire query is converted to sql and send to the database for execution. Second query in Figure 30, uses the same association relationship on the customer entity to perform calculations. Since the calculations are not part of an existing query, linq to sql has to bring all the orders for the customer and for each order bring down all its OrderDetails to calculate the Sum and Count operation. Bringing all the Orders and OrderDetails for a customer is an expensive operation which need not to be performed if all you are want ing to do is get the count and sum. Those operations can easily be done on the database. As a developer, it is important to understand the tradeoffs and know which option may be better suited for your scenario. For instance if you already have the order and OrderDetails for a customer in memory, than it may be more efficient to perform these operations in memrory instead of making a database call to the server.

Figure 30: aggregate operators are applied on the database if used inside of a linq query.