将 linq 查询转换为 lambda 语法

本文关键字：lambda 语法 转换 linq 查询 | 更新日期: 2023-09-27 18:34:28

我想在按天和小时发送消息时显示热图，我写了一些linq查询。

原始 SQL 查询

SELECT weekday, hr , count(message.hour)
FROM (SELECT days.dow as weekday, hours.hour as hr from days left join hours,message on message.dow = days.dow group by days.dow, hours.hour)
LEFT JOIN message on message.dow = weekday and message.hour = hr group by weekday, hr

消息类

public class Message
{
    public long Id { get; set; }
    public string Text { get; set; }
    public DateTime Timestamp { get; set; }
}

LINQ 查询(它有效(

int[] Hours = { 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 };
int[] Days = { 0, 1, 2, 3, 4, 5, 6 };
List<Message> messages = new List<Message>();
/*
  Load Message data from csv file
  ...
*/
var heatmap = from d in
                  (from message in Messages
                  join h in Hours on message.Timestamp.Hour equals h
                  join d in Days on (int)message.Timestamp.DayOfWeek equals d
                  orderby d, h
                  group new { d, h } by new { d, h } into g
                  select new { Weekday = g.Key.d, Hour = g.Key.h })
              join message in Messages on new { d.Weekday, d.Hour } equals new { Weekday = (int)message.Timestamp.DayOfWeek, Hour = message.Timestamp.Hour }
              group new { d.Weekday, d.Hour } by new { d.Weekday, d.Hour } into g
              select new { Weekday = g.Key.Weekday, Hour = g.Key.Hour, Total = g.Count() };

示例结果热图数据

      Weekday    Hour    Total
[0]     0         0       120
[1]     0         1       57
[2]     0         2       79
...
[167]   6         23      89

这是我将 LINQ 查询转换为 lambda 语法代码的代码，但它不起作用！ :( 

int[] Hours = { 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 };
int[] Days = { 0, 1, 2, 3, 4, 5, 6 };
List<Message> messages = new List<Message>();
/*
  Load Message data from csv file
  ...
*/
var heatmapLambda = (Messages.Join(Hours, message => message.Timestamp.Hour, h => h, (m, h) => new { m, h })
                                  .Join(Days, m => (int)m.m.Timestamp.DayOfWeek, d => d, (m,d) => new { m, d })
                                  .OrderBy(m => new {m.d, m.m.h})
                                  .GroupBy(g => new {g.d, g.m.h})
                                  .Select(r => new {
                                      Weekday = r.Key.d,
                                      Hour = r.Key.h
                                  }))
                              .Join(Messages, d => new {d.Weekday, d.Hour}, m => new { Weekday = (int)m.Timestamp.DayOfWeek, Hour = m.Timestamp.Hour}, (d,h) => new { d.Weekday, d.Hour})
                              .GroupBy(g => new { g.Weekday, g.Hour})
                              .Select(r => new { Weekday = r.Key.Weekday, Hour = r.Key.Hour, Count = r.Count() });

heatmapLambda没有任何项目。 我的 LINQ 查询的正确 lambda 表达式是什么？

将 linq 查询转换为 lambda 语法

在我看来，您正在做很多不必要的连接。

当您执行以下操作时：

from message in Messages
join h in Hours on message.Timestamp.Hour equals h
join d in Days on (int)message.Timestamp.DayOfWeek equals d

。它与：

    from message in Messages
    let h = message.Timestamp.Hour
    let d = (int)message.Timestamp.DayOfWeek

然后当你这样做时：

join message in Messages on new { d.Weekday, d.Hour } equals new { Weekday = (int)message.Timestamp.DayOfWeek, Hour = message.Timestamp.Hour }

。您基本上会重新加入原始消息。

我认为这是等效的查询：

var heatmap =
    from message in Messages
    let Hour = message.Timestamp.Hour
    let Weekday = (int)message.Timestamp.DayOfWeek
    orderby Weekday, Hour
    group message by new { Weekday, Hour } into gms
    select new { gms.Key.Weekday, gms.Key.Hour, Count = gms.Count() };

。因此，如果是这种情况，那么这就是您使用 lambda 所需要的：

var heatmap =
    Messages
        .OrderBy(message => (int)message.Timestamp.DayOfWeek)
        .ThenBy(message => message.Timestamp.Hour)
        .GroupBy(message => new { Weekday = (int)message.Timestamp.DayOfWeek, message.Timestamp.Hour })
        .Select(gms => new { gms.Key.Weekday, gms.Key.Hour, Count = gms.Count() });

IMO 查询的主要目标应该是以最佳方式获得正确的结果，而不是使用的语法(查询、方法或混合(。

在您的情况下，查询远非最佳 - 不必要的联接、分组和排序。从我所看到的，您正在尝试从Days和Hours集合中获取每个(weekday, hour)组合的消息计数，这可以通过简单的笛卡尔乘积与像这样的组连接相结合来实现

var query = 
    from key in (from d in Days from h in Hours select new { Weekday = d, Hour = h })
    join m in Messages
    on key equals new { Weekday = (int)m.Timstamp.DayOfWeek, m.Timestamp.Hour } into items
    select new { key.Weekday, key.Hour, Total = items.Count() };

通常，在使用联接和/或分组时，由于标识符透明，使用查询语法更容易、更自然。在这种特殊情况下，方法语法没有太大的不同，但您需要知道如何映射构造：

var query = 
    Days.SelectMany(d => Hours, (d, h) => new { Weekday = d, Hour = h })
    .GroupJoin(Messages, 
        key => key, m => new { Weekday = (int)m.Timstamp.DayOfWeek, m.Timestamp.Hour },
        (key, items) => new { key.Weekday, key.Hour, Total = items.Count() });
相关文章：