title: How to select the maximum value of a column and retrieve multiple results?
body: <p>I am struggling to understand how to construct my SQL query using Entity Framework 6 and SQL Server in my ASP.NET Web API v2 project.</p>
  
  <p>My table structure: the <code>Computers</code> table is my root table for all computers that have been inventoried by my software. It contains the column <code>ComputerID</code> which is used as a foreign key by other tables.</p>
  
  <p>I can retrieve a computer including all information I have on it by using the query below:</p>
  
  <pre><code>Computer computer = ComputersDbContext.Computers
                     ... more includes....
                      .Include("Computer_Win_Installed_Software")
                     ... more includes....
                      .Where(a =&gt; a.ComputerId == computerId)
                         .First(t =&gt; t.TenantId == tenantId);
  </code></pre>
  
  <p>As you can see, I have another table called <code>Computer_Win_Installed_Software</code> which stores all software installed on a system. It looks like this:</p>
  
  <pre><code>IdentityKey | ComputerID (FK) | Softwarename     | EntryTimestamp
  ------------+-----------------+------------------+--------------
            1 |               1 | Some Software    | 1547241345 
            2 |               1 | Another Software | 1547241345 
  </code></pre>
  
  <p><code>EntryTimestamp</code> is a Unix timestamp which is unique to every inventory run and is the same for all software discovered on that run. Now if the system gets inventoried again, the table will look like this:</p>
  
  <pre><code>IdentityKey | ComputerID (FK) | Softwarename       | EntryTimestamp
  ------------+-----------------+--------------------+---------------
            1 |               1 | Some Software      |     1547241345 
            2 |               1 | Another Software   |     1547241345 
            3 |               1 | Some Software      |     1886454564 
            4 |               1 | Another Software   |     1886454564 
            5 |               1 | Even More Software |     1886454564 
  </code></pre>
  
  <p>I want to keep historic data so I need to keep the old entries.</p>
  
  <p>My problem is that my EF query from above will return ALL of these entries in the resulting object.</p>
  
  <p>How do I change my query so that it only returns:</p>
  
  <pre><code>IdentityKey | ComputerID (FK) | Softwarename       | EntryTimestamp
  ------------+-----------------+--------------------+---------------
            3 |               1 | Some Software      |     1886454564 
            4 |               1 | Another Software   |     1886454564 
            5 |               1 | Even More Software |     1886454564 
  </code></pre>
  
  <p>I thought about using 2 queries:</p>
  
  <ul>
  <li>First query: I check for the max value of <code>EntryTimestamp</code></li>
  <li><p>Second query: something like this:</p>
  
  <pre><code>Computer computer = ComputersDbContext.Computers
                          .Include("Computer_Win_Installed_Software")      
                          .Where(a =&gt; a.ComputerId == computerId)
                          .Where(b =&gt; b.Computer_Win_Installed_Software.EntryTimestamp == EntryTimestamp)
                          .First(t =&gt; t.TenantId == tenantId);
  </code></pre></li>
  </ul>
  
  <p>But Intellisense immediately screams at me :D</p>
  
  <p>I also thought about only selecting the <code>MAX()</code> of the column <code>EntryTimestamp</code>; but I cannot even use <code>b.Computer_Win_Installed_Software.EntryTimestamp</code> in the query code.</p>
  
  <p>When I write: <code>.Where(b =&gt; b.Computer_Win_Installed_Software</code>, it doesn't list any of the columns as available options.</p>
  
  <p>I think this is because the <code>Computer_Win_Installed_Software</code> class in EF is of type <code>ICollection&lt;Computer_Win_Installed_Software&gt;</code>. It is the same problem with other tables which all have a 1 to many relationship to the <code>Computers</code> table.
  Another table has a 1 to 1 relationship to the <code>Computers</code> table and there I can select all columns. </p>
  
  <p>I'm super confused.</p>
  
  <p>And yes I did google but I could not find anything that helped me out. What's the right way to go here?</p>
  
  <p>Edit: Added Models </p>
  
  <p>DBContext:</p>
  
  <pre><code>public class DbEntities : DbContext
  {
      public virtual DbSet&lt;Agent&gt; Agents { get; set; }     
      public virtual DbSet&lt;Computer_Win_Installed_Software&gt; ComputerWinInstalledSoftware { get; set; }      
      public DbSet&lt;Computer&gt; Computers { get; set; }
  }
  </code></pre>
  
  <p>EF Model for the Computer:</p>
  
  <pre><code>//------------------------------------------------------------------------------
  // &lt;auto-generated&gt;
  //     This code was generated from a template.
  //
  //     Manual changes to this file may cause unexpected behavior in your application.
  //     Manual changes to this file will be overwritten if the code is regenerated.
  // &lt;/auto-generated&gt;
  //------------------------------------------------------------------------------
  
  namespace ProjectName
  {
      using System;
      using System.Collections.Generic;
  
      public partial class Computer
      {
          [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
          public Computer()
          {
              this.Computer_Win_Installed_Software = new HashSet&lt;Computer_Win_Installed_Software&gt;();            
          }
  
          public int ComputerId { get; set; }
          public int TenantId { get; set; }
          public virtual Agent Agent { get; set; }
          [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
          public virtual ICollection&lt;Computer_Win_Installed_Software&gt; Computer_Win_Installed_Software { get; set; }
  
      }
  }
  </code></pre>
  
  <p>EF Model for Computer_Win_Installed_Software:</p>
  
  <pre><code>//------------------------------------------------------------------------------
  // &lt;auto-generated&gt;
  //     This code was generated from a template.
  //
  //     Manual changes to this file may cause unexpected behavior in your application.
  //     Manual changes to this file will be overwritten if the code is regenerated.
  // &lt;/auto-generated&gt;
  //------------------------------------------------------------------------------
  
  namespace KysoWebApi
  {
      using System;
      using System.Collections.Generic;
  
      public partial class Computer_Win_Installed_Software
      {
          public int ComputerId { get; set; }
          public string SoftwareName { get; set; }
          public Nullable&lt;double&gt; SoftwareVersion { get; set; }
          public Nullable&lt;bool&gt; IsServerSoftware { get; set; }
          public Nullable&lt;int&gt; SoftwareVendorId { get; set; }
          public string SoftwareIs32or64bits { get; set; }
          public int ComputerWinInstalledSoftwareEntryId { get; set; }
          public string EntryTimestamp { get; set; }
  
          public virtual Computer Computer { get; set; }
      }
  }
  </code></pre>