Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,
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();


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?
Posted
Updated 27-Dec-18 4:19am
v2

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.
 
Share this answer
 
Comments
MadMyche 27-Dec-18 14:31pm    
Sums up my thoughts on EF, Thank you
Golden Basim 27-Dec-18 14:41pm    
thank you , i have many queries like this so i will convert them to "stored procedure" and " VIEWs ", but i'm not prefect in that , can you please i know example for one of my codes !?
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.
 
Share this answer
 
v2
Comments
Golden Basim 27-Dec-18 14:41pm    
thank you , i have many queries like this so i will convert them to "stored procedure" and " VIEWs ", but i'm not prefect in that , can you please i know example for one of my codes !?
W Balboos, GHB 27-Dec-18 14:44pm    
For VIEWS, use

CREATE VIEW viewName
AS
. . . put your SQL in here . . .

See here: https://www.w3schools.com/sql/sql_view.asp
You'll never learn to do things until you struggle a bit with actually doing them.

Also, when your question is answered please pick the answer you like and "accept it" to close your question.
Golden Basim 28-Dec-18 14:08pm    
thank you ..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900