Linq query to get the last record

Question

I know this is asked many times and I've searched most of the solutions online, but nothing seems to make it for me. I have a table with this structure:

ID | ScheduleId | Filename | Description
 1 |     10     |          |  ....
 2 |     10     | test.txt |  .....

I want to get the last non-empty Filename by passing the ScheduleId(e.g. to get "test.txt" in this case).

I've tried many things and nothing seems to get me the Filename. Here is the last one:

var tempFileName = objContext.SchedulesAndFiles
                           .Where(x => x.ScheduleId == scheduleId)
                           .OrderByDescending(x => x.ScheduleId)
                           .Take(1).Select(x => x.Filename);

This doesn't work as well, although I understand why it doesn't:

var tempFileName = from e in objContext.SchedulesAndFiles
                   where e.ScheduleId == scheduleId 
                   orderby e.ScheduleId descending
                   select e.Filename;

Calling .Last() or .LastOrDefault() throws an exception(The query operator 'LastOrDefault' is not supported.)

Answer 1

if have to include that you want only non-empty filenames. You may also use ToList() to finalize the query, then FirstOrDefault() should work as expected, try

var tempFileName = objContext.SchedulesAndFiles
                             .Where(x 
                                 => x.ScheduleId == scheduleId 
                                 && x.Filename != null 
                                 && x.Filename != "")
                             .OrderByDescending(x => x.ScheduleId)
                             .Take(1)
                             .Select(x => x.Filename)
                             .ToList()
                             .FirstOrDefault();

Answer 2

You should sort your records based on the ID instead of ScheduleId and also filter the records that has the empty Filename:

objContext.SchedulesAndFiles
          .Where(x => x.ScheduleId == scheduleId && x.Filename != "")
          .OrderByDescending(x => x.ID)
          .First().Filename;