如何将这个内连接子查询从 SQL 重写为 Lambda

Question

SELECT     ulcch.ID, ulcch.UserLoginHistoryID, ulcch.StatusID, 
ulcch.ClientModuleID, ulcch.DeviceState, ulcch.UpdatedAt, ulcch.CreatedAt
FROM         UserLoginClientConnectionHistory AS ulcch INNER JOIN
  (SELECT     MAX(CreatedAt) AS maxCreatedAt
    FROM          UserLoginClientConnectionHistory AS ulcch1
    GROUP BY UserLoginHistoryID) AS m ON m.maxCreatedAt = ulcch.CreatedAt

每天可能会有许多“设备状态”更新被审核到此登录表中。此查询返回每天的最后一个唯一的查询。

我希望将其重写为 Lambda 语句。这就是我已经走了多远，我不知道我是否走在正确的轨道上，并且我的 Max() 抛出了类型错误，可能是因为 group by 正在制作另一个列表或其他东西... 希望你能从我的对象示例中解决这个问题......：S

userLogin.UserLoginClientConnectionHistories.Where(x => x.CreatedAt ==
  userLoginClientConnectionHistoryRepository.GetAll(
    GenericStatus.Active).GroupBy(y => y.UserLoginHistoryID).Max(y => y.CreatedAt));

Answer 1

我认为这符合您的要求：

        var result = userLogin.UserLoginClientConnectionHistories
            .GroupBy(y => new { Id = y.UserLoginHistoryID, Day = y.CreatedAt.Date })
            .Select(x => new
            {
                Id = x.Key.Id,
                Day = x.Key.Day,
                MostRecent = x.Max(y => y.CreatedAt)
            });

这是一个测试平台：

public class Program
{
    class LoginEntry
    {
        public int UserLoginHistoryID { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    class UserLogin
    {
        public List<LoginEntry> UserLoginClientConnectionHistories = new List<LoginEntry>();
    }

    public static void Main(string[] args)
    {
        UserLogin userLogin = new UserLogin();
        userLogin.UserLoginClientConnectionHistories = new List<LoginEntry> {
            new LoginEntry {UserLoginHistoryID = 1, CreatedAt = new DateTime(2009, 1, 1, 3, 0 ,0)},
            new LoginEntry {UserLoginHistoryID = 1, CreatedAt = new DateTime(2009, 1, 1, 15, 0 ,0)},
            new LoginEntry {UserLoginHistoryID = 1, CreatedAt = new DateTime(2009, 1, 3, 11, 0 ,0)},
            new LoginEntry {UserLoginHistoryID = 1, CreatedAt = new DateTime(2009, 1, 1, 10, 0 ,0)},
            new LoginEntry {UserLoginHistoryID = 2, CreatedAt = new DateTime(2009, 1, 3, 4, 0 ,0)},
            new LoginEntry {UserLoginHistoryID = 2, CreatedAt = new DateTime(2009, 1, 3, 5, 0 ,0)},
        };

        var result = userLogin.UserLoginClientConnectionHistories
            .GroupBy(y => new { Id = y.UserLoginHistoryID, Day = y.CreatedAt.Date })
            .Select(x => new
            {
                Id = x.Key.Id,
                Day = x.Key.Day,
                MostRecent = x.Max(y => y.CreatedAt)
            });

        foreach (var item in result)
        {
            Console.WriteLine("User {0}, day {1}, most recent {2}",
                item.Id,
                item.Day,
                item.MostRecent);
        }
    }
}

输出：

User 1, day 01-01-2009 00:00:00, most recent 01-01-2009 15:00:00
User 1, day 03-01-2009 00:00:00, most recent 03-01-2009 11:00:00
User 2, day 03-01-2009 00:00:00, most recent 03-01-2009 05:00:00

Answer 2

这是作为 lambda 的内部连接部分。我假设 CreatedAt 是一个日期时间。

用户登录客户端连接历史记录
.GroupBy(ulcch1 =>;
新
{
名称 = ulcch1.名称
})
.选择(g =>
新
{
maxCreatedAt = (DateTime?)(g.Max (p => p.CreatedAt))
})

Answer 3

我认为您想按 CreatedAt 而不是 UserLoginHistoryID 进行分组：

var q = userLogin.UserLoginClientConnectionHistories
            .GroupBy(h => h.CreatedAt)
            .OrderByDescending(g => g.Key) // Sort by CreatedAt
            .First()
            .Select(h => new { h.Id, h.UserLoginHistoryID, ... });

这将返回共享最新 CreatedAt 的 UserLoginClientConnectionHistory 条目集值。

Answer 4

感谢你们所有的帮助，我已经投票给你们了，但你们不会相信，但几个小时后我搜索了一个将 SQL 转换为 LINQ 的程序，令我惊讶的是发现了一个名为“Linqer"。听起来很疯狂，没想到会走得太远，但它工作得很好。如果其他人陷入同一条船上，绝对值得检查该应用程序...

检查它返回的庞大查询！分析完之后，不觉得多了额外的臃肿吗？有人有任何优化技巧或发现任何不必要的代码吗？

        moduleDeviceStates = from ulh in user.UserLoginHistories
                             join ulcch in userLogin.UserLoginClientConnectionHistories on new { ID = ulh.ID } equals new { ID = ulcch.UserLoginHistoryID }
                             join cm in clientModuleRepository.GetAll(GenericStatus.Active) on new { ClientModuleID = ulcch.ClientModuleID } equals new { ClientModuleID = cm.ID }
                             join mo in moduleRepository.GetAll(GenericStatus.Active) on new { ModuleID = cm.ModuleID } equals new { ModuleID = mo.ID }
                             join m in
                                 (
                                     (from ulcch1 in userLogin.UserLoginClientConnectionHistories
                                      group ulcch1 by new
                                      {
                                          ulcch1.UserLoginHistoryID
                                      } into g
                                      select new
                                      {
                                          maxCreatedAt = g.Max(p => p.CreatedAt)
                                      })) on new { maxCreatedAt = ulcch.CreatedAt } equals new { maxCreatedAt = m.maxCreatedAt }
                             select new ModuleDeviceState()
                             {
                                 ModuleID = mo.ID,
                                 Name = mo.Name,
                                 DeviceState = (State.DeviceState)ulcch.DeviceState,
                                 CreatedAt = ulcch.CreatedAt
                             };

为你的帮助干杯 dahlbyk，但我确实想对 UserLoginHistoryID 进行分组，在深入研究 lambda 等效项之前，我已经在 SQL 中确认了我的查询:) 谢谢。

---

@Mark Thanks for taking the time to reply, yes I do what the [last] entries per user (userloginhistory.. which in turn contains a userID) for each day, and exporting my sql into the linq query did produce what I wanted (which can be seen in the query result below; this is what I want. The reason you see double entries for each day is because there are also attached ClientModule's.. so I really want all client module, per login entry per day - so hard to get a programming requirement across over a discussion forum argh!) Perhaps yours does exactly the same thing (it appears to if I am reading your output correctly) just a lot more streamlined.

我对您使用 GroupBy 和 Select 所做的匿名转换不太了解，但现在我明白了，它是有道理的。我也许可以试试你的。希望我可以对其进行调整，以包含每天不同的 ClientModule。所以无论如何..这是我的 SQL 的查询结果，实际上是我通过自己的 lambda 得到的结果：

ID  UserLoginHistoryID  StatusID    ClientModuleID  DeviceState UpdatedAt   CreatedAt
277 62  1   1   4   NULL    2009-10-31 13:28:59.003
278 62  1   16  4   NULL    2009-10-31 13:28:59.003
331 65  1   1   4   NULL    2009-10-31 17:13:28.333
332 65  1   16  4   NULL    2009-10-31 17:13:28.333

---

Update Mark: Hi again, well after a couple of tweaks on your query, I could produce the same object graph in .NET between both lambda statements. This is the one I will use now, derived from yours as it's more streamlined and easier to understand than the auto-gen'd one and I will award you the points :)

我向 Group By 添加了更多条目，因为我的 new ModuleDeviceState 类需要这些条目。

moduleDeviceStates = userLogin.UserLoginClientConnectionHistories
                    .GroupBy(y => new { Id = y.UserLoginHistoryID, 
                        CreatedAt = y.CreatedAt.Date, 
                        ModuleID = y.ClientModule.ModuleID, 
                        ModuleName = y.ClientModule.Module.Name,
                        DeviceState = y.DeviceState })
                    .Select(x => new ModuleDeviceState()
                                     {
                                         ModuleID = x.Key.ModuleID, 
                                         Name = x.Key.ModuleName,
                                         DeviceState = (State.DeviceState)x.Key.DeviceState,
                                         CreatedAt = x.Max(y => y.CreatedAt)
                                     });