I have a large table where I'm trying to select the `top 1` row for each FK in a list.

My table is laid out as:

```
ChangeId | AssetId | Timestamp
1          1         123
2          2         999
3          1         3478
4          3         344
5          2         1092

```

Where `ChangeId` is my `PK`, `AssetId` is my `FK` and `Timestamp` is the value I'm trying to select.

If I try the following:

```
var results =

from Asset in _context.Asset
join change in _context.Change on Asset.AssetId equals change.AssetId into potentialChange
from actualChange in potentialChange.OrderByDescending(y => y.ChangeId).Take(1)
select 
{
   AssetId,
   Timestamp
}

```

Where my expected result would be:

```
[
  {
    AssetId: 1,
    Timestamp: 3478
  },
  {
    AssetId: 2,
    Timestamp: 1092
  },
  {
    AssetId: 3,
    Timestamp: 344
  }
]

```

This query flags up the `The LINQ expression could not be translated and will be evaluated locally.` which is not suitable for a production rollout.

Running a `foreach` loop and selecting each item out 1 by 1 works, not it's not a performant solution.

Is there a suitable way to achieve the above?