Reputation: 43523
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?
Upvotes: 2
Views: 2590
Reputation: 2935
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.
Upvotes: 1