Select TOP 1 for each FK in list using Entity Framework

c# entity-framework entity-framework-core

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?

1
1
9/19/2019 9:26:24 AM

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
}
1
9/19/2019 9:39:40 AM

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow