Fetch every nth row with LINQ

user_8004201 0
Viewed 1447

We have a table in our SQL database with historical raw data I need to create charts from. We access the DB via Entity Framework and LINQ.

For smaller datetime intervals, I can simply read the data and generate the charts:

var mydata = entity.DataLogSet.Where(dt => dt.DateTime > dateLimit);

But we want to implement a feature where you can quickly "zoom out" from the charts to include larger date intervals (last 5 days, last month, last 6 months, last 10 years and so on and so forth.)

We don't want to chart every single data point for this. We want to use a sample of the data, by which I mean something like this --

  • Last 5 days: chart every data point in the table
  • Last month: chart every 10th data point in the table
  • Last 6 months: chart every 100th data point

The number of data points and chart names are only examples. What I need is a way to pick only the "nth" row from the database.

entity-framework linq c#
2 Answers

There might be a trick that is supported by ef that might work for this.

if (step != 0)
  query = query.Where(_ => Convert.ToInt32(_.Time.ToString().Substring(14, 2)) % step == 0);

this code converts the date into string then cuts the minutes out converts the minutes into an int and then gets every x'th minute for example if the variable step is 5 it's every 5 minutes.

For Postgresql this converts to:

WHERE ((substring(c.time::text, 15, 2)::INT % @__step_1) = 0)

this works best with fixed meassure points such as once a minute.

However, you can use the same method to group up things by cutting up to the hour or the minutes or the first part of the minute (10 minutes grouped) and use aggregation functions such as max() average() sum(), what might even is more desirable. For example, this groups up in hours and takes the max of most but the average of CPU load:

using var ef = new DbCdr.Context();
IQueryable<DbCdr.CallStatsLog> query;

query = from calls in ef.Set<DbCdr.CallStatsLog>()
        group calls by calls.Time.ToString().Substring(0, 13)
        into g
        orderby g.Max(_ => _.Time) descending
        select new DbCdr.CallStatsLog()
        {
            Time = g.Min(_ => _.Time),
            ConcurrentCalls = g.Max(_ => _.ConcurrentCalls),
            CpuUsage = (short)g.Average(_ => _.CpuUsage),
            ServerId = 0
        };

var res = query.ToList();

translates to:

 SELECT MAX(c.time) AS "Time",
 MAX(c.concurrent_calls) AS "ConcurrentCalls",
 AVG(c.cpu_usage::INT::double precision)::smallint AS "CpuUsage",
 0 AS "ServerId" 
 FROM call_stats_log AS c                                                                                                                                                                                          
 GROUP BY substring(c.time::text, 1, 13)                                                                                                                                                                           
 ORDER BY MAX(c.time) DESC

note: the examples work with postgres and iso datestyle.

user_43429680
Related