Linq Query: Distinct Year + Dynamic Name

Question

I'm trying to take a DateTime field and create a List of DateObject, where DateObject has 2 properties (Year and Range). I want to identify the year of the DateTime field then... If it's even, I want to retain that year for Year. If it's odd, I want to change it to year - 1 for Year.

Then, based off that, I want to identify a string for Range that will look like "[EvenYear] to [EvenYear + 1]".

My data looks like this:

TableData

• ID (i.e., 1)
• Date (i.e., 12/7/2017)

If the records look like:

• ID: 1, Date: 12/7/2017 (2017 should change to 2016)
• ID: 2, Date: 1/1/2016 (keeps 2016)
• ID: 3, Date: 1/2/2016 (keeps 2016)
• ID: 4, Date: 10/5/2015 (2015 would be 2014)

I want the output List of DateObjects to look like this (distinct based off the Year):

• Year: 2014, Range: 2014 to 2015
• Year: 2016, Range: 2016 to 2017

I am so frustrated with figuring this out. I've tried a lot of code.

This is the code I left off with that kept failing:

var sessionYears = db.tblDates.GroupBy(x => (x.Date.Year % 2 == 0 ? x.Date.Year : x.Date.Year - 1)).ToList();

return sessionYears.Select(x => new List<DateObject>                {
StartYear= x.Key,
Range = x.Select(y => y.Date.Year + " to " + (y.Date.Year + 1))
}

I've tried messing around with distinct and groupby, but I am clearly missing some steps, etc. I can't find anything online. Any help would be greatly appreciated!!! Thanks in advance.

Answer 1

You could create the list of DateRangeObject first and after that get the distinct values. This will make your query much simpler.

return db.tblDates
    .Select(x => new DateRangeObject {
        StartYear = x.Date.Year % 2 == 0 ? x.Date.Year : x.Date.Year - 1,
        EndYear = (x.Date.Year % 2 == 0 ? x.Date.Year : x.Date.Year - 1) + " - " + ((x.Date.Year % 2 == 0 ? x.Date.Year : x.Date.Year - 1) + 1);
    })
    .GroupBy(x => x.StartYear)
    .Select(x => x.First())
    .ToList();