How to mount SELECT in lambda C#?

Question

I recently asked for help to assemble a SELECT to bring the price of products

How to mount SELECT

They gave me this solution that worked perfectly:

SELECT P.PROCODIGO, P.PRONOME, H.HISPRECO
FROM PRODUTO P
INNER JOIN HISTORICO H ON P.PROCODIGO = H.PROCODIGO
WHERE H.HISDATA = (SELECT MAX(HISDATA) FROM HISTORICO WHERE PROCODIGO = P.PROCODIGO)

But now I need to mount that same SELECT in lambda with C#, because I started using Entity. Can someone help me?

Thank you!

Answer 1

Would look like this:

var produtos = db.Historicos
                 .Include(h => h.Produto)
                 .OrderByDescending(h => h.HistoricoData)
                 .GroupBy(h => h.ProdutoId)
                 .Select(group => group.FirstOrDefault())
                 .ToList();

In the Entity Framework one should not think like in SQL. The way of doing things is different. The generated SQL will possibly not be equal, but the result will be.