ASP.NET MVC Linq Query Error Some part of your SQL statement is nested too deeply

I have a linq query shown below but it's generating a server error:

Rewrite the query or break it up into smaller queries

How I can solve it or or break it up into smaller queries? In my case my local server working fine with it but generating error in IIS live server.

var LinkQList = db.Customers.Select(c => new
{
UnitId = c.UnitId,
CustomerId = c.CustomerId,
MemoDate = c.MemoMasters.Select(a => new { a.MemoDate }),
CustomerName = c.CustomerName,
SalesManName = c.SalesMan.SalesManName,
SalesManagerName = c.SalesMan.SalesManager.SalesManagerName,
DistrictName = c.Upazila.District.DistrictName,

//**Previous Year
PreviousYearOpeningMemoDiscount = c.MemoMasters.Where(s => s.MemoDate < aYearPreviousFromDate).Select(a => new { a.MemoDiscount }).Sum(s => s.MemoDiscount) ?? 0,
PreviousYearOpeningGatOther = c.MemoMasters.Where(s => s.MemoDate < aYearPreviousFromDate).Select(a => new { a.GatOther }).Sum(s => s.GatOther) ?? 0,
PreviousYearOpeningGrossSales = c.MemoMasters.Where(s => s.MemoDate < aYearPreviousFromDate).Select(a => new { a.MemoCost }).Sum(s => (double?)s.MemoCost) ?? 0,
PreviousYearQuantity = c.MemoMasters.Where(s => s.MemoDate >= aYearPreviousFromDate && s.MemoDate <= aYearPreviousToDate).Select(a => new { a.Quantity }).Sum(s => s.Quantity) ?? 0,
PreviousYearQuantityConverted = c.MemoMasters.Where(s => s.MemoDate >= aYearPreviousFromDate && s.MemoDate <= aYearPreviousToDate).Select(a => new { a.QuantityConverted }).Sum(s => s.QuantityConverted) ?? 0,
PreviousYearMemoDiscount = c.MemoMasters.Where(s => s.MemoDate >= aYearPreviousFromDate && s.MemoDate <= aYearPreviousToDate).Select(a => new { a.MemoDiscount }).Sum(s => s.MemoDiscount) ?? 0,
PreviousYearGatOther = c.MemoMasters.Where(s => s.MemoDate >= aYearPreviousFromDate && s.MemoDate <= aYearPreviousToDate).Select(a => new { a.GatOther }).Sum(s => s.GatOther) ?? 0,
PreviousYearGrossSales = c.MemoMasters.Where(s => s.MemoDate >= aYearPreviousFromDate && s.MemoDate <= aYearPreviousToDate).Select(a => new { a.MemoCost }).Sum(s => (double?)s.MemoCost) ?? 0,
PreviousYearOpeningTotalBf = c.Payments.Where(s => s.AdjustmentBf == true && s.PaymentDate < aYearPreviousFromDate).Select(s => new { s.SSAmount }).Sum(s => (double?)s.SSAmount) ?? 0,
PreviousYearOpeningTotalPayments = c.Payments.Where(s => s.AdjustmentBf == false && s.PaymentDate < aYearPreviousFromDate).Select(a => new { a.SCAmount }).Sum(s => (double?)s.SCAmount) ?? 0,
PreviousYearOpeningTotalDiscounts = c.Payments.Where(s => s.PaymentDate < aYearPreviousFromDate).Select(a => new { a.SDiscount }).Sum(s => (double?)s.SDiscount) ?? 0,
PreviousYearTotalBf = c.Payments.Where(s => s.AdjustmentBf == true && s.PaymentDate >= aYearPreviousFromDate && s.PaymentDate <= aYearPreviousToDate).Select(s => new { s.SSAmount }).Sum(s => (double?)s.SSAmount) ?? 0,
PreviousYearTotalPayments = c.Payments.Where(s => s.AdjustmentBf == false && s.PaymentDate >= aYearPreviousFromDate && s.PaymentDate <= aYearPreviousToDate).Select(a => new { a.SCAmount }).Sum(s => (double?)s.SCAmount) ?? 0,
PreviousYearTotalDiscounts = c.Payments.Where(s => s.PaymentDate >= fromDate && s.PaymentDate <= aYearPreviousToDate).Select(a => new { a.SDiscount }).Sum(s => (double?)s.SDiscount) ?? 0,

//**Current Year
OpeningMemoDiscount = c.MemoMasters.Where(s => s.MemoDate < fromDate).Select(a => new { a.MemoDiscount }).Sum(s => s.MemoDiscount) ?? 0,
OpeningGatOther = c.MemoMasters.Where(s => s.MemoDate < fromDate).Select(a => new { a.GatOther }).Sum(s => s.GatOther) ?? 0,
OpeningGrossSales = c.MemoMasters.Where(s => s.MemoDate < fromDate).Select(a => new { a.MemoCost }).Sum(s => (double?)s.MemoCost) ?? 0,
Quantity = c.MemoMasters.Where(s => s.MemoDate >= fromDate && s.MemoDate <= toDate).Select(a => new { a.Quantity }).Sum(s => s.Quantity) ?? 0,
QuantityConverted = c.MemoMasters.Where(s => s.MemoDate >= fromDate && s.MemoDate <= toDate).Select(a => new { a.QuantityConverted }).Sum(s => s.QuantityConverted) ?? 0,
MemoDiscount = c.MemoMasters.Where(s => s.MemoDate >= fromDate && s.MemoDate <= toDate).Select(a => new { a.MemoDiscount }).Sum(s => s.MemoDiscount) ?? 0,
GatOther = c.MemoMasters.Where(s => s.MemoDate >= fromDate && s.MemoDate <= toDate).Select(a => new { a.GatOther }).Sum(s => s.GatOther) ?? 0,
GrossSales = c.MemoMasters.Where(s => s.MemoDate >= fromDate && s.MemoDate <= toDate).Select(a => new { a.MemoCost }).Sum(s => (double?)s.MemoCost) ?? 0,
OpeningTotalBf = c.Payments.Where(s => s.AdjustmentBf == true && s.PaymentDate < fromDate).Select(s => new { s.SSAmount }).Sum(s => (double?)s.SSAmount) ?? 0,
OpeningTotalPayments = c.Payments.Where(s => s.AdjustmentBf == false && s.PaymentDate < fromDate).Select(a => new { a.SCAmount }).Sum(s => (double?)s.SCAmount) ?? 0,
OpeningTotalDiscounts = c.Payments.Where(s => s.PaymentDate < fromDate).Select(a => new { a.SDiscount }).Sum(s => (double?)s.SDiscount) ?? 0,
TotalBf = c.Payments.Where(s => s.AdjustmentBf == true && s.PaymentDate >= fromDate && s.PaymentDate <= toDate).Select(s => new { s.SSAmount }).Sum(s => (double?)s.SSAmount) ?? 0,
TotalPayments = c.Payments.Where(s => s.AdjustmentBf == false && s.PaymentDate >= fromDate && s.PaymentDate <= toDate).Select(a => new { a.SCAmount }).Sum(s => (double?)s.SCAmount) ?? 0,
TotalDiscounts = c.Payments.Where(s => s.PaymentDate >= fromDate && s.PaymentDate <= toDate).Select(a => new { a.SDiscount }).Sum(s => (double?)s.SDiscount) ?? 0,

}).Where(c =>
(c.UnitId == unitId && c.CustomerName != "Cash Party") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party PSC Islampur (Alomgir)") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party Pakiza Print") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party Pakiza Textile") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party Pakiza Fabrics") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party PSC Madhobdi (Alomgir)") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party Pakiza Store") &&
(c.UnitId == unitId && c.CustomerName != "Cash Party PSC Islampur")
).ToList()


1 Answer
1

Ok, I've got another way to implementing your code - without to deeply linq statements. Maybe you will take it like better solution.

Your list with strings (for example put into your class):

private List<string> _list = new List<string>()
{
"Cash Party",
"Cash Party PSC Islampur (Alomgir)",
"Cash Party Pakiza Print",
"Cash Party Pakiza Textile",
"Cash Party Pakiza Fabrics",
"Cash Party PSC Madhobdi (Alomgir)",
"Cash Party Pakiza Store",
"Cash Party PSC Islampur"
};


Create some Inequality enum (you need it to do operation on date):

public enum Inequality
{
Undefined = 0,
GreatherThan = 1,
GreatherOrEqualThan = 2,
LessThan = 3,
LessOrEqualThan = 4,
Equal = 5
}


Implemented method which return double of sum from your property (filtered by date):

/// <summary>
/// Return sum of property - filtered by dates and boolean
/// </summary>
/// <typeparam name="T">Type of data</typeparam>
/// <param name="data">Data collection</param>
/// <param name="datePropertyName">Name of date property to filter</param>
/// <param name="countPropertyName">Name of property which you want to sum</param>
/// <param name="booleanPropertyName">If filter have a some boolean restiction - put there this boolean property name</param>
/// <param name="booleanPropertyValue">If filter have a some boolean restiction - put there this boolean value to filter</param>
/// <param name="dateFrom">Your date from restiction</param>
/// <param name="inequalityFrom">Inequality 'from' sign to filter</param>
/// <param name="dateTo">Your date to restriction</param>
/// <param name="inequalityTo">Inequality 'to' sign to filter</param>
/// <returns>Sum of countPropertyName</returns>
public double GetFilteredCount<T>(List<T> data, string datePropertyName, string countPropertyName, string booleanPropertyName = null, bool? booleanPropertyValue = null, DateTime? dateFrom = null, Inequality inequalityFrom = Inequality.Undefined,
DateTime? dateTo = null, Inequality inequalityTo = Inequality.Undefined)
{
double? result = null;
var list = new List<T>(data);

if (list != null && list.Count > 0 && !String.IsNullOrEmpty(datePropertyName) && !String.IsNullOrEmpty(countPropertyName) && (inequalityFrom != Inequality.Undefined || inequalityTo != Inequality.Undefined))
{
list = list.Where(w =>
{
DateTime value = (DateTime)w.GetType().GetProperty(datePropertyName).GetValue(w);

return dateFrom.HasValue ?
(
inequalityFrom == Inequality.GreatherThan ? value > dateFrom :
inequalityFrom == Inequality.GreatherOrEqualThan ? value >= dateFrom :
inequalityFrom == Inequality.LessThan ? value < dateFrom :
inequalityFrom == Inequality.LessOrEqualThan ? value <= dateFrom :
inequalityFrom == Inequality.Equal ? value == dateFrom : true
) : true && dateTo.HasValue ?
(
inequalityTo == Inequality.GreatherThan ? value > dateTo :
inequalityTo == Inequality.GreatherOrEqualThan ? value >= dateTo :
inequalityTo == Inequality.LessThan ? value < dateTo :
inequalityTo == Inequality.LessOrEqualThan ? value <= dateTo :
inequalityTo == Inequality.Equal ? value == dateTo : true
) : true && !String.IsNullOrEmpty(booleanPropertyName) && booleanPropertyValue.HasValue ?
(
(bool)w.GetType().GetProperty(booleanPropertyName).GetValue(w) == booleanPropertyValue

) : true;

}).ToList<T>();

if (list != null && list.Count > 0)
{
result = list.Sum(s => (double?)s.GetType().GetProperty(countPropertyName).GetValue(s));
}
}
return result ?? 0;
}


and the last - your main method to return Customer collection:

public List<Customer> GetCustomersList(int unitId)
{
var result = new List<Customer>();
var data = db.Customers.Select(s => s).Where(w => w.UnitId == unitId && !_list.Contains(w.CustomerName));

if (data != null && data.Count > 0)
{
foreach (var row in data)
{
var customer = new Customer();

customer.UnitId = row.UnitId;
customer.CustomerId = row.CustomerId;
customer.MemoDate = row.MemoMasters.FirstOrDefault().MemoDate;
customer.CustomerName = row.CustomerName;
customer.SalesManName = row.SalesMan.SalesManName;
customer.SalesManagerName = row.SalesMan.SalesManager.SalesManagerName;
customer.DistrictName = row.Upazila.District.DistrictName;

// Previous Year
customer.PreviousYearOpeningMemoDiscount = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoDiscount", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.LessThan);
customer.PreviousYearOpeningGatOther = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "GatOther", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.LessThan);
customer.PreviousYearOpeningGrossSales = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoCost", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.LessThan);
customer.PreviousYearQuantity = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "Quantity", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);
customer.PreviousYearQuantityConverted = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "QuantityConverted", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);
customer.PreviousYearMemoDiscount = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoDiscount", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);
customer.PreviousYearGatOther = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "GatOther", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);
customer.PreviousYearGrossSales = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoCost", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);

customer.PreviousYearOpeningTotalDiscounts = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SDiscount", dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.LessThan);
customer.PreviousYearOpeningTotalBf = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SSAmount", "AdjustmentBf", true, dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.LessThan);
customer.PreviousYearTotalBf = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SSAmount", "AdjustmentBf", true, dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);
customer.PreviousYearOpeningTotalPayments = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SCAmount", "AdjustmentBf", false, dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.LessThan);
customer.PreviousYearTotalPayments = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SCAmount", "AdjustmentBf", false, dateFrom: aYearPreviousFromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);
customer.PreviousYearTotalDiscounts = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SDiscount", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: aYearPreviousToDate, inequalityTo: Inequality.LessOrEqualThan);

// Current Year

customer.OpeningMemoDiscount = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoDiscount", dateFrom: fromDate, inequalityFrom: Inequality.LessThan);
customer.OpeningGatOther = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "GatOther", dateFrom: fromDate, inequalityFrom: Inequality.LessThan);
customer.OpeningGrossSales = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoCost", dateFrom: fromDate, inequalityFrom: Inequality.LessThan);
customer.Quantity = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "Quantity", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);
customer.QuantityConverted = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "QuantityConverted", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);
customer.MemoDiscount = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoDiscount", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);
customer.GatOther = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "GatOther", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);
customer.GrossSales = GetFilteredCount<MemoMasters>(row.MemoMasters, "MemoDate", "MemoCost", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);

customer.OpeningTotalBf = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SSAmount", "AdjustmentBf", true, dateFrom: fromDate, inequalityFrom: Inequality.LessThan);
customer.OpeningTotalPayments = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SCAmount", "AdjustmentBf", false, dateFrom: fromDate, inequalityFrom: Inequality.LessThan);
customer.OpeningTotalDiscounts = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SDiscount", dateFrom: fromDate, inequalityFrom: Inequality.LessThan);
customer.TotalBf = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SSAmount", "AdjustmentBf", true, dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);
customer.TotalPayments = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SCAmount", "AdjustmentBf", false, dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);
customer.TotalDiscounts = GetFilteredCount<Payment>(row.Payments, "PaymentDate", "SDiscount", dateFrom: fromDate, inequalityFrom: Inequality.GreatherOrEqualThan, dateTo: toDate, inequalityTo: Inequality.LessOrEqualThan);

result.Add(customer);
}
}
return result;
}


If my solution helped you - sign my answer as correct :)
enjoy !

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.