Convert SQL to LINQ

Question

I have the following table

CREATE TABLE `ultimaposicaorastreadores` (
`Id` CHAR(36) COLLATE utf8_bin NOT NULL DEFAULT '',
`Serial` BIGINT(20) NOT NULL,
`DataGps` DATETIME NOT NULL,
PRIMARY KEY USING BTREE (`Id`, `DataGps`),
KEY `serial_idx` USING BTREE (`Serial`),
KEY `datagps_idx` USING BTREE (`DataGps`),
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

I need to do this query

SELECT 
lp.Id, 
lp.Serial,
lp.DataGps
FROM   ultimaposicaorastreadores lp
JOIN   (SELECT  Serial,
            MAX(DataGps) AS maxi
    FROM    ultimaposicaorastreadores
    GROUP BY Serial) t1
ON      lp.Serial = t1.Serial
AND     lp.DataGps = t1.maxi;

How do I convert this query to LINQ and get the registry ID with the largest Datagps?

Answer 1

Mounting the sql query to Line would be translated into the form below.

 Contexto.Set<ultimaposicaorastreadores>()
            .Where(x => x.DataGps == Contexto.Set<ultimaposicaorastreadores>().Where(y => y.Serial == x.Serial).Max(y => y.DataGps))
            .ToList();