Question
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?
Accepted Answer
Try to group it by AssetId and take max from each group
var results =
from Asset in _context.Asset
join change in _context.Change on Asset.AssetId equals change.AssetId into potentialChange
group potentialChange by potentialCharge.AssetId into g
select
{
g.Key,
g.Max().Timestamp
}
Popular Answer
Use Group By as follows:
List<MyTable> data = new List<MyTable>()
{
new MyTable(){ChangeId = 1, AssetId = 1, Timestamp = 123},
new MyTable(){ChangeId = 2, AssetId = 2, Timestamp = 999},
new MyTable(){ChangeId = 3, AssetId = 1, Timestamp = 123},
new MyTable(){ChangeId = 5, AssetId = 3, Timestamp = 123},
new MyTable(){ChangeId = 5, AssetId = 2, Timestamp = 123},
};
var expectedData = data.OrderByDescending(d => d.Timestamp).GroupBy(d => d.AssetId).Select(g => new
{
AssetId = g.Key,
TimeStamp = g.First().Timestamp
}).ToList();
This will give your expected result.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow