MySQL Database contains huge data (more than 5Gb) and more than 300,000 of records in some table.
Some Queries require to handle some of fields value before showing it to the user like this query :
var pQuery = DB2.sales_main
.OrderByDescending(o => o.smain_ID)
.Where(u => u.smain_Type == 0)
.Select(s => new SalesMainInfo
{
smain_ID = s.smain_ID,
cust_ID = s.cust_ID,
smain_Type = s.smain_Type,
pepole_Name = s.people_data.pepole_Name,
smain_Date = s.smain_Date,
smain_TotalValue = s.smain_TotalValue,
smain_PaidValue = s.smain_PaidValue,
smain_ReminValue = s.smain_ReminValue,
InvoiceCode = s.InvoiceCode,
smain_CardPaid = s.smain_CardPaid,
smain_CheaquePaid = s.smain_CheaquePaid,
smain_CashPaid = s.smain_CashPaid,
SecCurrency_paidValue = s.SecCurrency_paidValue,
SecCurrency_totalValue = s.SecCurrency_totalValue,
SecCurrency_ID = s.SecCurrency_ID,
SecCurrency_Index = s.SecCurrency_Index,
SecCurrency_Name = s.SecCurrency_Name,
r_user_ID = s.r_user_ID,
r_shift_ID = s.r_shift_ID,
smain_TaxValue = s.smain_TaxValue,
});
if (checkItem.Checked)
{
pQuery = pQuery.Where(sm => DB2.sales_item.Any(si => si.smain_ID == sm.smain_ID && si.stitems_ID == item));
}
var payRes = Paging.GetPaged(pQuery, GoToPage, paging1.Count_Per_Page);
paging1.PassParameters(payRes.CurrentPage, payRes.PageCount);
var payResList = payRes.Results.ToList();
foreach (var i in payResList)
{
try { i.SecCurrency_Name = Functions.GetDecimalParts(i.SecCurrency_paidValue.Value, false, i.SecCurrency_ID.Value) + "/" + Functions.GetDecimalParts(i.SecCurrency_totalValue.Value, false, i.SecCurrency_ID.Value) + " " + (i.SecCurrency_Index == null ? "" : CurrencyInfo.currencyShortcutByID(i.SecCurrency_Index.Value)); } catch { }
}
sales_mainBindingSource.DataSource = payResList;
here the amount in SecCurrency_paidValue, SecCurrency_totalValue must rounded depending on the currency used.
and also SecCurrency_Name must contains currency shortcut in the end.
so, how to inhect the Ef6 query to handle to avoid using
foreach that take more than 5 minutes to end
What I have tried:
foreach (var i in payResList)
{
try { i.SecCurrency_Name = Functions.GetDecimalParts(i.SecCurrency_paidValue.Value, false, i.SecCurrency_ID.Value) + "/" + Functions.GetDecimalParts(i.SecCurrency_totalValue.Value, false, i.SecCurrency_ID.Value) + " " + (i.SecCurrency_Index == null ? "" : CurrencyInfo.currencyShortcutByID(i.SecCurrency_Index.Value)); } catch { }
}