Entity Framework querying with max

Someone asks me to convert the T-SQL statement into LINQ to EF query:

SELECT * FROM  CopperPrices  
where ID in 
  (select max(ID) as ID from copperprices group by market, pname)

I use common LINQ-TO-OBJECT ideas and give the following answer:

class CopperPrice
{
    public int ID { get; set; }
    public string Market { get; set; }
    public string PName { get; set; }
}

var result = from p in copperPrices
      group p by new { Market = p.Market, PName = p.PName } into g
      select g.OrderByDescending(p => p.ID).First();

But it's not working in EF because of the following exception:

The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead

Can the above T-SQL statement be converted to one LINQ query statement?

Answers


Here you go:

var result1 = copperPrices.GroupBy(g => new { g.Market, g.PName }).Select
     (
         x => x.ToList().OrderByDescending(z => z.ID).FirstOrDefault()
     );

The important part here is the .Select(x => x.ToList()). The ToList returns the values of an IGrouping, each of which is what you ultimately want to order and select the max of.


Need Your Help

Dynamic List<T> type

c# .net dynamic

Is it possible to create a new List&lt;T&gt; where the T is dynamically set at runtime?

R: parallelization with foreach

r parallel-processing domc doparallel

I am new to R. I've written this very simple script to highlight my problem. If I run this regular for loop testdata is updated on each iteration just as I want it.