I am trying to get the most recent record by another field 'group' using .net core 3. In .net core 2 this was possible using GroupBy but this no longer works and causes an exception - and now i am looking for an alternative.
Entity looks as follows:
Client --> Messages
Example: The data is as follows in a Messages table:
|Date |Client |Message |
|01-01-2019|A |Hello |
|05-10-2019|A |Blah |
|06-11-2019|A |Newest |*Most recent by Date for Group A
|03-01-2019|B |Oldest |
|10-05-2019|B |Test |
|20-10-2019|B |Also New|*Most recent by Date for Group B
I would like the response to have only:
|Date |Client |Message |
|06-11-2019|A |Newest |*Most recent by Date for Group A
|20-10-2019|B |Also New|*Most recent by Date for Group B
Previously i could do the following:
PersistenceManager.Context.Client.Include(c => c.Client).Where(x.Date >= date).GroupBy(x => x.ClientId).Select(g => g.OrderByDescending(y => y.Date).FirstOrDefault());
I have read a interesting blog here that suggest doing this by starting with the Client entity however i have a lot of Client records (hundreds) and if there is no record it creates a null. This also does not seem like the right solution under the circumstances.
Context.Client.Select(g => g.Messages.OrderBy(p => p.Name).FirstOrDefault())
.ToList();
Anyone solved for this type of problem in .net core 3.
Note: There are solutions i am aware of that use .AsEnumerable(), but this converts the query to a client side query where you can then filter/group/etc. The solution i am looking for is where the query is run on SQL Server.
This is the same issue as referenced here but for earlier EF versions.
Note: Date Column is DD-MM-YYYY.
