如何使用LINQ获取最新号码

Question

这里列出了数据库表中客户的付款历史

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来获取数字。

Answer 1

如果我们假设忽略零，并且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

Answer 2

试试这个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();

• 试图避免负面查询
• awaitable函数

Answer 3

您写道：

PaymentId列自动递增

我的建议是根据CustomerId的公共值对每个用户的PaymentHistories进行分组。

然后，对于每个组，保留具有PaymentId的最高值的PaymentHistory。毕竟：PaymentId是自动递增的，所以客户X的PaymentHistories组中的PaymentHistory是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，请使用以下结果选择器：

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