如何对多个 Linq IQueryable 执行 sql Union

Question

我正在开发一个项目，我想用日志数据制作图表。

所以我已经将日志数据加载到我的 SQL 服务器中，并找到了生成我需要的结果的查询。每个日志条目包含每 2 秒的测量值。

SELECT '7am' as timeslot, MAX(Odometer_km) - MIN(Odometer_km) as distance, AVG(Tanktemperature_C) tanktemp, AVG(Pressure_bar) avgBar FROM [FC66294] 

where [epochasdate] >= '2018-05-03 07:00:00' AND [epochasdate] <= '2018-05-03 08:00:00'

UNION 

SELECT '8am' as timeslot, MAX(Odometer_km) - MIN(Odometer_km) as distance, AVG(Tanktemperature_C) tanktemp, AVG(Pressure_bar) avgBar FROM [FC66294] 

where [epochasdate] >= '2018-05-03 08:00:00' AND [epochasdate] <= '2018-05-03 09:00:00'

UNION 

SELECT '9am'as timeslot, MAX(Odometer_km) - MIN(Odometer_km) as distance, AVG(Tanktemperature_C) tanktemp, AVG(Pressure_bar) avgBar FROM [FC66294] 

where [epochasdate] >= '2018-05-03 09:00:00' AND [epochasdate] <= '2018-05-03 10:00:00'

此示例显示了三个时隙以及该时隙上的一些计算值。

现在，我尝试使用 linq 查询在 C# 中重现此 SQL 结果，因为我想在图表中显示结果。我想出了以下几点：

using (var entity = new DBEntity.Entities())

            {

                var startDatetime = new DateTime(2018, 05, 03, 7, 0, 0);

                var endDatetime = new DateTime(2018, 05, 03, 8, 0, 0);

                var firstResult = entity.M_FC66294

                    .Where(x => x.epochasdate >= System.Data.Entity.DbFunctions.AddHours(startDatetime, 0) && x.epochasdate <= System.Data.Entity.DbFunctions.AddHours(endDatetime, 0))

                    .GroupBy(x => 1)

                    .Select(g => new {

                        TankTemp = g.Average(x => x.Tanktemperature_C),

                        Bar = g.Average(x => x.Pressure_bar),

                        MinOdo = g.Min(x => x.Odometer_km),

                        MaxOdo = g.Max(x => x.Odometer_km)

                    });

现在变量 test1 实际上包含了我需要的 2 行数据。但我希望变量 test2 包含 24 行（1 天）的数据。

但它不包含任何行。

那么，如何连接多个结果变量，然后在 FinalQuery 上运行 ToList() ？

编辑：或者如何在循环内使用 concat？

Answer 1

您在询问连接，但您似乎使用它，所以我不太确定您需要什么，但是如果您希望变量中的 24 条记录按小时分组，您只需按数据的小时运行分组即可。像这样var groupedbyhour = entity.M_FC66294.GroupBy(entry => entry.epochasdate.Date.AddHours(entry.epochasdate.Hour))//group by hour&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(HourGroup => new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TankTemp = HourGroup.Average(x => x.Tanktemperature_C),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Bar = HourGroup.Average(x => x.Pressure_bar),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MinOdo = HourGroup.Min(x => x.Odometer_km),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MaxOdo = HourGroup.Max(x => x.Odometer_km),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; epochasdate = HourGroup.Max(x => x.epochasdate.Date.AddHours(x.epochasdate.Hour))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}).ToList();请记住，纪元现在将是几小时组，因此例如 2019/07/30 - 7:00 和 2019/07/30 - 8:00更新这是我的完整测试，以便您可以复制。public class entry{&nbsp; &nbsp; public int Odometer_km;&nbsp; &nbsp; public DateTime epochasdate;&nbsp; &nbsp; public int Tanktemperature_C;&nbsp; &nbsp; public int Pressure_bar;}class Program{&nbsp;&nbsp; &nbsp; static void Main(string[] args)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; List<entry> entries = new List<entry>(){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() {epochasdate = new DateTime(2019, 07, 29, 7, 2, 1), Odometer_km = 10, Pressure_bar = 1, Tanktemperature_C = 23},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 7, 1, 1), Odometer_km = 20, Pressure_bar = 2, Tanktemperature_C = 25 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 7, 3, 1), Odometer_km = 22, Pressure_bar = 2, Tanktemperature_C = 24 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 7, 22, 1), Odometer_km = 25, Pressure_bar = 4, Tanktemperature_C = 22 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 24, 1), Odometer_km = 36, Pressure_bar = 2, Tanktemperature_C = 20 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 21, 1), Odometer_km = 42, Pressure_bar = 3, Tanktemperature_C = 19 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 29, 1), Odometer_km = 50, Pressure_bar = 2, Tanktemperature_C = 21 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 22, 1), Odometer_km = 55, Pressure_bar = 4, Tanktemperature_C = 20 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 52, 1), Odometer_km = 62, Pressure_bar = 2, Tanktemperature_C = 19 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 43, 1), Odometer_km = 80, Pressure_bar = 3, Tanktemperature_C = 17 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 22, 1), Odometer_km = 120, Pressure_bar = 1, Tanktemperature_C = 18 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 12, 1), Odometer_km = 140, Pressure_bar = 3, Tanktemperature_C = 16 },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 31, 1), Odometer_km = 156, Pressure_bar = 2, Tanktemperature_C = 17 }&nbsp; &nbsp; &nbsp; &nbsp; };&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; var groupedbyhour = entries.GroupBy(entry => entry.epochasdate.Date.AddHours(entry.epochasdate.Hour))//group by hour&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(HourGroup => new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TankTemp = HourGroup.Average(x => x.Tanktemperature_C),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Bar = HourGroup.Average(x => x.Pressure_bar),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MinOdo = HourGroup.Min(x => x.Odometer_km),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MaxOdo = HourGroup.Max(x => x.Odometer_km),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MeasureTime = HourGroup.Max(x => x.epochasdate.Date.AddHours(x.epochasdate.Hour))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}).ToList();&nbsp; &nbsp; }}如果您的 epochasdate 类型不是日期时间并且由于某种原因无法分组，请使用 .addhour 并向值添加 0 小时，它应该返回您的 groupby 功能可以理解的 DateTime 结果