這里列出了 db 表中客戶的付款歷史記錄
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
PaymentId 列是自動遞增的。如何獲得該客戶的最后一筆付款，即 $30.00 的數字？我的專案是 Asp.net API，所以我需要使用 LINQ 來獲取數字。
uj5u.com熱心網友回復：
如果我們假設我們忽略了零，并且PayId是單調遞增的，那么大概：
作為 LINQ：
var val = ctx.SomeTable
.Where(x => x.CustomerId == customerId && x.FeePaid != 0)
.OrderByDescending(x => x.PayId)
.Select(x => x.FeePaid)
.First();
或作為 SQL：
select top 1 FeePaid
from SomeTable
where CustomerId = @customerId
and FeePaid <> 0
order by PayId desc
uj5u.com熱心網友回復：
試試這個 linq 運算式：
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();
- 盡量避免否定查詢
- 等待函式
uj5u.com熱心網友回復：
你寫了：
PaymentId 列是自動遞增的
我的建議是對PaymentHistories每個用戶進行分組，因此通過 CustomerId 的共同值。
然后對于每個組，保留PaymentHistory具有最高值的PaymentId。畢竟：PaymentId是自動遞增的，所以PaymentHistory在PaymentHistories客戶X的組中是PaymentId最高的
為此，我使用了Queryable.GroupBy的多載，它有一個引數 resultSelector，所以我可以精確地指定我想要的結果。
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(),
}
如果您不需要 FeePaid，也不需要 PaymentId，請使用以下 resultSelector：
(customerId, paymentHistoriesWithThisCustomerId) => new
{
CustomerId = customerId,
LastPayment = paymentHistoriesWithThisCustomerId
.OrderByDescending(paymentHistory => paymentHistory.PaymentId)
.Select(paymentHistory => new
{
PaymentId = paymentHistory.PaymentId,
FeePaid = paymentHistory.FeePaid,
})
.FirstOrDefault();
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/327182.html