从IQueryable创建自定义对象，而无需将所有内容都加载到内存中 [英] Create custom objects from IQueryable without loading everything into memory

问题描述

这是对此问题的后续问题.您应该先阅读.

This is a follow up question to this question. You should read that first.

由于此答案，我现在有了一个查询，该查询将返回正确的条目.参见:

I have now, thanks to this answer, created a query that will return the correct entries. See:

IQueryable<Data> onePerHour = dataLastWeek
    .Where(d => 
        !dataLastWeek
        .Any(d2 =>
            d2.ArchiveTime.Date == d.ArchiveTime.Date &&
            d2.ArchiveTime.Hour == d.ArchiveTime.Hour &&
            d2.ArchiveTime < d.ArchiveTime));

现在要处理条目并在图表上显示它们，我只需要模型类Data的一个或两个属性.用例是这样的:

Now for processing the entries and displaying them on a chart, I only need one or two properties of the model class Data. The use case is something like this:

List<Data> actualData = onePerHour.ToList();

var tempCTupels = new List<TimeTupel<float>>();
tempCTupels.AddRange(actualData.Select(d => new TimeTupel<float>(d.ArchiveTime, d.TempC)));

var co2Tupels = new List<TimeTupel<float>>();
tempCTupels.AddRange(actualData.Select(d => new TimeTupel<float>(d.ArchiveTime, d.CO2Percent)));

TimeTupel非常简单，定义如下:

TimeTupel is very simple and defined like this:

public class TimeTupel<TData>
{
    public TimeTupel(DateTime time, TData yValue)
    {
        Time = time;
        YValue = yValue;
    }

    public DateTime Time { get; set; }
    public TData YValue { get; set; }
}

问题

当前actualdata是List<Data>，这意味着它已完全加载到内存中.
由于我仅使用两个属性，因此无需检索整个对象即可创建TimeTupel.

Question

Currently actualdata is a List<Data> which means it's fully loaded in memory.
Since I only use two properties I wouldn't need to retrieve the whole object to create the TimeTupels.

现在我的问题是如何实现性能提升?删除ToList是正确的方法吗?

Now my question is how would I achieve a performance increase? Is it the correct approach to remove the ToList?

• 只需使用IQueryable<Data>创建TimeTupel:
  IQueryable<Data> actualData = onePerHour;产生运行时错误("System.InvalidOperationException:'Sql Tree中的空TypeMapping'")

• Just using the IQueryable<Data> to create the TimeTupel:
  IQueryable<Data> actualData = onePerHour; yields a runtime error ("System.InvalidOperationException: 'Null TypeMapping in Sql Tree'")

使用AsEnumerable:
IEnumerable<Data> actualData = onePerHour.AsEnumerable();很慢，大约需要22秒才能获取10天的数据

Using AsEnumerable:
IEnumerable<Data> actualData = onePerHour.AsEnumerable(); is slow, takes around 22 seconds for 10 days worth of data

使用上述代码中的ToList(ToArray几乎相等):
List<Data> actualData = onePerHour.ToList();更快，对于相同数量的数据大约需要5秒钟

Using ToList as seen in the above code (ToArray is almost equal):
List<Data> actualData = onePerHour.ToList(); is faster, takes around 5 seconds for the same amount of data

推荐答案

您可以在Select语句中使用匿名类型，以仅将所需的数据列检索到内存中，然后将该内存中的数据转换为TimeTupel< >从那里上课.看起来像这样:

You can use an anonymous type in a Select statement to retrieve only the needed columns of data into memory, and then convert that in-memory data into the TimeTupel<> class from there. It would look like this:

var actualData = dataLastWeek
    .Where(d => 
        !dataLastWeek
        .Any(d2 =>
            d2.ArchiveTime.Date == d.ArchiveTime.Date &&
            d2.ArchiveTime.Hour == d.ArchiveTime.Hour &&
            d2.ArchiveTime < d.ArchiveTime))
    .Select(d => new { d.ArchiveTime, d.TempC, d.CO2Percent})
    .ToList();

var tempCTupels = actualData.Select(d => new TimeTupel<float>(d.ArchiveTime, d.TempC)).ToList();

var co2Tupels = actualData.Select(d => new TimeTupel<float>(d.ArchiveTime, d.CO2Percent)).ToList();

这篇关于从IQueryable创建自定义对象，而无需将所有内容都加载到内存中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！