Every nth element of IQueryable EntityFramework and Linq

Question

I am currently querying a table in a database where I need to retrieve 24 evenly-spaced datapoints to plot on a graph the price of gold from a certain date in a dataset to the current time. The table could contain millions of rows of data.

I wanted to use modular (%) but the query when written in LINQ cannot be translated to the SQL query. This I believe is also due to the fact that IQueryable does not have an enumerator and the LINQ library does not have the necessary means to do this.

This does work when I .ToList() the orderedGoldData. However, on a dataset that is extremely large, I would rather write a query to do this.

public IEnumerable<HistoricalGoldDatum> GetHistoricGoldData(string userId, DateTime firstDate,
            int dataPoints = 24)
{
    var orderedGoldData =
        DbContext.UserBalanceHistoricalData
            .Where(gol => gol.DateCreated > firstDate)
            .OrderBy(gol => gol.DateCreated);
    var div = orderedGoldData.Count() / dataPoints;
    return orderedGoldData.Where((b, i) => i % div == 0);
}

How can I achieve what I wanted to achieve above on an IQueryable using LINQ or a standard query in the best time complexity?

Answer 1

In such scenario Linq to SQL might produce not the best efficent query so I would use standard query, something like this:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY DateCreated) AS rn FROM UserBalanceHistoricalData) hd
WHERE (hd.rn % div)=0