Using Linq to select the first and last values

Question

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

• dreza: Looks like Last() might be implemented already potentially? msdn.microsoft.com/en-us/library/bb354927%28v=vs.100%29.aspx. Guess it might depend on your Target framework though. As a query do you know why you couldn't use that?

Answer 1

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.

• Family: Question edited.
• svick: @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.
• Family: Just in case others have a similar problem. I couldn't use myValues.Last(), I had to use myValues.OrderByDescending(p => p.Time).First()
• dreza: @svick a query. Does First() on the IQueryable interface produce SQL (if that's the database) like Select top 1 * ?
• svick: @dreza Yeah, that's exactly what it should do.

Answer 2

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).