Select the Most Recent Entry of Each Distinct Value in Linq

Question

Given a list of values with an unknown number of distinct string values, how do I get the most recent value of Each?

I am given a list of objects with the following three properties: Balance, BalanceType, and CreatedDate. Going in, I know there are a number of distinct values BalanceType can be set to, but I cannot be sure how many distinct values there are. For example, this is a valid input:

[{
 "BalanceType":"Cash",
 "Balance":350.03,
 "CreatedDate":10-20-16
},
{
 "BalanceType":"Cash",
 "Balance":250.01,
 "CreatedDate":10-20-15
},
{
 "BalanceType":"Cash",
 "Balance":450.21,
 "CreatedDate":10-20-14
},
{
 "BalanceType":"Securiites",
 "Balance":350.03,
 "CreatedDate":10-20-16
}]

As is the following:

[{
 "BalanceType":"Cash",
 "Balance":350.03,
 "CreatedDate":10-20-16
},
{
 "BalanceType":"Credit",
 "Balance":250.01,
 "CreatedDate":10-20-15
},
{
 "BalanceType":"LoanAmount",
 "Balance":450.21,
 "CreatedDate":10-20-14
},
{
 "BalanceType":"Securiites",
 "Balance":350.03,
 "CreatedDate":10-20-16
}]

I have already tried using the Max function to do this, but I discovered it only gives the maximum indicated value, not the object. What am I missing?

This Question is related, but in mysql so it isn't usable for me.

Sounds like you need to `GroupBy()`, then `OrderBy()` the date... — maccettura, Oct 10 '17 at 20:58
Also, I would edit your question to include actual code. LINQ needs some objects to query against... — maccettura, Oct 10 '17 at 21:00

score 2 · Accepted Answer · answered Oct 10 '17 at 21:10

It is helpful if you post data in C# format so it can be used directly. Translating your data, here are queries for your answer:

var src1 = new[] {
    new {
        BalanceType =  "Cash",
        Balance =  350.03,
        CreatedDate = new DateTime(2016, 10, 20)
    },
    new {
        BalanceType =  "Cash",
        Balance =  250.01,
        CreatedDate =  new DateTime(2015, 10, 20)
    },
    new {
        BalanceType =  "Cash",
        Balance =  450.21,
        CreatedDate =  new DateTime(2014, 10, 20)
    },
    new {
        BalanceType =  "Securiites",
        Balance =  350.03,
        CreatedDate =  new DateTime(2016, 10, 20)
    }
};
var src2 = new[] {
    new {
        BalanceType = "Cash",
        Balance = 350.03,
        CreatedDate = new DateTime(2016, 10, 20)
    },
    new {
        BalanceType = "Credit",
        Balance = 250.01,
        CreatedDate = new DateTime(2015, 10, 20)
    },
    new {
        BalanceType = "LoanAmount",
        Balance = 450.21,
        CreatedDate = new DateTime(2014, 10, 20)
    },
    new {
        BalanceType = "Securiites",
        Balance = 350.03,
        CreatedDate = new DateTime(2016, 10, 20)
    }
};


var ans1 = from r in src1
           group r by r.BalanceType into rg
           let latest = rg.Max(r => r.CreatedDate)
           select new { BalanceType = rg.Key, Balance = rg.Where(r => r.CreatedDate == latest).FirstOrDefault().Balance, CreatedDate = latest };

var ans2 = from r in src2
           group r by r.BalanceType into rg
           let latest = rg.Max(r => r.CreatedDate)
           select new { BalanceType = rg.Key, Balance = rg.Where(r => r.CreatedDate == latest).FirstOrDefault().Balance, CreatedDate = latest };

I assumed that if there were more than one latest dated BalanceType, it didn't matter which was chosen, so I used the first. If your DateTimes actually had times you could possibly replace FirstOrDefault() with Single() and crash your program if your assumption is wrong.

That's accurate, and probably why I was having so much trouble with it. Thank you! — Adam Wells, Oct 11 '17 at 14:30

Select the Most Recent Entry of Each Distinct Value in Linq

1 Answers1