CODEWITHSUNDEEP
c#entity-framework-core
AutoGnome
AutoGnome

Reputation: 11

Issue with an EF Core query

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

Answers (4)

Caius Jard
Caius Jard

Reputation: 74710

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

Joe
Joe

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

Shakir Ahmed
Shakir Ahmed

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

Darkk L
Darkk L

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

Related Questions