使用 Let 进行子查询的 Linq

Question

我试图从这个 Linq 查询中获取的是所有广告的列表，其中最近关联的具有 LogType.IsStatus == true 的日志具有 LogType.Name已确认或已更新。需要明确的是，一个 Advert 有很多 Log，每个 Log 有一个 LogType。到目前为止，我有以下内容，但它在 LastOrDefault 上给了我一个错误 System.NotSupportedException。

var adverts = (from a in database.Adverts
                      let lastLog = (from l in a.Logs
                                     where l.LogType.IsStatus == true
                                     orderby l.Created_at
                                     select l).LastOrDefault()
                      where (lastLog != null)
                            &&
                            (lastLog.LogType.Name == "Confirmed" || lastLog.LogType.Name == "Renewed")
                            orderby a.Created_at descending
                      select a).ToList();

Answer 1

LINQ to Entities 不支持 LastOrDefault()（请参阅

您可以通过将 let 子查询中的 order by 子句更改为 order by descending 来解决此问题，然后请改用 FirstOrDefault() 。

var adverts = (from a in database.Adverts
               let lastLog = (from l in a.Logs
                              where l.LogType.IsStatus == true
                              orderby l.Created_at descending
                              select l).FirstOrDefault()
               where (lastLog != null)
                     &&
                     (lastLog.LogType.Name == "Confirmed" || lastLog.LogType.Name == "Renewed")
                      orderby a.Created_at descending
               select a).ToList();

Answer 2

试试这个

var lookingType = new string[]{"Confirmed", "Renewed"};

var result = database.Adverts
  .Where(entry 
    => entry.Logs.Count() > 0 && entry.Logs.Any(log => log.LogType.IsStatus))
  .Select(entry 
    => new { data = entry, 
       lastLog = entry.Logs.OrderByDescending(log => log.Created_at).First() 
    })
  .Where(entry => lookingType.Contains(entry.lastLog.LogType.Name))
  .Select(entry => entry.data).ToList();