LINQ查询-按键获取最新记录

本文关键字：获取 最新 新记录 查询 LINQ | 更新日期: 2023-09-27 18:02:41

我有以下查询，它向我返回一个来宾列表和一个RSVP列表。然而，由于用户每个事件可以有多个RSVP，我只想得到最新的。RSVP表有一个提交日期列。

from guest in Guests
                join rsvp in RSVPs.Where(o => o.EventID == "1234")
                on guest.UserName equals rsvp.UserName into sr
                from x in sr.DefaultIfEmpty()
                select new
                {
                     guest,
                    x
                }

LINQ查询-按键获取最新记录

按日期排序，取1:

(from guest in Guests
            join rsvp in RSVPs.Where(o => o.EventID == "1234")
            on guest.UserName equals rsvp.UserName into sr
            from x in sr.DefaultIfEmpty()
            orderby x.RSVPDate desc
            select new
            {
                 guest,
                x
            })
.Take(1);

伪-

group x by username into g
select new {
  UserName = g.key // username
  FirstRsvp = g.OrderByDescending(item => item.RsvpDate).FirstOrDefault()
}

希望这是对对象的LINQ，因为这类东西可能不会在SQL提供程序中定义。如果它是SQL并且没有实现。AsEnumerable((将把查询拉到内存中，然后它将从此成为L2O。

from guest in Guests
                join rsvp in RSVPs.Where(o => o.EventID == "1234")
                on guest.UserName equals rsvp.UserName into sr
                from x in sr.DefaultIfEmpty()
                select new
                {
                     guest,
                    guest.RSVPs.Where(o => o.EventID =="1234")
                               .OrderByDesc(o=>o.SubmittedDate)
                               .FirstOrDefault()
                }

事实上，您也可以尝试这个，其想法是加载正确的RSVP并加载附加的来宾对象，前提是存在外键关系。

context.RSVPs.Include("Guest").Where(o => o.EventID =="1234").OrderByDesc(o=>o.SubmittedDate).FirstOrDefault()

否则，这可能是一个选项：

(from rsvp in context.RSVP
where rsvp.EventId == "1234"
join guest in context.Guests on rsvp.UserName equals guest.UserName 
select new {rsvp, guest}
orderby desc rsvp.SubmittedDate).FirstOrDefault()
相关文章：