如何避免太多（JOINS，SELECT，WHERE）来增加实体框架性能？ [英] How to avoid too many (JOINS, SELECT, WHERE) to increase entity framework performance ?

问题描述

我使用以下代码将股票与最低余额进行比较，并获得否。短缺项目 as：

i use the following codes to compare the stock with Minimum Balance and get the no. of shortages items as:

int Shortages_StockNoSer = nDB01.stock_noserials
                .GroupBy(x => x.stitems_ID)
                .Select(grp => new
                {
                    itemID = grp.Key,
                    sum = grp.Sum(x => x.StockQnty),
                    min = grp.Select(s => 
                                     s.st_items.stitems_MinBalance).FirstOrDefault()
                }).Where(u => u.sum < u.min && u.min != 0)
                .Count();
int Shortages_StockWithSer = nDB01.purchases_item_seriels
               .GroupBy(x => x.stitems_ID)
               .Select(grp => new
               {
                   itemID = grp.Key,
                   sum = grp.Count(),
                   min = grp.Select(s => 
                                    s.st_items.stitems_MinBalance).FirstOrDefault()
               }).Where(u => u.sum < u.min && u.min != 0)
               .Count();
int CountShortagesItems = Convert.ToInt32( Shortages_StockNoSer) + 
                          Convert.ToInt32(Shortages_StockWithSer);

这也是我的代码没有。其余额超过信用额度的客户：

also this my code to get no. of customers that its Balances exceeded the credit limit :

int CustomersBalances_StockNoSer = nDB01.people_data
                .GroupBy(x => x.pepole_ID)
                .Select(grp => new
                {
                    itemID = grp.Key,
                    balance = nDB01.account_items.Where(u => (u.account_main.accitem_PeplID == grp.Key && u.accitem_AccID == 10) ||
                                    (u.account_main.accitem_PeplID == grp.Key && u.accitem_AccID == 59)).FirstOrDefault().accitem_Debit -
                                    nDB01.account_items.Where(u => (u.account_main.accitem_PeplID == grp.Key && u.accitem_AccID == 10) ||
                                    (u.account_main.accitem_PeplID == grp.Key && u.accitem_AccID == 59)).FirstOrDefault().accitem_Credit,
                    Limited = grp.Select(s => s.pepole_CreditLimit).FirstOrDefault()
                }).Where(u => (u.balance) > u.Limited )
                .Count();

我的尝试：



i使用 Entity Framework Profiler 检查此代码并获取此警报：



第一个代码警报：

太多嵌套选择语句



第二个代码警报：

太多嵌套选择语句

太多陈述中的条款

避免多次加入





我有什么可以做的吗？

What I have tried:

i checked this code with Entity Framework Profiler and get this alerts :

The first Code Alerts :
TOO MANY NESTING SELECT STATEMENTS

The Second Code Alerts :
TOO MANY NESTING SELECT STATEMENTS
TOO MANY WHERE CLAUSES IN STATEMENT
AVOID TOO MANY JOINS


Is there anything I can do about this?

推荐答案

写一个存储的procvedure来做你想要的连接，然后调用它来自实体框架。



您必须关注的两件事，查询需要多长时间以及返回的数据量。当您进行join / where时，EF并不是最有效的数据提取方式。

Write a stored procvedure that does the joins you want, and call it from entity framework.

Two things you have to be concerned with, how long a query takes, and how much data it returns. EF ain't exactly the most efficient way to pull data when you're doing join/where.

除了解决方案1中的概念之外，您还可以通过以下方式简化（明显）查询使用JOIN，UNIONS和其他查询组件创建适当的VIEW。



您可能会发现您的工作总体上更容易，因为一旦您有了查看所需的VIEW使用它的查询大大减少。

In addition to the concepts from solution 1, you can simplify the (apparent) queries by creating appropriate VIEWs with the JOIN, UNIONS, and other query components.

You may find your work much easier, overall, as once you have a VIEW the thought required for queries that use it is much reduced.

这篇关于如何避免太多（JOINS，SELECT，WHERE）来增加实体框架性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！