Linq with subquery with Let

Question

What I'm trying to get from this Linq query is a list of all Adverts where the most recent associated Log with a LogType.IsStatus == true has a LogType.Name of either Confirmed or Renewed. To be clear, an Advert has many Logs and each Log has one LogType. So far I have the following, but it's giving me an error System.NotSupportedException on the LastOrDefault.

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

LastOrDefault() is not supported in LINQ to Entities (see here).

You can work around this by changing the order by clause in your let subquery to an order by descending and then use FirstOrDefault() instead.

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();