How to select top result for a given ID then join that into another table EF Core LINQ

Question

For the life of me I am unable to google my way out of this one.

I have 2 tables within a database 1. Computers 2. UserLogins

Essentially, I'm trying to get the latest login entry from the "UserLogins" table, and join it with the corresponding entry in the "Computers" table.

This sounds simple enough, but I haven't sat through enough LINQ/EF Core courses yet to figure out how to do this correctly it seems.

Here is some SQL that I know functions how I expect it to:

SELECT * FROM ComputerInfo 
LEFT JOIN (
    SELECT LoginID, UserID, l.ComputerName, IpAddress, l.LoginTime FROM UserLogins as l
    INNER JOIN (
    SELECT ComputerName, MAX(LoginTime) as LoginTime
    FROM UserLogins
    GROUP BY ComputerName) as max on max.ComputerName = l.ComputerName and max.LoginTime = l.LoginTime
    ) as toplogin on toplogin.ComputerName = ComputerInfo.ComputerName

For reference, I am going to be implementing this in my Controller.cs class, and I am using : EF Core (3.1.2) ASP.NET Core (3.1)

I do have a couple queries I was experimenting with that return the results, but I can't join them without errors:

var computerQuery = _context.ComputerInfo
                    .OrderBy(on => on.ComputerName)

var userQuery = _context.UserLogins
                .Select(p => p.ComputerName)
                .Distinct()
                .Select(id => _context.UserLogins
                .OrderByDescending(p => p.LoginTime)
                .FirstOrDefault(p => p.ComputerName == id))
                .ToListAsync();

Codestripper · Accepted Answer · 2020-04-09T19:35:40.957

So I kind of found a shotty way to get this done I think. Not sure if it is correct but here is what I came up with:

I Created a new class called "ComputerInfoFull" which basically was just "ComputerInfo" && "UserLogins" combined, and used this for the linq query:

var initial = from computerInfo in _context.ComputerInfo
                          from userInfo in _context.UserLogins
                              .Where(o => o.ComputerName == computerInfo.ComputerName)
                              .OrderByDescending(o => o.LoginTime).Take(1)
                          select new ComputerInfoFull(computerInfo, userInfo);

I'm very sure there is a cleaner Lambda way of writing this, but I can't figure out how to make it work right. Too much stuff going on for my tiny brain to handle lol. If anyone has any ideas on how I can make this cleaner please let me know so I can learn.

How to select top result for a given ID then join that into another table EF Core LINQ

1 Answers1