Issue with EF Core Query-CodePudding

I am trying to create a query for an entity that has a one to many relationship to another entity in EF core.

Here are the two entities:

public class Computer {
  public int ComputerId { get; set; }
  public string Hostname { get; set; }
  public string IpAddress { get; set; }

  public ICollection<Event> Events { get; set; }
}

public class Event {
  public int EventId { get; set; }
  public string Description { get; set; }
  public DateTime EventTime { get; set; }
  
  public Computer Computer { get; set; }
}

I am trying to find out how I would go about querying all Computer records with the EventId of the newest Event.

I understand how to do simple queries on each, but am confused how I would go about writing a query that would return the data I need from both.

Thanks in advance!

CodePudding user response：

Newer versions of EFC (5 ) understand how to turn this into a ROW_NUMBER OVER, which is an efficient way to get what you're after:

context.Computers
  .Select(c => new {
    Computer = c, //or just take some properties from it
    RecentEvent = c.Events.OrderByDescending(e => e.EventTime).FirstOrDefault()
  });

It gets written to something like:

SELECT * 
FROM 
  Computers c
  INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ComputerId ORDER BY EventTime DESC) rn
    FROM Events
  ) e ON c.ComputerId = e.ComputerId AND rn = 1

It's the "and rn=1" that does the magic

This gets the whole of each object, but you can select just the ID if you want (I assumed you wanted more data from the event)

CodePudding user response：

Off the top of my head, what you want is probably something close to this. I might have my clauses ordered wrong but I always need to monkey around with EFCore queries.

using var ctx = new MyDatabaseContext();

var newestEventsForEachComputer = 
    ctx.Events
       .AsNoTracking()
       .Include(e => e.Computer)             
       .OrderByDescending(e => e.EventTime)
       .GroupBy(e => e.Computer.Id)
       .Select(g => g.First())
       .ToList();

Note: I am assuming it is valid to have the .Include clause there given your description of the table linking.

CodePudding user response：

You can try this, the result will be a list of Computers and they will have a list with only one Event with the latest id

dbContext.Computer.Include(x => x.Event.OrderByDescending(x => x.Id).Take(1)).Select(x => x).ToList();