高效的LINQ to Entities查询

Question

我有一个实体集合Readings。 每个Reading都链接到一个名为Meter的实体。 （每个Meter都保存多个读数）。 每个Reading保存了一个仪表编号字段（int）和一个时间字段。 以下是一些简化的代码以演示它：

public class Reading
{
    int Id;
    int meterId;
    DateTime time;
}

public class Meter
{
    int id;
    ICollection<Readings> readings;    
}

在特定时间段和meterid列表中，如何最有效地获取每个仪表在该时间段内的第一个和最后一个读数？我能够遍历所有仪表，并为每个仪表获取该时期的第一个和最后一个读数，但我想知道是否有更有效的方法来实现此操作。还有一个奖励问题：相同的问题，但需要获取多个时间段的数据，而不仅仅是一个时间段。

- omer schleifer

---

你尝试过使用 Queryable.First() 和 Queryable.Last() 吗？ - user1914530

是的，我的解决方案是对于每个计量器和时间段进行First()和Last()操作 - 但这并没有考虑到我正在查看相同时间段的所有计量器。也许在这里使用某种分组会更有效？ - omer schleifer

Answer 1

我不确定您想要这些数据的具体形式，但您可以将其投影到一个匿名类型中：

var metersFirstAndLastReading = meters.Select(m => new 
    {
        Meter = m,
        FirstReading = m.readings.OrderBy(r => r.time).First(),
        LastReading = m.readings.OrderBy(r => r.time).Last()
    });

你可以像这样读取你的结果列表（这个例子只是为了说明）：

foreach(var currentReading in metersFirstAndLastReading)
{
    string printReadings = String.Format("Meter id {0}, First = {1}, Last = {2}", 
                               currentReading.Meter.id.ToString(),
                               currentReading.FirstReading.time.ToString(),
                               currentReading.LastReading.time.ToString());

    // Do something...
}

另一种选择是在Meter中创建属性，动态返回第一个和最后一个读数值：

public class Meter
{
    public int id;
    public List<Reading> readings;

    public Reading FirstReading 
    {
        get
        {
            return readings.OrderBy(r => r.time).First();
        }
    }

    public Reading LastReading
    {
        get
        {
            return readings.OrderBy(r => r.time).Last();
        }
    }
}

编辑：我对问题的理解有些误解。

以下是实现来确定仪表的第一个和最后一个读数，包括日期范围（假设meterIdList是ID的ICollection<int>，而begin和end是指定的日期范围）。

var metersFirstAndLastReading = meters
    .Where(m => meterIdList.Contains(m.id))
    .Select(m => new 
    {
        Meter = m,
        FirstReading = m.readings
                        .Where(r => r.time >= begin && r.time <= end)
                        .OrderBy(r => r.time)
                        .FirstOrDefault(),
        LastReading = m.readings
                        .Where(r => r.time >= begin && r.time <= end)
                        .OrderByDescending(r => r.time)
                        .FirstOrDefault()
    });

现在您将无法使用属性（因为您需要提供参数），因此方法作为替代方案可以正常工作：

public class Meter
{
    public int id;
    public List<Reading> readings;

    public Reading GetFirstReading(DateTime begin, DateTime end)
    {
        var filteredReadings = readings.Where(r => r.time >= begin && r.time <= end);

        if(!HasReadings(begin, end))
        {
            throw new ArgumentOutOfRangeException("No readings available during this period");
        }

        return filteredReadings.OrderBy(r => r.time).First();
    }

    public Reading GetLastReading(DateTime begin, DateTime end)
    {
        var filteredReadings = readings.Where(r => r.time >= begin && r.time <= end);

        if(!HasReadings(begin, end))
        {
            throw new ArgumentOutOfRangeException("No readings available during this period");
        }

        return filteredReadings.OrderBy(r => r.time).Last();
    }

    public bool HasReadings(DateTime begin, DateTime end)
    {
        return readings.Any(r => r.time >= begin && r.time <= end);
    }
}

- Dave New

---

@omer：非常愉快！您是否希望用户指定时间范围以确定第一次和最后一次读数？ - Dave New

是的，请看一下我的帖子，它说：“给定一个时间段”...但无论如何，我的真正问题是关于性能的，你的查询是否会提高性能，而不是我的“天真”解决方案？如果是这样，请解释一下为什么？谢谢。 - omer schleifer

@omer：你有什么好运气吗？ :) - Dave New

@davenewza，我最终使用了你的解决方案metersFirstAndLastReading（在“misunderstood”短语之后的第二个）。它确实有所帮助。如果您能够慷慨地纠正其中的两个问题，我将非常感激并将其标记为答案。1. Last()无效，而是需要按降序排序并选择First()（或更好的是FirstOrDefault()）。2.缺少按计量器ID过滤。我将把有效的代码添加到我的问题中。干杯 :-) - omer schleifer

@omer：使用meterIdList更新了代码，并修复了OrderByDescending部分（糟糕！）。使用First()或FirstByDefault()取决于数据的性质。当数据肯定被期望时，您肯定希望您的代码抛出异常-这取决于您 :) - Dave New

Answer 2

我有一个非常相似的数据模型，在这个代码中用于获取最旧阅读的地方，我只需将其更改为包括最新的。

我使用查询语法来做这样的事情：

var query = from reading in db.Readings
            group reading by reading.meterId
            into readingsPerMeter
            let oldestReadingPerMeter = readingsPerMeter.Min(g => g.time)
            let newestReadingPerMeter = readingsPerMeter.Max(g => g.time)
            from reading in readingsPerMeter
            where reading.time == oldestReadingPerMeter || reading.time == newestReadingPerMeter 
            select reading; //returns IQueryable<Reading> 

这将导致每个仪表只有最新和最旧的读数。

我认为这样做是有效的原因是它只需要一次数据库查询就可以获取每个仪表的所有读数，而不是为每个仪表进行多次查询。我们有大约40000个仪表，约3000万个读数。我刚刚在我们的数据上测试了查询，用了大约10秒钟。

执行的SQL是两个子查询之间的交叉连接，分别针对最小日期和最大日期。

更新：

由于这是可查询的，您应该能够在后面提供一个时间段，例如：

query.Where(r=>r.time > someTime1 && r.time < someTime2)

或者将其放入原始查询中，我只是喜欢这样分开。由于我们尚未执行获取数据的操作，因此查询尚未执行。

- Jim Wolff

---

@谢谢，请注意：a. 这不是按时间过滤的。b. 它只返回第一个或最后一个读数，而不是两者都返回。但我想我明白了。 - omer schleifer

更新以添加周期部分，它将为每个计量器获取周期内的最新和最旧读数。 - Jim Wolff

这确实可以提高性能。但是它每米只给出一个读数，而不是两个。有什么办法可以同时获取第一个和最后一个吗？谢谢。 - omer schleifer

是的，我能看到问题在于它返回了一个包含两个结果的行，而不是针对每个结果（最小值和最大值）返回一行。这需要在转换为SQL时进行调整。 - Jim Wolff

Answer 3

创建一个名为Result的返回类型新类，其外观如下：

public class Result
{
    public int MeterId;
    public Readings Start;
    public Readings Last;
}

我通过创建一个米的列表并填充一些数据来模拟您的情况，尽管您的查询应该基本相同

var reads = Meters.Where(x => x.readings != null)
                  .Select(x => new Result
                          {
                              MeterId = x.id,
                              Start = x.readings.Select(readings => readings).OrderBy(readings=>readings.time).FirstOrDefault(),
                              Last = x.readings.Select(readings=>readings).OrderByDescending(readings=>readings.time).FirstOrDefault()
                          });

- Lotok

---

或者OP可以使用匿名类型。 - Dave New

是的，我只是更喜欢有类型的，但匿名也不错。我认为使用返回类型可以增加一些清晰度。 - Lotok

Answer 4

public IEnumerable<Reading> GetFirstAndLastInPeriod
    (IEnumerable<Reading> readings, DateTime begin, DateTime end)
{
    return
        from reading in readings
        let span = readings.Where(item => item.time >= begin && item.time <= end)
        where reading.time == span.Max(item => item.time) 
            || reading.time == span.Min(item => item.time)
        select reading;            
}

- Jan P.

Answer 5

meters.Where(mt=>desiredMeters.Contains(mt)).Select(mt=>
   new{
     mt.Id,
     First = mt.Readings.Where(<is in period>).OrderBy(rd=>rd.Time).FirstOrDefault(),
     Last = mt.Readings.Where(<is in period>).OrderBy(rd=>rd.Time).LastOrDefault()
   });

如果您的每个计量器有很多读数，那么这种方法的性能就会受到影响，您应该考虑使用SortedList类来进行读数。

- Tormod

Answer 6

我的解决方案将会返回您想要的准确结果（在给定时间范围内包含读数的所有计量表列表）

public IList<Reading[]> GetFirstAndLastReadings(List<Meter> meterList, DateTime start, DateTime end)
     {       
        IList<Reading[]> fAndlReadingsList = new List<Reading[]>();

            meterList.ForEach(x => x.readings.ForEach(y =>
            {
                var readingList = new List<Reading>();
                if (y.time >= startTime && y.time <= endTime)
                {
                      readingList.Add(y);
                      fAndlReadingsList.Add(new Reading[] { readingList.OrderBy(reading => reading.time).First(), readingList.OrderBy(reading => reading.time).Last() });
                }
            }));

       return fAndlReadingsList;
    }

- Ashok Damani

Answer 7

感谢所有回复者，我得到了一些非常好的线索。以下是对我有效的解决方案：

        /// <summary>
        /// Fills the result data with meter readings matching the filters.
        /// only take first and last reading for each meter in period.
        /// </summary>
        /// <param name="intervals">time intervals</param>
        /// <param name="meterIds">list of meter ids.</param>
        /// <param name="result">foreach meter id , a list of relevant meter readings</param>
        private void AddFirstLastReadings(List<RangeFilter<DateTime>> intervals, List<int> meterIds, Dictionary<int, List<MeterReading>> result)
        {
            foreach (RangeFilter<DateTime> interval in intervals)
            {
                var metersFirstAndLastReading = m_context.Meter.Where(m => meterIds.Contains(m.Id)).Select(m => new
                {
                    MeterId = m.Id,
                    FirstReading = m.MeterReading
                                    .Where(r => r.TimeStampLocal >= interval.FromVal && r.TimeStampLocal < interval.ToVal)
                                    .OrderBy(r => r.TimeStampLocal)
                                    .FirstOrDefault(),
                    LastReading = m.MeterReading
                                    .Where(r => r.TimeStampLocal >= interval.FromVal && r.TimeStampLocal < interval.ToVal)
                                    .OrderByDescending(r => r.TimeStampLocal)
                                    .FirstOrDefault()
                });

                foreach (var firstLast in metersFirstAndLastReading)
                {
                    MeterReading firstReading = firstLast.FirstReading;
                    MeterReading lastReading = firstLast.LastReading;

                    if (firstReading != null)
                    {
                        result[firstLast.MeterId].Add(firstReading);
                    }

                    if (lastReading != null && lastReading != firstReading)
                    {
                        result[firstLast.MeterId].Add(lastReading);
                    }

                }

            }
        }


    }

- omer schleifer