如何将SQL查询转换为LINQ(以获取每个bookingStatus的最后记录)

Question

需要帮助将下面的查询翻译成LINQ

;With BookingWithLastStatus 
as
(
Select *, Rnk = ROW_NUMBER() over (partition by BookingId order by Id desc)
from BookingStatus
)

Select * 
from BookingWithLastStatus
where Rnk=1 AND StatusId = 3

我已经做了下面的LINQ，但它没有得到正确的记录。

var BookStatus = from p in _context.Set<BookingStatus>()
where p.StatusId == 3
group p by p.BookingId into opt
select new {
BookingId = opt.Key,
Id = opt.Max(x => x.Id)
};

SQL查询只得到1条记录，这是正确的，我的LINQ得到多条记录。

更新:

我是这样做的:

首先获取所有BookingStatus

var GetAllBookStatus = await _context.Set<BookingStatus>() 
.ToListAsync();

然后根据我需要的SQL查询执行过滤。

var FilteredBookStatus = GetAllBookStatus
.OrderByDescending( x => x.Id )
.GroupBy(person => person.BookingId)   
.Select( group => new { Group = group, Count = group.Count() } )
.SelectMany( groupWithCount =>
groupWithCount.Group.Select( b => b)
.Zip(
Enumerable.Range( 1, groupWithCount.Count ),
( b, i ) => new { 
b.Id,
b.BookingId,
b.BookingMWABId, 
b.BookStatus,
b.CreatedBy,
b.CreatedDate,
b.Destination,
b.InternalStatus,
b.LineNum,
b.ModifiedBy,
b.ModifiedDate,
b.Module,
b.ReasonCode,
b.ReceivedBy,
b.RefNo,
b.StatusId,
b.TimeStamp,
RowNumber = i }
)
)
.Where(a => a.StatusId == 3 && a.RowNumber == 1)
.ToList();

但我对获得所有记录没有信心，因为它会随着时间的推移而增长。有什么我可以改变我的代码吗?

Answer 1

使用EF core 6。X，您可以执行以下操作。这不是SQL中的最佳情况，但应该可以工作:

var BookStatus = 
from p in _context.Set<BookingStatus>()
group p by p.BookingId into g
select g.OrderByDescending(x => x.Id).First();
BookStatus = BookStatus.Where(p => p.StatusId == 3);

或其他变体

var BookStatus = _context.Set<BookingStatus>().AsQueryable();
BookStatus = 
from d in BookStatus.Select(d => new { d.BookingId }).Distinct()
from p in BookStatus
.Where(p => p.BookingId == d.BookingId)
.OrderByDescending(p => p.Id)
.Take(1)
select p;
BookStatus = BookStatus.Where(p => p.StatusId == 3);