CODEWITHSUNDEEP
c#entity-frameworklinq
L Riley
L Riley

Reputation: 337

Linq Return List of Objects after max effective From Date

Hiiya,

So trying to work out a query that returns a list of objects based on its max effective from date before a selected datetime.

Principle Example

+----+--------------+------------+----------------+
| id | Employee_Num | Money_Owed | Effective_From |
+----+--------------+------------+----------------+
| 1  | 1            | 10.11      | 20/10/2017     |
+----+--------------+------------+----------------+
| 2  | 1            | 15.11      | 24/10/2017     |
+----+--------------+------------+----------------+
| 3  | 1            | 20.11      | 30/10/2017     |
+----+--------------+------------+----------------+
| 4  | 2            | 6.89       | 20/10/2017     |
+----+--------------+------------+----------------+
| 5  | 2            | 9.89       | 25/10/2017     |
+----+--------------+------------+----------------+
| 6  | 2            | 12.89      | 29/10/2017     |
+----+--------------+------------+----------------+

so say I want to return each employees record as of 21/10/17 I would expect the below to be returned as a list of objects (Entities)

+----+--------------+------------+----------------+
| id | Employee_Num | Money_Owed | Effective_From |
+----+--------------+------------+----------------+
| 1  | 1            | 10.11      | 20/10/2017     |
+----+--------------+------------+----------------+
| 4  | 2            | 6.89       | 20/10/2017     |
+----+--------------+------------+----------------+

Then as of 24/10/2017

+----+--------------+------------+----------------+
| id | Employee_Num | Money_Owed | Effective_From |
+----+--------------+------------+----------------+
| 2  | 1            | 15.11      | 24/10/2017     |
+----+--------------+------------+----------------+
| 4  | 2            | 6.89       | 20/10/2017     |
+----+--------------+------------+----------------+

Im guessing the Query should be something along these lines but cant work out what it should be.

 var qry = from t in db.Entity.Where(x => x.Effective_From <= as_of_date)
     .OrderBy(x => x.Employee_Num)
     select new Entity { *need rest of entity fields* ,effective_from = Max(e => e.Effective_From) };

Any help to finish off or point me in a different direction would be appreciated...

Upvotes: 2

Views: 544

Answers (1)

Steve Py
Steve Py

Reputation: 34793

I believe what you'd be looking to do would involve a GroupBy expression to group by employee number, ordered by Employee number and then effective from descending, then take the first available combination.

var qry = db.Entity.Where(x => x.Effective_From <= as_of_date)
   .OrderBy(x => x.Employee_Num)
   .ThenByDescending(x => X.Effective_From)
   .GroupBy(x => x.Employee_Num)
   .Select(g => g.FirstOrDefault())
   .ToList();

This is if you want the actual entities. I'd avoid doing something like "select new" for an entity as while this will contain the data of the relevant entities, they aren't the same reference as far as the context is concerned. .Select() would be used to retrieve an anonymous type suited for some logic or to populate a view model for a front end, or a DTO for an API result.

Upvotes: 3

Related Questions