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\$
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\$
  • \$\begingroup\$ Question edited. \$\endgroup\$ – Family May 3 '13 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 '13 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 '13 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 '13 at 22:59
  • \$\begingroup\$ @dreza Yeah, that's exactly what it should do. \$\endgroup\$ – svick May 3 '13 at 23:04
0
\$\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\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.