我在Windows上使用EF 6，ASP.NET
我有这个SQL语句，看起来像这样：
SELECT M.STMTDT, M.stmtno, ISNULL(SUM(M.PayAmount),0) as Fee, ISNULL(SUM(A.Amount),0) as Adjustment, ISNULL(SUM(M.PayAmount) + SUM(A.Amount),0) as Total FROM MainData M
LEFT OUTER JOIN Adjustments A
ON M.STMTDT = A.STMTDT AND M.stmtno = A.Stmtno
where M.CID= '334R' AND YEAR(M.stmtdt) > year(getdate())-4
GROUP BY M.STMTDT, M.stmtno
这是一个非常标准的查询，一个组，加入，位置和总和。我想在 LINQ 中创建相同的查询，但事实证明这样做很困难。
这是我的 LINQ 查询：
var fourYearsAgo = DateTime.Now.AddYears(-4).Year;
var dataWithoutGrouping = from m in MainData
where m.CID == "334r" && m.STMTDT.Value.Year > fourYearsAgo
join a in Adjustments
on new {m.STMTDT, m.Stmtno} equals new {a.STMTDT, a.Stmtno} into grp
from ja in grp.DefaultIfEmpty()
select new {
Dt = m.STMTDT,
No = m.Stmtno,
Fee = m.PayAmount,
Adjustment = ja.Amount
};
var data = (from b in dataWithoutGrouping
group b by new {b.Dt, b.No }into grp
select new {
StatmentFee = grp.Sum(x => x.Fee),
StatementAdjustments = grp.Sum(x => x.Adjustment),
StatementDate = grp.FirstOrDefault().Dt,
StatementNo = grp.FirstOrDefault().No
}).ToList();
生成此 SQL：
-- Region Parameters
DECLARE @p0 VarChar(1000) = '334r'
DECLARE @p1 Int = 2014
-- EndRegion
SELECT [t3].[value] AS [StatmentFee], [t3].[value2] AS [StatementAdjustments], (
SELECT [t6].[STMTDT]
FROM (
SELECT TOP (1) [t4].[STMTDT]
FROM [MainData] AS [t4]
LEFT OUTER JOIN [Adjustments] AS [t5] ON ([t4].[STMTDT] = [t5].[STMTDT]) AND ([t4].[stmtno] = [t5].[Stmtno])
WHERE ((([t3].[STMTDT] IS NULL) AND ([t4].[STMTDT] IS NULL)) OR (([t3].[STMTDT] IS NOT NULL) AND ([t4].[STMTDT] IS NOT NULL) AND ((([t3].[STMTDT] IS NULL) AND ([t4].[STMTDT] IS NULL)) OR (([t3].[STMTDT] IS NOT NULL) AND ([t4].[STMTDT] IS NOT NULL) AND ([t3].[STMTDT] = [t4].[STMTDT]))))) AND ((([t3].[stmtno] IS NULL) AND ([t4].[stmtno] IS NULL)) OR (([t3].[stmtno] IS NOT NULL) AND ([t4].[stmtno] IS NOT NULL) AND ((([t3].[stmtno] IS NULL) AND ([t4].[stmtno] IS NULL)) OR (([t3].[stmtno] IS NOT NULL) AND ([t4].[stmtno] IS NOT NULL) AND ([t3].[stmtno] = [t4].[stmtno]))))) AND ([t4].[CID] = @p0) AND (DATEPART(Year, [t4].[STMTDT]) > @p1)
) AS [t6]
) AS [StatementDate], (
SELECT [t9].[stmtno]
FROM (
SELECT TOP (1) [t7].[stmtno]
FROM [MainData] AS [t7]
LEFT OUTER JOIN [Adjustments] AS [t8] ON ([t7].[STMTDT] = [t8].[STMTDT]) AND ([t7].[stmtno] = [t8].[Stmtno])
WHERE ((([t3].[STMTDT] IS NULL) AND ([t7].[STMTDT] IS NULL)) OR (([t3].[STMTDT] IS NOT NULL) AND ([t7].[STMTDT] IS NOT NULL) AND ((([t3].[STMTDT] IS NULL) AND ([t7].[STMTDT] IS NULL)) OR (([t3].[STMTDT] IS NOT NULL) AND ([t7].[STMTDT] IS NOT NULL) AND ([t3].[STMTDT] = [t7].[STMTDT]))))) AND ((([t3].[stmtno] IS NULL) AND ([t7].[stmtno] IS NULL)) OR (([t3].[stmtno] IS NOT NULL) AND ([t7].[stmtno] IS NOT NULL) AND ((([t3].[stmtno] IS NULL) AND ([t7].[stmtno] IS NULL)) OR (([t3].[stmtno] IS NOT NULL) AND ([t7].[stmtno] IS NOT NULL) AND ([t3].[stmtno] = [t7].[stmtno]))))) AND ([t7].[CID] = @p0) AND (DATEPART(Year, [t7].[STMTDT]) > @p1)
) AS [t9]
) AS [StatementNo]
FROM (
SELECT SUM([t2].[PayAmount]) AS [value], SUM([t2].[value]) AS [value2], [t2].[STMTDT], [t2].[stmtno]
FROM (
SELECT [t0].[STMTDT], [t0].[stmtno], [t0].[PayAmount], [t1].[Amount] AS [value], [t0].[CID]
FROM [MainData] AS [t0]
LEFT OUTER JOIN [Adjustments] AS [t1] ON ([t0].[STMTDT] = [t1].[STMTDT]) AND ([t0].[stmtno] = [t1].[Stmtno])
) AS [t2]
WHERE ([t2].[CID] = @p0) AND (DATEPART(Year, [t2].[STMTDT]) > @p1)
GROUP BY [t2].[STMTDT], [t2].[stmtno]
) AS [t3]
如您所见，与原始语句相比，此SQL效率非常低。
请有人帮我转换我的 LINQ 以生成上面显示的原始 SQL。
另外，不，由于无法解释的原因，我无法使用SQL，对不起！
编辑
示例数据：
主数据：
日期| |斯特姆诺 |费
2016-01-29| 00：00：00.000 |3124360 |25.00
2016-02-12| 00：00：00.000 |3124391 |50.00
2016-01-29| 00：00：00.000 |3124360 |600.00
2016-02-12| 00：00：00.000 |3124391 |75.00
调整：
标准日期 | StmtNo| 调整后
2016-01-29 |00：00：00.000 |3124360 0.00
2016-02-12 |00：00：00.000 |3124391 0.00
2016-01-29 |00：00：00.000 |3124360 120.00
2016-02-12 |00：00：00.000 |3124391 60.00
期望的结果：
标准日期 |斯特姆诺 |费用 |调整 |总
2016-01-29 00：00：00.000 |3124360 |25.00 |0.00 |0.00
2016-02-12 00：00：00.000 |3124391 |50.00 |0.00 |0.00
2016-02-19 00：00：00.000 |3124404 |600.00 |120.00 |720.00
2016-02-19 00：00：00.000 |3124405 |75.00 |60.00 |135.00
这是我得到的结果：
如果您已经有MainData and AdjustmentEF
我创建了一个列表：
List<MainData> mdata = new List<MainData>();
List<Adjustments> adj = new List<Adjustments>();
List<Result> resFinal = new List<Result>();
和LINQ
var gety = DateTime.Now.AddYears(-4);
var res = from h in mdata
join j in adj
on h.StmtNo equals j.StmtNo
select new Result { StmtDate = h.StmtDate, StmtNo = h.StmtNo, Fee = h.Fee, Adj = j.Adj, Total = (h.Fee * j.Adj) };
resFinal = res.Cast<Result>().Where(x=>x.StmtDate > gety).ToList();
最终的班级持有者
public class Result
{
public DateTime StmtDate { get; set; }
public int StmtNo { get; set; }
public double Fee { get; set; }
public double Adj { get; set; }
public double Total { get; set; }
}
感谢评论中的伊万，我想出了这个解决方案：
var fourYearsAgo = DateTime.Now.AddYears(-4).Year;
var dataWithoutGrouping = from m in MainData
where m.CID == "334r" && m.STMTDT.Value.Year > fourYearsAgo
join a in Adjustments
on new {m.STMTDT, m.Stmtno} equals new {a.STMTDT, a.Stmtno} into grp
from ja in grp.DefaultIfEmpty()
select new {
Dt = m.STMTDT,
No = m.Stmtno,
Fee = m.PayAmount,
Adjustment = ja.Amount
};
var data = (from b in dataWithoutGrouping
group b by new {b.Dt, b.No }into grp
select new {
StatmentFee = grp.Sum(x => x.Fee),
StatementAdjustments = grp.Sum(x => x.Adjustment),
StatementDate = grp.Key.Dt,
StatementNo = grp.Key.No
}).ToList();
我只需要使用grp.Key，因为FirstOrDefault()正在为每条记录创建一个选择。
另外，如果有人需要，这是 VB 中的答案(我将我的问题翻译成 C#，因为 VB 在这里并不那么流行，我把它翻译回来)：
Dim fourYearsAgo = DateTime.Now().AddYears(-4).Year
Dim dataWithoutGrouping = From m In dbContext.MainDatas
Where m.CID = "334r" AndAlso m.STMTDT.Value.Year > fourYearsAgo
Group Join a In dbContext.Adjustments
On New With {m.STMTDT, m.stmtno} Equals New With {a.STMTDT, a.Stmtno} Into Group
From ja In Group.DefaultIfEmpty()
Select New With {
.Dt = m.STMTDT,
.No = m.stmtno,
.Fee = m.PayAmount,
.Adjustment = ja.Amount
}
Dim data = (From b In dataWithoutGrouping
Group b By grpKeys = New With {b.Dt, b.No} Into Group
Select New With {
.StatmentFee = Group.Sum(Function(x) x.Fee),
.StatementAdjustments = Group.Sum(Function(x) x.Adjustment),
.StatementDate = grpKeys.Dt,
.StatementNo = grpKeys.No
}).ToList()
它生成此 SQL：
DECLARE @p0 VarChar(1000) = '334r'
DECLARE @p1 Int = 2014
-- EndRegion
SELECT SUM([t2].[PayAmount]) AS [StatmentFee], SUM([t2].[value]) AS [StatementAdjustments], [t2].[STMTDT] AS [StatementDate], [t2].[stmtno] AS [StatementNo]
FROM (
SELECT [t0].[STMTDT], [t0].[stmtno], [t0].[PayAmount], [t1].[Amount] AS [value], [t0].[CID]
FROM [MainData] AS [t0]
LEFT OUTER JOIN [Adjustments] AS [t1] ON ([t0].[STMTDT] = [t1].[STMTDT]) AND ([t0].[stmtno] = [t1].[Stmtno])
) AS [t2]
WHERE ([t2].[CID] = @p0) AND (DATEPART(Year, [t2].[STMTDT]) > @p1)
GROUP BY [t2].[STMTDT], [t2].[stmtno]