CODEWITHSUNDEEP
c#sqllinq
DotNetRussell
DotNetRussell

Reputation: 9857

Linq output changes

So I have a pretty simple SQL call

SELECT count(col1) as count, sum(col2) as charges, col3, col4 
FROM table GROUP BY col3, col4

When I do the above call I get 4,961 rows returned.

When I run the results through the following linq statement it returns the data in a different order every single time it runs.

I guess what my disconnect here is, is why do I get different results? Shouldn't the below linq block parse the data the same no matter what order it's in? 

IEnumerable<Chart.Point> recs = from a in
   (from r in cache.Data
      where ids.Contains(r[idColName].ToString())
         group r by r[ranColName].ToString() into g
            select new Chart.Point { Key = g.Key, Value = g.Sum(x => 
                 Convert.ToInt64(x[countCol])) }).Take(numberOfColumns)
     orderby a.Value descending, a.Key
     select a;

Upvotes: 0

Views: 121

Answers (1)

Steve Ruble
Steve Ruble

Reputation: 3895

Let's split the query to make things a little clearer:

var points = 
  from r in cache.Data                               
  where ids.Contains(r[idColName].ToString())       
  group r by r[ranColName].ToString() into g        
  select new Chart.Point {                          
   Key = g.Key,                                     
   Value = g.Sum(x => Convert.ToInt64(x[countCol])) 
  };

var topPoints = points.Take(numberOfColumns);

var orderedPoints = from a in topPoints
                    orderby a.Value descending, a.Key
                    select a;

The cache.Data property has an arbitrary order, because it's populated from a SQL query which doesn't specify ordering. This means that topPoints may end up with a different set of points each time. As a result, the ordering will appear to be different on different runs (but in reality it is the points being ordered which is different, not really the ordering of the points).

You should be able to get a consistent, ordered, and limited sequence of points by moving the Take operation after the ordering operation, so that the same points will always be at the top before the limit is applied.

Upvotes: 2

Related Questions