使用LINQ获取第n行 [英] Fetch every nth row with LINQ

问题描述

我们的SQL数据库中有一个表，其中包含我需要用来创建图表的历史原始数据.我们通过Entity Framework和LINQ访问数据库.

对于较小的日期时间间隔，我可以简单地读取数据并生成图表:

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

但是我们要实现一项功能，您可以在图表中快速缩小"以包含较大的日期间隔(过去5天，上个月，过去6个月，过去10年，依此类推.)等等. /p>

我们不想为此绘制每个数据点的图表.我们想使用数据样本，我的意思是这样的-

• 过去5天:绘制表格中的每个数据点的图表
• 上个月:在表格中每隔10个数据点绘制图表
• 过去6个月:每100个数据点绘制图表

数据点和图表名称的数量仅是示例.我需要的是一种仅从数据库中选择第n"行的方法.

解决方案

您可以使用 juharr 所述，实体框架不支持此重载.解决这个问题的一种方法是做这样的事情-

var data = entity.DataLogSet.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        ToArray().
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

请注意添加了ToArray().不过，这并不是理想的选择，因为它将强制加载与初始查询之前仅选择每第n行的数据相匹配的数据.

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.

解决方案

You can use the Select overload that includes the item index of enumerations. Something like this should do the trick --

var data = myDataLogEnumeration.
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

If you need to limit the query with a Where or sort with OrderBy, you must do it before the first Select, otherwise the indexes will be all wrong --

var data = myDataLogEnumeration.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

Unfortunately, as juharr commented, this overload is not supported in Entity Framework. One way to deal with this is to do something like this --

var data = entity.DataLogSet.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        ToArray().
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

Note the addition of a ToArray(). This isn't ideal though as it will force loading all the data that matches the initial query before selecting only every nth row.

这篇关于使用LINQ获取第n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！