Linq query with subquery

Question

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 ? :)

Answer 1

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();