I am using EF 4.0 POCO in my application. Are there any downsides to retrieving information like this?
Given a customerId and a productId, I would like to apply some business rules that require me to fetch lots teeny pieces of information from the database requiring multiple queries. Instead, I could write one query like so:
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();
The Customers.Take(0).DefaultIfEmpty() just gives a way to start the query and Customers, SalesOrderHeaders and Products are EF ObjectQuery instances from the 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]
