如何将此SQL查询与聚合函数转换为linq等效项 [英] How can I convert this SQL query with aggregate function to linq equivalent
本文介绍了如何将此SQL查询与聚合函数转换为linq等效项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的SQL查询:
选择 Product.ProductName ,Sum(Credit) - Sum(借方) as TotalBalance
来自 Acct_GL 内部 join 产品 ON Acct_GL.AcctRef = Product.ProductCode
其中 AccountId = @ custaid AND CONVERT (日期,effdate, 103 )< = CONVERT (日期, @ todate , 103 ) AND (反向= ' N')
分组 Product.ProductName
这个是我的Linq查询:
var TotalAccountBalance =(来自 pro _db.Products
join gl _db.Acct_GL
on pro.ProductCode.Trim()equals(gl.AcctRef.Trim())
其中 gl.AccountID == CustId&& gl.EffDate& lt; = FromDate&&
gl.Reverse == N
group pro by pro.ProductName into pd
orderby pd .Key
select new AccountBalanceModel()
{
Total = Convert.ToDecimal(gl.Credit) - Convert.ToDecimal(gl.Debit)
})。FirstOrDefault();
解决方案
< blockquote>好像,你走在正确的轨道上......
区别在于:
var TotalAccountBalance =( from pro in _db.Products
join gl in _db.Acct_GL on pro.ProductCode等于gl.AcctRef
其中 gl.AccountID == CustId&& gl.EffDate < = FromDate&& gl.Reverse == N
group pro by pro.ProductName into pd
select new AccountBalanceModel()
{
ProductName = pro.ProductName,
Total = Convert.ToDecimal(gl.Credit) - Convert.ToDecimal(gl.Debit )
})。ToList();
var TotalAccountBalance =
(来自pro in _db.Products
加入gl在_db.Acct_GL上pro.ProductCode等于gl.AcctRef
其中gl.AccountID == CustId && gl.EffDate< = FromDate && gl.Reverse ==
N
group pro by pro.ProductName into pd
选择新的AccountBalanceModel()
{
ProductName = pd.key,
总计=(十进制)pd.Sum(x => x.Credit) - (十进制)pd.Sum(x => x.Debit)
})。ToList();
This is my sql query:
Select Product.ProductName, Sum(Credit) - Sum(Debit) as TotalBalance
from Acct_GL inner join Product ON Acct_GL.AcctRef = Product.ProductCode
where AccountId = @custaid AND CONVERT(Date,effdate,103) <= CONVERT(Date,@todate,103) AND (Reverse = 'N')
Group by Product.ProductName
This is my Linq query:
var TotalAccountBalance = (from pro in _db.Products
join gl in _db.Acct_GL
on pro.ProductCode.Trim() equals (gl.AcctRef.Trim())
where gl.AccountID == CustId && gl.EffDate <= FromDate &&
gl.Reverse == "N"
group pro by pro.ProductName into pd
orderby pd.Key
select new AccountBalanceModel()
{
Total = Convert.ToDecimal(gl.Credit) - Convert.ToDecimal(gl.Debit)
}).FirstOrDefault();
解决方案
Seems, you're on the right track...
The difference is:
var TotalAccountBalance = (from pro in _db.Products join gl in _db.Acct_GL on pro.ProductCode equals gl.AcctRef where gl.AccountID == CustId && gl.EffDate <= FromDate && gl.Reverse == "N" group pro by pro.ProductName into pd select new AccountBalanceModel() { ProductName = pro.ProductName, Total = Convert.ToDecimal(gl.Credit) - Convert.ToDecimal(gl.Debit) }).ToList();
var TotalAccountBalance =
(from pro in _db.Products
join gl in _db.Acct_GL on pro.ProductCode equals gl.AcctRef
where gl.AccountID == CustId && gl.EffDate <= FromDate && gl.Reverse ==
"N"
group pro by pro.ProductName into pd
select new AccountBalanceModel()
{
ProductName = pd.key,
Total = (decimal)pd.Sum(x=>x.Credit) - (decimal)pd.Sum(x=>x.Debit)
}).ToList();
这篇关于如何将此SQL查询与聚合函数转换为linq等效项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文