Linq Query to get last entry of every year in the last x years

Question

I have a table in an MSSQL table which contains usage data. For a report I need to load only the last entry of every year in the last 10 years.

Example

ID |    Hits    | GeneratedAt
 1 |     12     | 20171231 
 2 |     35     | 20161231
 3 |     5      | 20151230
 4 |     10     | 20141231

Is there a way a way to get this values using a single linq queries or can I combine multiple linq queries somehow?

The idea I came up with was looking for december 31 but this will make problems of for any reason there is no entry for this date. Instead the query should load the last avaible day (as in ID 3).

private static IQueryable<Usage> GetYearValues(IQueryable<Usage> queryable)
{
    var firstYear = DateTime.Now.AddYears(-10);

    var yInfosArray = queryable
        .Where(
            s.GeneratedAt.Year >= firstYear.Year
            && s.GeneratedAt.Month == 12
            && s.GeneratedAt.Day == 31 || s.GeneratedAt >= today
        )
        .OrderByDescending(s => s.GeneratedAt);
    return yInfosArray;
}

Answer 1

Just sort by descending order, group by year and pick the first entry inside every group:

queryable
    .Where(x => x.GeneratedAt.Year >= DateTime.Now.Year - n) // if this year doesn't count to last n years, remove the `=` sign
    .OrderByDescending(x => x.ID) // or, by x.GeneratedAt if the table isnt time sorted
    .GroupBy(x => x.GeneratedAt.Year, (year, g) => g.First()) // select last (first in desc) of each year