像这样将多个查询聚合到一个EF中？ [英] Aggregating multiple queries into one with EF like this?
问题描述
我在我的应用程序中使用EF 4.0 POCO。 检索这样的信息是否有不利之处？
I am using EF 4.0 POCO in my application. Are there any downsides to retrieving information like this?
给出 customerId 和一个 productId ，我想应用一些业务规则，这些规则要求我从数据库中获取大量信息，这需要多个查询。相反，我可以这样写一个查询：
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();
Customers.Take（0）.DefaultIfEmpty（）只是提供了一种启动查询以及 Customers ， SalesOrderHeaders 和 Products 是上下文中的EF ObjectQuery实例（如果来自LinqPad，则为此示例）。这将导致以下SQL：
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]
推荐答案
出于以下三个原因，我倾向于使用单独的查询：
I lean to using separate queries for three reasons:
- 隔离：单独的查询更清晰，更易于维护：使用一个整体查询，每次更改都可能有很多副作用。将业务规则应用于较小的孤立代码段更容易。
- 效率：您可能最终组成了一个查询，与单独的查询相比，它的效率低得多，因为它不可能找到一个好的执行计划，甚至可能超过更多数据库往返的开销（但这将作为基准）。
- 锁定：它可能会工作一段时间，直到需求以某种大查询不再起作用的方式发生变化：可能需要不成比例的大量重构。
- Isolation: Separate queries are much clearer and better maintainable: with one monolith query, each change potentially has many side-effects. It is easier to apply business rules to small, isolated pieces of code.
- Efficiency: You might end up composing a query that is far less inefficient than separate queries because it gets impossible to find a good execution plan, this may even outweigh the cost of more database round-trips (but that is to be benchmarked).
- Locked-in: It may work for a while, until requirements change in a way that one big query does not work anymore: may require a disproportional lot of refactoring.
还有一些直觉：需要一些技巧（ Take（0））通常表明设计不好（也许您只是个才华横溢的人，但就我而言，通常是前者。）
Plus a bit of gut feeling: needing some trick (Take(0)) is often indicative of bad design (or maybe you're just f** brilliant, but in my case it's usually the former).
但是，我当然看到了潜在的优势。如前所述，由于较少的数据库往返次数，结果可能会表现更好。而且，使用一个 select new 组成一个数据传输对象，而不是将它们从单独的位编织在一起，是很舒服的。
However, I see the potential advantages of course. As said, it may turn out to perform better because of less db roundtrips. And it is quite comfortable to use one select new to compose one data transfer object as opposed to knitting it together from the separate bits.
因此，没有明确的结论。就个人而言，我确实想保持简单并在性能确实存在问题时处理性能。
So, not a clear cut verdict. Personally I like to keep things simple and deal with performance when it really is an issue.
这篇关于像这样将多个查询聚合到一个EF中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
