Trying to get max date from table using Linq2Sql

Question

The following linq2sql code is causing me much headache and I was hoping someone could help

 DateTime _maxRecordedDate = (from snapshot in _ctx.Usage_Snapshots
                                         where snapshot.server_id == server_id
                                         orderby snapshot.reported_on descending
                                         select snapshot.reported_on).First().Value;

This code works in LinqPad and compiles fine but when the project runs there is a 'Specified Method is Unsupported".

If I don't use Value or cast it I get the following error:

**

Cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'. An explicit conversion exists (are you missing a cast?)

**

Answer 1

Does it not want First().Value? Maybe just First().

I'm looking at this.

Answer 2

DateTime? _maxRecordedDate = _ctx.Usage_Snapshots.Where(s => s.server_id == server_id).Max(d => d.reported_on);

Answer 3

Robert provided a better way to do it, but the problem with your code is that you are calling .Value and that will running in the context of SQL Server, so there is no .Value operation that can be translated to SQL. You'd have to call .ToList().First().Value, or assign to DateTime?. Since you can't call .First().ToList() ... since First() returns a single DateTime? then you're better off just assigning to DateTime?, because you don't want to return the entire list from SQL.

Answer 4

After much searching I was able to find that the problem stems from using ADO.NET Data Services. Apparently they use a limited subset of Linq and it's currently not possible to use methods like Max, First, etc. Bummme