c# - 简化许多 LINQ Join
问题描述
我有一个相当复杂的 LINQ 查询,但这就是问题的重点
var result = await _context.TblBreakpoints
.GroupBy(b => new { b.BpgroupId, b.ResultType, b.DrugId, b.Susceptible, b.LowIntermediate, b.Intermediate, b.Resistant})
.Join(_context.TblBreakpointgroups,
bg => bg.Key.BpgroupId,
g => g.BpgroupId,
(bg, g) => new
{
GroupId = bg.Key.BpgroupId,
DrugId = bg.Key.DrugId,
Susceptible = bg.Key.Susceptible,
LowIntermediate = bg.Key.LowIntermediate,
Intermediate = bg.Key.Intermediate,
Method = bg.Key.ResultType,
Resistant = bg.Key.Resistant,
StandardId = g.BpstandardId,
GroupName = g.BpgroupName,
Count = bg.Count(),
})
.Join(_context.TblBreakpointStandards,
i => i.StandardId,
j => j.BpstandardId,
(i, j) => new
{
Standard = j.Bpstandard,
GroupId = i.GroupId,
GroupName = i.GroupName,
Count = i.Count,
Method = i.Method,
DrugId = i.DrugId,
Susceptible = i.Susceptible,
LowIntermediate = i.LowIntermediate,
Intermediate = i.Intermediate,
Resistant = i.Resistant
})
.Join(_context.TblDrugs,
i => i.DrugId,
j => j.DrugId,
(i, j) => new
{
DrugName = j.DrugName,
Standard = i.Standard,
GroupId = i.GroupId,
GroupName = i.GroupName,
Count = i.Count,
Method = i.Method,
Susceptible = i.Susceptible,
LowIntermediate = i.LowIntermediate,
Intermediate = i.Intermediate,
Resistant = i.Resistant
})
.Join(_context.TblBreakpointgroupmembers,
i => i.GroupId,
j => j.BpgroupId,
(i, j) => new
{
OrganismId = j.OrganismId,
Standard = i.Standard,
GroupId = i.GroupId,
GroupName = i.GroupName,
Count = i.Count,
Method = i.Method,
DrugName = i.DrugName,
Susceptible = i.Susceptible,
LowIntermediate = i.LowIntermediate,
Intermediate = i.Intermediate,
Resistant = i.Resistant
})
.Join(_context.TblOrganismNames,
i => i.OrganismId,
j => j.OrganismId,
(i, j) => new BreakpointSummary
{
OrganismName = j.OrganismName,
Standard = i.Standard,
GroupName = i.GroupName,
Count = i.Count,
Method = i.Method,
DrugName = i.DrugName,
Susceptible = i.Susceptible,
LowIntermediate = i.LowIntermediate,
Intermediate = i.Intermediate,
Resistant = i.Resistant
})
.ToListAsync().ConfigureAwait(false);
从每个 Join 的查询中,我不断传递以前的值并添加来自连接的值。只有 5 个连接已经很乏味了,如果有更多的连接,它会变得更加乏味。我错过了更好的方法吗?
我认为等效的 SQL 是
WITH bpg (BPGroupId, ResultType, DrugId, Susceptible, LowIntermediate, Intermediate, Resistant, Count)
AS (
SELECT BPGroupId, ResultType, DrugId, Susceptible, LowIntermediate, Intermediate, Resistant, COUNT(BPGroupId)
FROM dbo.tbl_Breakpoint a
GROUP BY BPGroupId,
ResultType,
DrugId,
Susceptible,
LowIntermediate,
Intermediate,
Resistant
)
SELECT a.BpgroupName, b.BPStandard, c.DrugName, e.OrganismName, CTE.ResultType, CTE.Susceptible, CTE.LowIntermediate, CTE.Intermediate, CTE.Resistant, CTE.Count
FROM dbo.tbl_breakpointgroup a
INNER JOIN bpg CTE ON a.BPGroupId = CTE.BPGroupId
INNER JOIN tbl_BreakpointStandard b ON b.BPStandardId = a.BPStandardId
INNER JOIN tbl_Drug c ON c.DrugID = CTE.DrugId
INNER JOIN tbl_breakpointgroupmember d ON d.BPGroupId = CTE.BPGroupId
INNER JOIN tbl_OrganismName e ON e.OrganismId = d.OrganismId
WHERE a.BPGroupId = CTE.BPGroupId
解决方案
通常,在 LINQ 中使用手动连接时,最好使用查询语法,因为它提供范围变量(对应于 SQL 中的表/查询别名)透明度。例如(在伪代码中)
from a in queryA
join b in queryB on ... // can use any field from a
join c in queryC on ... // can use any field from a and b
join d in queryD on ... // can use any field from a, b and c
...
select new
{
// can use any field for a, b, c, d etc.
}
与方法相同的语法有点复杂,但原则是将前面的“变量”包装在像匿名类型这样的简单元组中,直到你得到最终的投影,例如(在伪代码中)
queryA
.Join(queryB, a => {a fields}, b => {b fields), (a, b) => new { a, b }) // first result
.Join(queryC, r => {r.a, r.b fields), c => { c fields }, (r, c) => new { r.a, r.b, c } // second result
.Join(queryD, r => {r.a, r.b, r.c fields), d => { d fields }, (r, d) => new { r.a, r.b, r.c, d } // third result
...
.Select(r => new { r.a, r.b, r.c, r.d... fields });
将其应用于您的示例,相应的查询语法可能是这样的(请注意,里面的子查询可以使用任何合适的语法):
var query =
from cte in _context.TblBreakpoints
.GroupBy(b => new { b.BpgroupId, b.ResultType, b.DrugId, b.Susceptible, b.LowIntermediate, b.Intermediate, b.Resistant})
.Select(g => new
{
g.Key.BpgroupId, g.Key.ResultType, g.Key.DrugId, g.Key.Susceptible, h.Key.LowIntermediate, g.Key.Intermediate, g.Key.Resistant,
Count = g.Count(),
})
join a in _context.TblBreakpointgroups on cte.BpgroupId equals a.BpgroupId
join b in _context.TblBreakpointStandards on a.BpstandardId equals b.BpstandardId
join c in _context.TblDrugs on cte.DrugId equals c.DrugId
join d in _context.TblBreakpointgroupmembers on cte.BpgroupId equals d.BpgroupId
join e in _context.TblOrganismNames on d.OrganismId equals e.OrganismId
select new BreakpointSummary
{
OrganismName = e.OrganismName,
Standard = b.Bpstandard,
GroupName = a.BpgroupName,
Count = cte.Count,
Method = cte.ResultType,
DrugName = d.DrugName,
Susceptible = cte.Susceptible,
LowIntermediate = cte.LowIntermediate,
Intermediate = cte.Intermediate,
Resistant = cte.Resistant,
};
您可以使用上述规则将其转换为方法语法,但对我来说这不值得。
推荐阅读
- c++ - Vtk 热图:雷达数据的科学可视化:为 vtkPlaneSource 更新 vtkFloatArray
- xpath - 使用 XPATH 获取元素
- javascript - 如何在antdesign中的下拉事件“触发”上应用mixpanel
- java - 如何对 OpenJDK 进行更改并运行程序?
- python - 用python在sql server中插入数据
- c# - 我可以使用 Ocelot 将 POST http 请求重新路由到 GET http 请求吗?
- wordpress - 如何在 wordpress 插件中部署反应图像?
- android - ViewGroup 将所有项目宽度设置为孩子要求的最大值
- java - logback 文件名格式,包括时间格式
- azure - 将 VM 规模集附加到应用程序网关的后端池