title: How to select top result for a given ID then join that into another table EF Core LINQ
body: <p>For the life of me I am unable to google my way out of this one. </p>
  
  <p>I have 2 tables within a database
  1. Computers
  2. UserLogins</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is some SQL that I know functions how I expect it to:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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)</p>
  
  <p>I do have a couple queries I was experimenting with that return the results, but I can't join them without errors:</p>
  
  <pre><code>var computerQuery = _context.ComputerInfo
                      .OrderBy(on =&gt; on.ComputerName)
  
  var userQuery = _context.UserLogins
                  .Select(p =&gt; p.ComputerName)
                  .Distinct()
                  .Select(id =&gt; _context.UserLogins
                  .OrderByDescending(p =&gt; p.LoginTime)
                  .FirstOrDefault(p =&gt; p.ComputerName == id))
                  .ToListAsync();
  </code></pre>