Reputation: 11
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, how would I go about writing a query that would return the data I need from both?
Upvotes: 0
Views: 119
Reputation: 74700
Newer versions of EF Core (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).
Upvotes: 2
Reputation: 6816
Off the top of my head, you probably want something close to this. I might have my clauses ordered wrong, but I always need to monkey around with EF Core 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.
Upvotes: 0
Reputation: 547
First of all, you need to build a proper relationship between models. If you go with the code first approach. follow the below code snippets
public class Computer
{
[Key]
public int ComputerId { get; set; }
public string Hostname { get; set; }
public string IpAddress { get; set; }
[InverseProperty("Computer")]
public ICollection<Event> Events { get; set; }
}
public class Event
{
[Key]
public int EventId { get; set; }
public string Description { get; set; }
public DateTime EventTime { get; set; }
public Computer Computer { get; set; }
}
Now you have created a good relationship between the computer and the event model.
Now, the computer entity has a collection of its own events and those events has the same computer entity.
But you cannot get collection of computers just by querying a single eventId. Because your relationship is not like that. You can query like this
[HttpGet]
public async Task<IActionResult> GetByIdAsync(int id)
{
var result = await _context.Computers.Include(a => a.Events).FirstOrDefaultAsync(a => a.ComputerId == id);
return new JsonResult(result);
}
This will return a specific computer with related events.
Upvotes: 0
Reputation: 1035
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();
Upvotes: 0