How to select the maximum value of a column and retrieve multiple results?

Question

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.

My table structure: the Computers table is my root table for all computers that have been inventoried by my software. It contains the column ComputerID which is used as a foreign key by other tables.

I can retrieve a computer including all information I have on it by using the query below:

Computer computer = ComputersDbContext.Computers
                   ... more includes....
                    .Include("Computer_Win_Installed_Software")
                   ... more includes....
                    .Where(a => a.ComputerId == computerId)
                       .First(t => t.TenantId == tenantId);

As you can see, I have another table called Computer_Win_Installed_Software which stores all software installed on a system. It looks like this:

IdentityKey | ComputerID (FK) | Softwarename     | EntryTimestamp
------------+-----------------+------------------+--------------
          1 |               1 | Some Software    | 1547241345 
          2 |               1 | Another Software | 1547241345 

EntryTimestamp 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:

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 

I want to keep historic data so I need to keep the old entries.

My problem is that my EF query from above will return ALL of these entries in the resulting object.

How do I change my query so that it only returns:

IdentityKey | ComputerID (FK) | Softwarename       | EntryTimestamp
------------+-----------------+--------------------+---------------
          3 |               1 | Some Software      |     1886454564 
          4 |               1 | Another Software   |     1886454564 
          5 |               1 | Even More Software |     1886454564 

I thought about using 2 queries:

• First query: I check for the max value of EntryTimestamp

• Second query: something like this:

  Computer computer = ComputersDbContext.Computers
                          .Include("Computer_Win_Installed_Software")      
                          .Where(a => a.ComputerId == computerId)
                          .Where(b => b.Computer_Win_Installed_Software.EntryTimestamp == EntryTimestamp)
                          .First(t => t.TenantId == tenantId);
  

But Intellisense immediately screams at me :D

I also thought about only selecting the MAX() of the column EntryTimestamp; but I cannot even use b.Computer_Win_Installed_Software.EntryTimestamp in the query code.

When I write: .Where(b => b.Computer_Win_Installed_Software, it doesn't list any of the columns as available options.

I think this is because the Computer_Win_Installed_Software class in EF is of type ICollection<Computer_Win_Installed_Software>. It is the same problem with other tables which all have a 1 to many relationship to the Computers table. Another table has a 1 to 1 relationship to the Computers table and there I can select all columns.

I'm super confused.

And yes I did google but I could not find anything that helped me out. What's the right way to go here?

Edit: Added Models

DBContext:

public class DbEntities : DbContext
{
    public virtual DbSet<Agent> Agents { get; set; }     
    public virtual DbSet<Computer_Win_Installed_Software> ComputerWinInstalledSoftware { get; set; }      
    public DbSet<Computer> Computers { get; set; }
}

EF Model for the Computer:

//------------------------------------------------------------------------------
// <auto-generated>
//     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.
// </auto-generated>
//------------------------------------------------------------------------------

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

        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<Computer_Win_Installed_Software> Computer_Win_Installed_Software { get; set; }

    }
}

EF Model for Computer_Win_Installed_Software:

//------------------------------------------------------------------------------
// <auto-generated>
//     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.
// </auto-generated>
//------------------------------------------------------------------------------

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<double> SoftwareVersion { get; set; }
        public Nullable<bool> IsServerSoftware { get; set; }
        public Nullable<int> SoftwareVendorId { get; set; }
        public string SoftwareIs32or64bits { get; set; }
        public int ComputerWinInstalledSoftwareEntryId { get; set; }
        public string EntryTimestamp { get; set; }

        public virtual Computer Computer { get; set; }
    }
}

Answer 1

So, for a given Computer, the ICollection<Computer_Win_Installed_Software> Computer_Win_Installed_Software is always going to include all related rows with all EntryTimestampts. You can't (easily) filter the entities included.

So, instead, just return the filtered entities:

var computer = ComputersDbContext.Computers
                .Where(a => a.ComputerId == computerId)
                .First(t => t.TenantId == tenantId);
var cwis = computer
            .Computer_Win_Installed_Software
                .Where(b => b.EntryTimestamp == EntryTimestamp);

Of course, I'm not sure this is what you actually want to do, you may have an XY Problem.

Can you try not including the related objects, but just querying against them?

var computer = ComputersDbContext.Computers
                .Where(a => a.ComputerId == computerId)
                .First(t => t.TenantId == tenantId);
                .Where(b => b.Computer_Win_Installed_Software.EntryTimestamp == EntryTimestamp);

Answer 2

Please try this :

            var computer = db.ComputerWinInstalledSoftware
                .Include("Computer")
                .First(c => c.Computer.ComputerId == computerId 
                && c.Computer.TenantId == tenantId 
                && c.EntryTimestamp == EntryTimestamp ).Computer;

Answer 3

Try this:

Computer computer = context.Computers
   .Include(c => c.Computer_Win_Installed_Software.Join(
       c.Computer_Win_Installed_Software.Where(a => a.ComputerId == computerId && a.Computer.TenantId == tenantId)
       .GroupBy(s => s.Softwarename)
       .Select(g => new { SoftwareName = g.Key, MaxDate = g.Max(r => r.EntryTimestamp) })
       ,o => o.EntryTimestamp
       ,i => i.MaxDate
       , (o,i) => o))
   .First(a => a.ComputerId == computerId && a.TenantId == tenantId);

I don't see the TenantId column anywhere on your Entity definition so just make sure to put the correct field in there.

What this is doing is joining to the records by max date.

UPDATE: I made a small correction. It should be grouping by Softwarename. Change the grouping to your liking so it correctly identifies each piece of software uniquely.

Answer 4

Alright. After lots of research I found several post stating that you cannot filter within include stattements and that it is an all or nothing kind of deal. Filtering at the end gives strange results all the time.

Sources: Entity Framework - Selective Condition on Included Navigation Property

Does Include load all related entities or the specified ones?

I was able to find 2 solutions: DynamicSelectExtensions: https://github.com/thiscode/DynamicSelectExtensions

There seems to beEntityFramework+ (EF+). I have seen post of the developers on Stackoverflow, although I cannot find them at the moment. His library seems to be able to do what I want to do, but normal EF is not and it seems it will never be, as the feature request for this dates back several years.

Thank you everyone for trying to help me and investing your time. For now, I will go with multiple queries and maybe check back at a later time, hopefully with more exqerience :D