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.)

In your first example you `OrderByDescending` on `ScheduleId`. Don't you mean `ID`? Also, where do you filter out empty `FileName` records? — Daniel Kelley, Jul 01 '14 at 09:23
You just have to use `tempFileName.First` since you're already using `descending`. — Tim Schmelter, Jul 01 '14 at 09:23
First() should be supported and you can use this with OrderByDescending — Davio, Jul 01 '14 at 09:26
You are ordering by `ScheduleId` but your sample contains two records with the same `ScheduleId`. So obviously you should include another column you want to order by, for example the ID: `... orderby e.ScheduleId descending, ID descending` — Tim Schmelter, Jul 01 '14 at 09:41
Yes, you are right, I've already accepted Selman22's answer, as he suggested that. It seems to get me exactly the result that I expect — Apostrofix, Jul 01 '14 at 09:46

esskar · Accepted Answer · 2014-07-01T09:30:56.957

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

score 9 · Answer 2 · answered Jul 01 '14 at 09:23

9

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;

answered Jul 01 '14 at 09:23

Selman Genç

100,147
13
119
184

2

this will throw if nothing is found – esskar Jul 01 '14 at 10:14

score 3 · Answer 3 · answered Jul 01 '14 at 09:31

One option is to call ToList() or AsEnumerable() before trying to use LastOrDefault().

var tempFileName = objContext.SchedulesAndFiles
                   .Where(x => x.ScheduleId == scheduleId 
                            && x.Filename != null && x.Filename != '')
                   .ToList().LastOrDefault();
if(tempFileName != null)
{
    // Do something
}

score 0 · Answer 4 · answered Jul 01 '14 at 09:25

You can try this query. I think you have to issue the last or default before selecting the file name

 var tempFileName = objContext.SchedulesAndFiles
                               .Where(x => x.ScheduleId == scheduleId && ! string.IsNullOrEmpty(e.FileName))
                               .FirstOrDefault().Select(x => x.Filename);

score 0 · Answer 5 · answered Jul 01 '14 at 09:32

The final attempt:

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

For every item with this scheduleId, this gets the all of the items which have a non-empty fileName, sorted descending by ID (assuming that higher IDs are inserted after lower IDs), getting the First() (should be supported) and getting its FileName.

Note that you could run into a NullPointerException on First() if there is no satisfying fileName.

Also, you may need to normalize/trim to not find spaces/tabs and such things.

Linq query to get the last record

5 Answers5