Last and LastOrDefault not supported

Question

I am trying to get the first and last values in a list. The query operator First() is supported but Last() and LastOrDefault() give an error. Am I using the Last() operator incorrectly?

var purchaseBills = db.PurchaseBills.OrderBy(p => p.BillID);

if (purchaseBills.Count() >0)
{
    var firstBill = purchaseBills.First(); // This is supported

    // Attempt 1                    
    var lastBill = purchaseBills.Last(); // Not supported
    
    // Attempt 2
    var lastBill = purchaseBills.LastOrDefault(); // Not supported

    //Attempt 3
    var lastBill = purchaseBills.Reverse().First(); // Not supported
    
    textBoxPurchaseBillFrom.Text = firstBill.BillNo.ToString();
    textBoxPurchaseBillTo.Text = lastBill.BillNo.ToString();
}

Update:

--Errors--

Attempt 1: The query operator 'Last' is not supported.

Attempt 2: The query operator 'LastOrDefault' is not supported.

Attempt 3: The query operator 'Reverse' is not supported.

Possible duplicate of [LINQ To Entities does not recognize the method Last. Really?](http://stackoverflow.com/questions/7293639/linq-to-entities-does-not-recognize-the-method-last-really). This question was asked a month before the other question, but that question has better answers. — Tot Zam, Mar 02 '17 at 16:46

score 54 · Accepted Answer · answered Aug 31 '11 at 07:07

54

Instead of putting it into an own list by calling ToList() or ToArray() i would prefer to use AsEnumerable().
Additionally like the others you should try OrderByDescending()
Instead of Count() i would use Any().

answered Aug 31 '11 at 07:07

Oliver

43,366
8
94
151

3

I found ordering by descending, then using FirstOrDefault() will work. – Yogurt The Wise Jan 19 '17 at 14:50

score 28 · Answer 2 · answered Aug 31 '11 at 06:54

28

either you switch your OrderBy to

.OrderByDescending(p => p.BillID)

(and use first) or you do something like

purchaseBills.ToArray().Last()

if this is not to expensive.

answered Aug 31 '11 at 06:54

Random Dev

51,810
9
92
119

score 21 · Answer 3 · answered Aug 31 '11 at 06:58

21

Last is not supported by the back-end DB. You should try other techniques:

Run your query using OrderByDescending so your requested item comes first.
Code your LINQ query as usual, but enforce Linq2Sql to render it to a CLR collection and then you'll have free access to everything locally, including Last. Example:
```
var bills = purchaseBills.ToList();
var last = bills.Last();
```

answered Aug 31 '11 at 06:58

While true that in most SQL dialects you `ORER BY ... DESC LIMIT 1`, Linq is just a SQL translation layer. Conceivably this translation could've been implemented but wasn't – CervEd Oct 27 '22 at 09:40

George Duckett · Answer 4 · 2011-08-31T06:59:29.927

11

The problem is that there's no easy translation into SQL for Last or Reverse, so either convert it to something in memory (ToList, ToArray) if there aren't going to be too many records, or you could run the query a 2nd time, with OrderByDescending instead of OrderBy and use First.

edited Aug 31 '11 at 06:59

answered Aug 31 '11 at 06:54

George Duckett

31,770
9
95
162

"The problem is that there's no easy translation into SQL for Last or Reverse", there is. It's `ORDER BY ... DESC LIMIT 1`. The problem is `first` may be called on unordered queries, where the database offers no guarantee of order but ostensibly retrieves the first record in the clustered index. There's no `last` in this sense, because in SQL this can only be done with an explicit order. Imho, the LINQ implementation here is imprecise and an annoying source of runtime errors – CervEd Oct 27 '22 at 09:45

score 10 · Answer 5 · edited Mar 09 '16 at 04:15

10

I try not to use LastOrDefault() because sometime it doesn't work or support. I'm sorting id by desc and then grab the first records.

.OrderByDescending(o=>o.id)
.FirstOrDefault(s => s.Name == Name)

edited Mar 09 '16 at 04:15

Remi Guan

21,506
17
64
87

answered Mar 09 '16 at 03:59

Lap Huynh

101
1
3

Same issue, i switched linq to use orderby descending, then used FirstOrDefault() instead of LastorDefault. – Yogurt The Wise Jan 19 '17 at 14:49

score 4 · Answer 6 · answered Aug 31 '11 at 06:56

4

It has something to do with the fact that the Last operator is trying to be sent to the SQL server which has no corresponding command. Once solution is to put a ToArray() or Tolist() at the end of your db call which makes that one line an explicit call to get the data (instead of lazing loading on each of the other lines).

answered Aug 31 '11 at 06:56

Erik Philips

53,428
11
128
150

Seems to be the first post to actually answer the question, which was "*Am I using the Last() operator incorrectly*" – mins May 13 '20 at 14:28

score 1 · Answer 7 · answered Jul 21 '17 at 11:46

1

Yet another way get last element without orderbydescending and load all entities:

var lastBill = purchaseBills
    .Where(f => f.BillID == purchaseBills.Max(f2 => f2.BillID))
    .FirstOrDefault();

answered Jul 21 '17 at 11:46

Stas Boyarincev

3,690
23
23

score 1 · Answer 8 · edited Oct 29 '21 at 17:45

You can convert your IEnumerable into a List using the ToList() method, which will ensure that all your attempts are successful, as shown:

var purchaseBills = db.PurchaseBills.OrderBy(p => p.BillID);

if (purchaseBills.Any())
{
  var firstBill = purchaseBills.First(); // This is supported

// Attempt 1                    
  var lastBill = purchaseBills.ToList().Last();

// Attempt 2
  var lastBill = purchaseBills.ToList().LastOrDefault(); 

//NoLogic
  var lastBill = purchaseBills.ToList().Reverse().First();

  textBoxPurchaseBillFrom.Text = firstBill.BillNo.ToString();
  textBoxPurchaseBillTo.Text = lastBill.BillNo.ToString();
}

Last and LastOrDefault not supported

8 Answers8

Linked