select last row from details table for each user

Question

We have 2 tables

1. tbl_members In this table we have all members details like memberId, name, age, etc

2. tbl_payment In this table we have all their payment details like paymentId, memberId, PaymentDate, etc

We want to select all of our user's names and display only their last paymentDate and Amount

Name   PaymentDate   Amount
----------------------------
Row1   12/12/2011    1200
Row2   12/18/2011    100
Row3   12/16/2011    99
Row4   12/08/2011    1500

How can this be done using linq?

Answer 1

You should use grouping to group payments by user and then select top element by its PaymentDate. Take a look at that question: Linq - Top value form each group.

tbl_payment.GroupBy(p => p.memberId).Select(g => g.Order(p => p.PaymentDate).First())

Answer 2

Something like:

Members.Select(c => new 
{ 
  Member = m, 
  LastPayment = m.Payments.OrderByDescending(p => p.PaymentDate).FirstOrDefault()
})
.Select(x => new { x.Member.Name, x.LastPayment.PaymentDate, x.Payment.Amount })