stored-procedures - 复杂高效的 LINQ 查询
问题描述
我写了一个生成不同销售点的linq
query
现金簿并按sort
. 完成时间太长date
。query
第1步:首先我在sql server中写了一个视图,它结合了费用和支付两种交易类型。
select *, 'expense' as TransactionType from tbl_Expenses
Union
select *, 'payment' as TransactionType from tbl_Payments
第 2 步:我现在编写了一个 linq 来查询视图并生成所需的结果。
public IList<dto_CashBookList> CashBookReport(DateTime? fromDate, DateTime? toDate, int? locationId)
{
try
{
List<dto_CashBookList> lstCashBookList = new List<dto_CashBookList>();
var locations = locationId == 0 ? _context.vw_Cashbook.Where(t => t.TransactionDate >= fromDate && t.TransactionDate <= toDate).DistinctBy(d => d.LocationId).ToList() : _context.vw_Cashbook.Where(t => t.TransactionDate >= fromDate && t.TransactionDate <= toDate && t.LocationId == locationId).DistinctBy(d => d.LocationId).ToList();
foreach(var location in locations)
{
List<dto_CashBook> lstCashBook = new List<dto_CashBook>();
var transactions = _context.vw_Cashbook.Where(t => t.TransactionDate >= fromDate && t.TransactionDate <= toDate && t.LocationId == location.LocationId).DistinctBy(d => d.TransactionDate.Date).OrderBy(k=>k.TransactionDate).ToList();
foreach (var transaction in transactions)
{
try
{
var expense = (from x in _context.vw_Cashbook
where x.TransactionType == "Expense" && x.TransactionDate.Year == transaction.TransactionDate.Year && x.TransactionDate.Day == transaction.TransactionDate.Day && x.TransactionDate.Month == transaction.TransactionDate.Month && x.LocationId ==transaction.LocationId
select new dto_Expense()
{
RecieverName = x.Name,
ReferenceNumber = x.ReferenceNumber,
Amount = x.Amount,
TransactionId = x.TransactionId,
PaymentModeId = x.PaymentModeId,
TransactionDate = x.TransactionDate
});
var payment = (from x in _context.vw_Cashbook
where x.TransactionType == "Payment" && x.TransactionDate.Year == transaction.TransactionDate.Year && x.TransactionDate.Day == transaction.TransactionDate.Day && x.TransactionDate.Month == transaction.TransactionDate.Month && x.LocationId == transaction.LocationId
select new dto_Payment()
{
Amount = x.Amount,
TransactionId = x.TransactionId,
PaymentDate = x.TransactionDate,
Invoice = new dto_Invoice()
{
CustomerName = x.Name,
InvoiceNumber = x.ReferenceNumber,
TransactionId = x.TransactionId
}
});
var bf = _financeRepository.GetCashBookBBF(transaction.TransactionDate, transaction.LocationId);
var totalExpense = expense.FirstOrDefault()==null? 0 : expense.Sum(x => x.Amount);
var totalPayment = payment.FirstOrDefault()==null? 0 : payment.Sum(x => x.Amount);
var cb = (bf + totalPayment) - totalExpense;
lstCashBook.Add(
new dto_CashBook()
{
BalanceBF = bf,
TotalExpense = totalExpense,
TotalPayment = totalPayment,
BalanceCD = cb,
TransactionStartDate = (DateTime)fromDate,
TransactionEndDate = (DateTime)toDate,
ReportDate = transaction.TransactionDate,
Expense = expense.ToList(),
Payment = payment.ToList()
});
}
catch (Exception ex)
{
throw ex;
}
}
lstCashBookList.Add(new dto_CashBookList()
{
LocationName =location.LocationName,
CashBook = lstCashBook
});
}
return lstCashBookList.ToList();
}
catch (Exception ex)
{
throw ex;
}
}
我想知道是否有更好的方法可以编写此代码。我宁愿stored procedure
使用linq
query
.
解决方案
推荐阅读
- vbscript - 从替换中跳过一个字符串,如果它有任何后缀或后缀
- android - 在服务内部使用广播接收器
- angular - Angular 8:NG8002:不能绑定到“ontime”,因为它不是“div”的已知属性
- node.js - 在win10中,我执行yarn add protobufjs,但是报错:error An unexpected error occurred: "EPERM: operation not allowed, unlink
- python - 模型很容易收敛但预测结果很差
- java - 尽管没有任何错误,但 JDBC 无法更新
- regex - 正则表达式:匹配带有下划线和 id 的单词
- perl - 如何将多个 HMTL 文件中的内容合并到一个文件中?
- http - 为什么不总是为 CORS 请求发送 Origin HTTP 标头?
- javascript - React cloneElement 不适用于功能组件