How to re-write this inner join subquery from SQL to 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

There can be many updates of 'device state' per day audited into this login table. This query returns the last unique one for each day.

I would like this re-written as a Lambda statement. This is how far I got, I don't know if i'm on the right track, and my Max() is throwing a type error, probably because the group by is making another list or something... Hope you can work it out from my object examples.... :S

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

Answer 1

Here is the inner join portion as a lambda. I assumed CreatedAt was a dateTime.

UserLoginClientConnectionHistory .GroupBy (ulcch1 => new
{ Name = ulcch1.Name }) .Select (g => new
{ maxCreatedAt = (DateTime?)(g.Max (p => p.CreatedAt)) })

Answer 2

I think you want to group by CreatedAt rather than UserLoginHistoryID:

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

This will return the set of UserLoginClientConnectionHistory entries that share the most recent CreatedAt value.

Answer 3

Thanks for all your help guys, i've voted you all up, but you wouldn't believe it but a few hours later I searched for a program to convert SQL to LINQ, and to my surprise found one called "Linqer". Sounds crazy and didn't expect to get far, but it worked perfectly.. definitely worth checking out that app if anyone else gets stuck in the same boat...

Check the mammoth query it returned! After analysing it, don't think it's got extra bloat? Anyone have any optimisation tips or spot any unnecessary code?

        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
                             };

Cheers for your help dahlbyk, but I did want to group on UserLoginHistoryID, I had my query confirmed in SQL before delving into a lambda equivalent :) thanks.

---

@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.

See I didn't know too much about the anon casting you've done there with GroupBy and Select, but now I see it, it makes sense. I might give yours a go. Hopefully I can give it a tweak to include distinct ClientModule's per day too. So anyway.. here is the query result from my SQL, and effectively what I got through my own 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 :)

I added a few more entries to the Group By as I need that for my new ModuleDeviceState class.

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)
                                     });

Answer 4

I think this does what you want:

        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)
            });

Here is a testbed for it:

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);
        }
    }
}

Output:

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