Aggregating multiple queries into one with EF?
I am using EF 4.0 POCO in my application. Are there any downsides to getting information like this?
Given a customerId
and a productId
, I would like to apply some business rules that require me to get many small pieces of information from a database that requires multiple queries. Instead, I could write one query like this:
var customerId = 1;
var productId = 1;
var aggregateQuery =
from entry in Customers.Take(0).DefaultIfEmpty()
select new
{
numberOfOrders = SalesOrderHeaders.Where (header => header.CustomerID == customerId).Count(),
canSellProduct = Products.Where(product => product.ProductID == productId && product.SellEndDate > DateTime.Now).Count () > 0
//more infromation of this sort, required to enforce business rules
};
var informationPacket = aggregateQuery.First();
Customers.Take(0).DefaultIfEmpty()
just makes it possible to run a query, and Customers
, SalesOrderHeaders
and Products
are instances of EF ObjectQuery from context (this example if from LinqPad). This results in the following SQL:
-- Region Parameters
DECLARE @p0 Int = 1
DECLARE @p1 Int = 1
DECLARE @p2 DateTime = '2012-04-04 21:02:20.798'
DECLARE @p3 Int = 0
-- EndRegion
SELECT TOP (1) [t6].[value] AS [numberOfOrders], [t6].[value2] AS [canSellProduct]
FROM (
SELECT (
SELECT COUNT(*)
FROM [Sales].[SalesOrderHeader] AS [t3]
WHERE [t3].[CustomerID] = @p0
) AS [value],
(CASE
WHEN ((
SELECT COUNT(*)
FROM [Production].[Product] AS [t5]
WHERE ([t5].[ProductID] = @p1) AND ([t5].[SellEndDate] > @p2)
)) > @p3 THEN 1
WHEN NOT (((
SELECT COUNT(*)
FROM [Production].[Product] AS [t5]
WHERE ([t5].[ProductID] = @p1) AND ([t5].[SellEndDate] > @p2)
)) > @p3) THEN 0
ELSE NULL
END) AS [value2]
FROM (
SELECT NULL AS [EMPTY]
) AS [t0]
OUTER APPLY (
SELECT TOP (0) NULL AS [EMPTY]
FROM [Sales].[Customer] AS [t1]
) AS [t2]
) AS [t6]
source to share
I tend to use separate queries for three reasons:
- Insulation . Individual queries are much clearer and better supported: with a single monolithic query, each change potentially has many side effects. It's easier to apply business rules to small, isolated pieces of code.
- Efficiency . You might write a query that is much less inefficient than the individual queries, as it is impossible to find a good execution plan, it might even outweigh the cost of more database calls (but that has to be compared).
- Locked : It may run for a while until the requirements change so that one big query no longer works: A disproportionate batch of refactoring may be required.
Plus a little gut feeling: the need for trick ( Take(0)
) is often indicative of poor design (or maybe you're just f ** ing brilliant, but in my case it's usually the first one).
However, I can see, of course, potential benefits. As said, it might be better because there are less db roundtrips. And it's pretty convenient to use one select new
to create a single data transfer object, rather than knitting it out of individual bits.
So this is not a clear-cut verdict. Personally, I like to keep things simple and deal with performance when it really is a problem.
source to share