How do I get the most recent entry from a table using LINQ?

Question

I have a table of prices, which has a column for Date Updated. Every time there is a price change, a new entry is made to the table.

In order to return the most up to date price, I am trying to order by date, and then use .Last() to get the newest entry:

 var upgrades = from d in vf.tl_phn_devices
                       where d.available == true
                       select new
                       {
                           DeviceName = d.tl_phn_manufacturer.manufacturer + " " + d.model,
                           Price = (from p in vf.tl_phn_prices 
                                    where p.deviceID == d.deviceID
                                    orderby p.dateUpdated ascending
                                    select p.price).Last(),
                           URL = d.url,
                           ImageURL = d.image

                       };

However, when I run the above I get an error saying that .Last() is unsupported. I have also tried .AsEnumberable().Last() but this didn't work either.

Elsewhere in the code I got round a similar issue by taking an extra step:

  var orders = (from o in vf.tl_phn_orders
                    where o.login == CurrentUserName
                    orderby o.date ascending
                    select new
                     {
                         Login = o.login,
                         Name = o.name,
                         Device = o.tl_phn_device.tl_phn_manufacturer.manufacturer + " " + o.tl_phn_device.model,
                         Price = o.tl_phn_price.price,
                         date = o.date

                     }).AsEnumerable();

        var order = orders.LastOrDefault();

But I want to do it all in one "hit" so I can return it in the first query.

score 7 · Accepted Answer · edited May 23 '17 at 12:03

7

Just do orderby descending and then Select First.

Last and LastOrDefault are not supported with Entity framework, since they can't get translated into underlying language (SQL for your case)

orderby p.dateUpdated descending
select p.price).First(),

You may also see: Supported and Unsupported LINQ Methods (LINQ to Entities)

edited May 23 '17 at 12:03

Community

1
1

answered Feb 04 '14 at 15:08

Habib

219,104
29
407
436

1

The linked page doesn't have horizontal scroll bar or is it just my browser – ElectricRouge Feb 04 '14 at 15:14
@ElectricRouge, its an MSDN page and it doesn't require horizontal scrolling on my screen – Habib Feb 04 '14 at 15:15
@ElectricRouge, just tried on second screen , and yes you have to select and scroll to right , strange. **MSDN man!!** – Habib Feb 04 '14 at 15:16

Sam Leach · Answer 2 · 2014-02-04T15:16:32.720

Use First() and change the order:

 var upgrades = from d in vf.tl_phn_devices
                where d.available
                select new
                {
                    DeviceName = d.tl_phn_manufacturer.manufacturer + " " + d.model,
                    Price = (from p in vf.tl_phn_prices 
                             where p.deviceID == d.deviceID
                             orderby p.dateUpdated descending
                             select p.price).First(),
                    URL = d.url,
                    ImageURL = d.image    
                };

The problem is that EntityFramework cannot generate the SQL for your query.

Your second query's use of .Last() works because you have loaded the entities into memory (with .AsEnumerable()) and are now using LINQ to Objects instead of LINQ to Entities.

You cannot do the same with the first query because of the nested queries.

How do I get the most recent entry from a table using LINQ?

2 Answers2