How to get the latest number using LINQ-CodePudding

Here lists a payment history of a customer in a db table

CustomerId  PayId   FeePaid
xx-yy-zz    37      0
xx-yy-zz    32      0
xx-yy-zz    31      30.00
xx-yy-zz    28      0
xx-yy-zz    26      0
xx-yy-zz    18      35.99
xx-yy-zz    17      0
xx-yy-zz    16      0
xx-yy-zz    9       12.00
xx-yy-zz    6       0

The PaymentId column is auto incremented. How to get the last payment of this customer, i.e., the number $30.00? My project is Asp.net API, so I need use LINQ to get the number.

CodePudding user response：

If we assume that we're ignoring zeros, and that PayId is monotonically incrementing, then presumably:

as LINQ:

var val = ctx.SomeTable
    .Where(x => x.CustomerId == customerId && x.FeePaid != 0)
    .OrderByDescending(x => x.PayId)
    .Select(x => x.FeePaid)
    .First();

or as SQL:

select top 1 FeePaid
from SomeTable
where CustomerId = @customerId
and FeePaid <> 0
order by PayId desc

CodePudding user response：

Try this linq expression:

var result = await (from d in _ctx.MyTable
                    where d.CustomerId="xx-yy-zz" && d.FreePaid > 0
                    orderby d.PayId descending
                    select d.FreePaid).FirstOrDefaultAsync();

tried to avoid negative queries
awaitable function

CodePudding user response：

You wrote:

The PaymentId column is auto incremented

My advice would be to group the PaymentHistories per user, so by common value of CustomerId.

Then for each group, keep the PaymentHistory that has the highest value of PaymentId. After all: PaymentId is auto-increments, so the PaymentHistory in the group of PaymentHistories of Customer X is the one with the highest PaymentId

For this I used the overload of Queryable.GroupBy that has a parameter resultSelector, so I can precisely specify what I want in my result.

IQueryable<PaymentHistory> paymentHistories = ...
var lastCustomerPayments = paymentHistories.GroupBy(

    // parameter keySelector: make groups with same CustomerId
    paymentHistory => paymentHistory.CustomerId,

    // parameter resultSelector: for every CustomerId and all PaymentHistories
    // that have this value for CustomerId, make one new:
    (customerId, paymentHistoriesWithThisCustomerId) => new
    {
        CustomerId = customerId,

        // get the feePaid of the PaymentHistory with the largest PaymentId
        FeePaid = paymentHistoriesWithThisCustomerId
            .OrderByDescending(paymentHistory => paymentHistory.PaymentId)
            .Select(paymentHistory => paymentHistory.FeePaid)
            .FirstOrDefault(),
    }

If you don't want FeePaid, but also the PaymentId, use the following resultSelector:

(customerId, paymentHistoriesWithThisCustomerId) => new
{
    CustomerId = customerId,

    LastPayment = paymentHistoriesWithThisCustomerId
        .OrderByDescending(paymentHistory => paymentHistory.PaymentId)
        .Select(paymentHistory => new
        {
            PaymentId = paymentHistory.PaymentId,
            FeePaid = paymentHistory.FeePaid,
        })
        .FirstOrDefault();
    }