Question 88250 in ASP.NET Question posted in
The official documentation can be found here.

How to get the latest number using LINQ – Asp.net

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.

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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();
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search