Entity Framework - how to select first row each IDNumber by one query Using lambda

Question

I have orders table with IdNumber that is the ID of the user, I need to get the last order of each user (the InsuranceDate is the date).

The problem is how to get the last order if one user can have many orders. how to do it with one query without huge foreach after the entity query ?

public class Order
{
    [Key]
    public int OrderID { get; set; }        
    public string IdNumber { get; set; }
    public int Price { get; set; }
    public Datetime InsuranceDate { get; set; }
}

This is an awfully similar question: http://stackoverflow.com/questions/470440/how-to-select-only-the-records-with-the-highest-date-in-linq — jdnew18, Feb 11 '15 at 21:20

ocuenca · Accepted Answer · 2015-02-11T22:46:41.910

2

Try with this solution:

  var r = db.Orders.GroupBy(o => o.IdNumber).Select(g => g.OrderByDescending(o => o.InsuranceDate).FirstOrDefault());

First, you need to group your orders by the user id. Then, sort in descending order each group and select only the first. This way you will get the last order of each user.

edited Feb 11 '15 at 22:46

answered Feb 11 '15 at 22:37

ocuenca

38,548
11
89
102

score 0 · Answer 2 · answered Feb 11 '15 at 22:19

0

You can try something like this: var result = Orders.OrderByDescending(o => o.InsuranceDate).FirstOrDefault(x => x.IdNumber == userID); where userID is the user's ID that you want to match.

answered Feb 11 '15 at 22:19

DrewJordan

5,266
1
25
39

Entity Framework - how to select first row each IDNumber by one query Using lambda

2 Answers2