7
\$\begingroup\$

I want to get just the first and last values in a date range. I have the following code:

  using (var myEntities = new dataEntities())
  {
      var myValues = (from values in myEntities.PointValues
                      where values.PointID == dataValue && values.DataTime >= fromDate && values.DataTime <= toDate
                      orderby values.DataTime
                      select new BarChartValue
                      {
                          Time = values.DataTime,
                          Value = values.DataValue
                      }).ToList();
      var myBarChart = new List<BarChartValue> { myValues.First(), myValues.Last() };
      return myBarChart;
  }

This seems inefficient to me as I select the entire date range values initially. I can't seem to find a way to put first and last into the select command. Surely there must be a way to do this?

In response to Svick.

So, if I do this:

      var firstValue = (from values in myEntities.PointValues
                      where values.PointID == dataValue && values.DataTime >= fromDate && values.DataTime <= toDate
                      orderby values.DataTime
                      select new BarChartValue
                      {
                          Time = values.DataTime,
                          Value = values.DataValue
                      }).First();
      var lastValue = (from values in myEntities.PointValues
                        where values.PointID == dataValue && values.DataTime >= fromDate && values.DataTime <= toDate
                        orderby values.DataTime
                        select new BarChartValue
                        {
                            Time = values.DataTime,
                            Value = values.DataValue
                        }).Last();

Is that the best way to do it? I don't have any profiling tools (or if I do, I'm unsure how to use them).

The solution as provided by svick is:

      var myValues = (from values in myEntities.PointValues
                      where values.PointID == dataValue && values.DataTime >= fromDate && values.DataTime <= toDate
                      orderby values.DataTime
                      select new BarChartValue
                          {
                              Time = values.DataTime,
                              Value = values.DataValue
                          });
      var myBarChart = new List<BarChartValue> { myValues.First(), myValues.OrderByDescending(p => p.Time).First() };

A lot of this comes from my lack of understanding of the difference between IQueryable, IEnumerable and List. Here is a good explanation that helped me understand a bit more of what's going on:

https://stackoverflow.com/questions/4844660/differences-between-iqueryable-list-ienumerator

\$\endgroup\$
1

2 Answers 2

4
\$\begingroup\$

What you could do is to remove the ToList() from your query. This would change your code from making a single query that returns many values to two queries, each returning a single value.

\$\endgroup\$
5
  • \$\begingroup\$ Question edited. \$\endgroup\$
    – Family
    May 3, 2013 at 14:59
  • \$\begingroup\$ @Family Yeah, except that you don't need to repeat yourself. You should still have myValues (but without ToList()) and just call myValues.First() and myValues.Last(), like before. \$\endgroup\$
    – svick
    May 3, 2013 at 15:06
  • \$\begingroup\$ Just in case others have a similar problem. I couldn't use myValues.Last(), I had to use myValues.OrderByDescending(p => p.Time).First(); \$\endgroup\$
    – Family
    May 3, 2013 at 16:21
  • \$\begingroup\$ @svick a query. Does First() on the IQueryable interface produce SQL (if that's the database) like Select top 1 * ? \$\endgroup\$
    – dreza
    May 3, 2013 at 22:59
  • \$\begingroup\$ @dreza Yeah, that's exactly what it should do. \$\endgroup\$
    – svick
    May 3, 2013 at 23:04
1
\$\begingroup\$

Create the base query:

var myValues = (from values in myEntities.PointValues
                where values.PointID == dataValue && values.DataTime >= fromDate && values.DataTime <= toDate
                select new BarChartValue
                {
                    Time = values.DataTime,
                    Value = values.DataValue
                })

Then build up the first and last query:

var firstAndLast = myValues.OrderBy(x => values.DataTime).Take(1)
                    .Union(
                        myValues.OrderByDescending(x => values.DataTime).Take(1)
                    ).ToList();

This will result in one SQL query if the backend is an SQL database and will not recieve entites which are unnecessary (i think the Last() would download all entities).

\$\endgroup\$
0

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .