Using GroupBy in LINQ to get last inserted rows

Question

I have a simple table that I would like to get the last inserted rows from using GroupBy.

I have the following code that works without grouping but would like to make a new function with grouping

 public Session FindLastFinished(IdentityUser user)
    {
        return _dbContext.Sessions.OrderByDescending(o => o.CreatedAt).FirstOrDefault(s => s.User.Equals(user) && s.Progress.Equals(Status.Finished.ToString()));
    }

I need to add a grouping for the column ScenarioId so that it will return the last inserted row for each ScenarioId but am having trouble adding the GroupBy syntax.

public List<Session> FindLastFinishedByScenarios(IdentityUser user)

Don't you have `Scenario` entity with collection navigation property similar to `public ICollection Sessions { get; set; }` ? — Ivan Stoev, Jun 18 '20 at 11:29

score 0 · Answer 1 · answered Jun 18 '20 at 09:22

0

This should do:

_dbContext.Sessions.ToList()
.GroupBy(x => x.ScenarioId)
.Select(groupOrderedByCreatedAt =>
       {
            var orderedGroup = groupOrderedByCreatedAt.OrderByDescending(x => x.CreatedAt);
                //Work with the orderedGroup
        });

Works like this:

You group them by scenarioId
Select on it takes each group
You order each group by createdAt

Then you work with the ordered group

answered Jun 18 '20 at 09:22

Liquid Core

1
6
27
52

Severity Code Description Project File Line Suppression State Error CS0411 The type arguments for method 'Enumerable.Select(IEnumerable, Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly. It doesn't like the "select" part of the statement and won't compile. – Adam Jun 18 '20 at 10:57

score 0 · Accepted Answer · answered Jun 18 '20 at 09:22

0

_dbContext.Sessions.GroupBy(s => s.ScenarioId)
.ToDictionary(e => e.Key, e => e.OrderByDescending(o => o.CreatedAt).First())

In that case you will have a dictionary that have the scenario id as a key and the last inserted item as value.

answered Jun 18 '20 at 09:22

Arcord

1,724
1
11
16

1

I get the following error : Client side GroupBy is not supported. – Adam Jun 18 '20 at 10:44
Are you using EF Core ? Which version ? – Arcord Jun 18 '20 at 11:14
Yes EF Core, Net Core 3.1 – Adam Jun 18 '20 at 11:20
It seems is not always usable anymore. You can use a ".AsEnumerable()" after the "Sessions" but it means all records will be fetch from the database before applying the group by which is not great (but it seems in the past that's how EF dealt with group by) : https://stackoverflow.com/questions/58138556/client-side-groupby-is-not-supported. – Arcord Jun 18 '20 at 11:47
So my answer will not help you (unless you use the .AsEnumerable() or a .ToList(), you will get the result you want but in an inefficient way). – Arcord Jun 18 '20 at 11:49
Efficiency is not a big deal here....this is a proof of concept application with limited users and probably only a few thousand records in the table by the end of the test. – Adam Jun 18 '20 at 11:50
It should be OK then :-) – Arcord Jun 18 '20 at 12:05

score 0 · Answer 3 · answered Jun 18 '20 at 12:01

I have used this to solve it for now since efficiency isn't the biggest issue.

_dbContext.Sessions.ToList().GroupBy(s => s.ScenarioId).ToList().
                Select(e => e.OrderByDescending(o => o.CreatedAt).First())
.Where(w => w.User.Equals(user) && w.Progress.Equals(Status.Finished.ToString())).ToList();

If anyone has a better idea I am all ears!

Using GroupBy in LINQ to get last inserted rows

3 Answers3