I'm trying to convert a SQL query to linq, but I'm getting stuck on combining group by and max()

What I'm trying to do is find the last ID key column for each SerialNumber column within a table.

Here's the sample tables, truncated to just the columns I need to answer this question.

Devices Table
SerialNumber
12345
45678
67890

History Table
ID | SerialNumber
1  | 12345
2  | 45678
3  | 67890
4  | 12345
5  | 67890

My expected output would be:

ID | SerialNumber
2  | 45678
4  | 12345
5  | 67890
开发者_运维问答

Here's the SQL I'm trying to convert from:

SELECT max(ID) as ID, SerialNumber
FROM History
WHERE SerialNumber in
 (SELECT Distinct SerialNumber
 FROM Devices
 WHERE DeviceType = 0)
GROUP BY SerialNumber)

Here's the best working linq query I've got:

var query= from ListOfIDs in History
           where (from distinctSerialNubers in Devices
           where distinctSerialNubers.DeviceType == 0
           select distinctSerialNubers.SerialNumber).Contains(ListOfIDs.SerialNumber)
           select new {ListOfIDs.ID, ListOfIDs.SerialNumber};

Using .Max() will return the last ID in the History table. Trying to group ListOfIDs by ListOfIDs.SerialNumber will separate all ids by SerialNumber, but I can't seem to find a way to get into the groups to run .Max().

Edit: Thank you for the answers. This combination of the two answers works well for me.

var query= from historyEntry in History
where (from s in Devices where s.DeviceType==0 select s.SerialNumber).Contains(historyEntry.SerialNumber)
group historyEntry by historyEntry.SerialNumber into historyGroup
select historyGroup.Max(entry => entry.ID);

Thank you both, I've been trying to figure this one out all day.

---

Haven't compiled/tested it but this should be what you're looking for:

from historyEntry in db.History
join device in db.Devices on history.SerialNumber equals device.SerialNumber
where device.DeviceType == 0
group historyEntry by historyEntry.SerialNumber into historyGroup
select new { ID = historyGroup.Max(entry => entry.ID), SerialNumber = historyGroup.Key }

---

What about :

var snoQuery = (from s in Devices where s.DeviceType==0 select s.SerialNumber).Distinct();

var query (
   from h in History 
   where snoQuery.Contains(h.SerialNumber)
   group h by h.SerialNumber into results
   select new { Id = results.Max(r=>r.id) , SerialNumber = results.Key}
);