CODEWITHSUNDEEP
c#linq
mardok
mardok

Reputation: 2425

Linq query with subquery

I have entity called UserPaymentHistory with Id, UserId, Price, PaymentDate. I need to get that row which will have a User with last PaymentDate.

I can do query like this:

var lastPaymentDate =
    db.Repository<UserPayment>()
            .Where(u => u.UserId == 1)
            .Select(x => x.PaymentDate)
            .Max();

And then:

var userPayment = db.Repository<UserPayment>()
                      .Where(u => u.UserId == request.UserId 
                      && u.PaymentDate == lastPaymentDate).Single();

Is any way that I could get that record in one query ? :)

Upvotes: 3

Views: 100

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236308

Order payments by PaymentDate in descending order and select first one:

var userPayment = db.Repository<UserPayment>()
                      .Where(u => u.UserId == request.UserId)
                      .OrderByDescending(u => u.PaymentDate)
                      .FirstOrDefault();

Upvotes: 6

Related Questions