LINQ not sorting List<> properly

Question

My EF query is supposed to be sorting by the date of the first Product in the list, but for some reason, it only sorts most of the products and some of the dates are in the wrong order.

Here's the code...

using (var context = new SalesEntities())
{
   var groupedData = context.s84_Schedule.AsExpandable()
      .Where(predicate)
      .GroupBy(c => new { c.CustomerID, c.s84_Customer.CustomerName, c.SubdivisionID, c.s84_Subdivision.SubdivisionName, c.LotNumber })
      .Select(grouped => new s84_Report_Project_POCO
      {
         CustomerID = grouped.Key.CustomerID,
         CustomerName = grouped.Key.CustomerName,
         SubdivisionID = grouped.Key.SubdivisionID,
         SubdivisionName = grouped.Key.SubdivisionName,
         LotNumber = grouped.Key.LotNumber,
         Products = grouped.Select(x => new s84_Report_Project_Product
         {
            ProductID = x.ProductID,
            ProductName = x.s84_Product.ProductName,
            ProductDate = x.CustomerExpectedDate,
            FieldRepID = x.FieldRepID,
            FieldRepName = x.s84_FieldRep.FieldRepName,
            InstallerID = x.InstallerID,
            InstallerName = x.s84_Installer.InstallerName,
            StatusID = x.StatusID,
            StatusColor = x.s84_Status.StatusColor,
            StatusName = x.s84_Status.StatusName,
            Completed = x.Completed
         }).ToList()
      });

   var finalList = groupedData.ToList().Where(x => x.Products.Last().Completed == false).ToList();

   List<s84_Report_Project_POCO> lst = finalList.OrderBy(x => x.Products.First().ProductDate).ToList();
   return lst;
}

Code seems good to me, but look at how one of the dates is out of order...

weird sorting http://www.84sales.com/weird_sort.png

last line... List lst = finalList.OrderBy(x => x.Products.First().ProductDate).ToList(); — Targaryen, Nov 18 '15 at 16:27
It looks like you're sorting on only the first product in a collection of products' ProductDate. Is this the same value you're using in your column?. — Derek Van Cuyk, Nov 18 '15 at 16:28
Yes, "Frame Labor Start," "Frame Labor Rough In," and "Framing Labor Punch" are all products, so each of those dates is a ProductDate. The first date in each row should be the x.Products.First().ProductDate ....obviously this OrderBy is not doing what I expected. — Targaryen, Nov 18 '15 at 16:30
And you're using the lst and not finalList variable right, since finalList is not sorted? — Derek Van Cuyk, Nov 18 '15 at 16:32
Your question has a *lot* of irrelevant code in it, and we don't know how you're using the result. You're ordering by the *first* date in each group - is that what you're displaying? We can't tell. Please post a short but *complete* program demonstrating the problem. — Jon Skeet, Nov 18 '15 at 16:35
It looks like you're calling .first() on an unordered list, so there's no guarantee you're getting the product you want. 10/7 is before both the 10/16 and the 11/6 in the next row, so it might be pulling one of those as the comparison. You need to make sure you're getting the correct product you want to order by, instead of just taking the first one. — Aaron Gates, Nov 18 '15 at 16:39
Yes, I am using lst to display the data. Also I updated the screenshot so it includes all of the data I am displaying. @JonSkeet Does the updated screenshot help? — Targaryen, Nov 18 '15 at 16:43
Not nearly as much as a short but complete program demonstrating the problem would. Aside from anything else, we have no idea how `x.Products.First().ProductDate` relates to "Frame Labor Start"... — Jon Skeet, Nov 18 '15 at 16:52
You could make it complete by creating a console app with all the data and code in required to reproduce the problem. Currently we don't know the types of half the values, etc. Also note that you're only showing dates for products with the selected product ID - which may exclude the one you did the ordering on... — Jon Skeet, Nov 18 '15 at 16:59
@JonSkeet I think you may have just fixed a problem I did not know I had. However, the answers below fixed my original problem. — Targaryen, Nov 18 '15 at 17:01

score 1 · Accepted Answer · edited Nov 18 '15 at 16:35

Try doing the order by on the inital select

var groupedData = context.s84_Schedule.AsExpandable()
                              .Where(predicate)
                              .GroupBy(c => new { c.CustomerID,
                                                  c.s84_Customer.CustomerName, 
                                                  c.SubdivisionID, 
                                                  c.s84_Subdivision.SubdivisionName, 
                                                  c.LotNumber })
                              .Select(grouped => new s84_Report_Project_POCO
                              {
                                  CustomerID = grouped.Key.CustomerID,
                                  CustomerName = grouped.Key.CustomerName,
                                  SubdivisionID = grouped.Key.SubdivisionID,
                                  SubdivisionName = grouped.Key.SubdivisionName,
                                  LotNumber = grouped.Key.LotNumber,
                                  Products = grouped
                                    .Select(x => new s84_Report_Project_Product
                                  {
                                      ProductID = x.ProductID,
                                      ProductName = x.s84_Product.ProductName,
                                      ProductDate = x.CustomerExpectedDate,
                                      FieldRepID = x.FieldRepID,
                                      FieldRepName = x.s84_FieldRep.FieldRepName,
                                      InstallerID = x.InstallerID,
                                      InstallerName = x.s84_Installer.InstallerName,
                                      StatusID = x.StatusID,
                                      StatusColor = x.s84_Status.StatusColor,
                                      StatusName = x.s84_Status.StatusName,
                                      Completed = x.Completed
                                  }).OrderBy(x => x.CustomerExpectedDate).ToList()
                              });

This has to be `.OrderBy(x => x.ProductDate)`. Please fix it. — ataravati, Nov 18 '15 at 16:32

score 1 · Answer 2 · answered Nov 18 '15 at 16:43

The problem is the .First() function, witch returns the first record, but not necessarly in date order. if you wich to order your grouped datas by date so that the First() function returns the most recent date, you'll need to order your datas before grouping them, and then REorder your results with the First()function :

using (var context = PrimaryConnection.returnNewConnection())
        {
            var groupedData = context.s84_Schedule.AsExpandable()
                              .Where(predicate)
                              .GroupBy(c => new { c.CustomerID, c.s84_Customer.CustomerName, c.SubdivisionID, c.s84_Subdivision.SubdivisionName, c.LotNumber })
                              .Select(grouped => new s84_Report_Project_POCO
                              {
                                  CustomerID = grouped.Key.CustomerID,
                                  CustomerName = grouped.Key.CustomerName,
                                  SubdivisionID = grouped.Key.SubdivisionID,
                                  SubdivisionName = grouped.Key.SubdivisionName,
                                  LotNumber = grouped.Key.LotNumber,
                                  Products = grouped
                                    .Select(x => new s84_Report_Project_Product
                                  {
                                      ProductID = x.ProductID,
                                      ProductName = x.s84_Product.ProductName,
                                      ProductDate = x.CustomerExpectedDate,
                                      FieldRepID = x.FieldRepID,
                                      FieldRepName = x.s84_FieldRep.FieldRepName,
                                      InstallerID = x.InstallerID,
                                      InstallerName = x.s84_Installer.InstallerName,
                                      StatusID = x.StatusID,
                                      StatusColor = x.s84_Status.StatusColor,
                                      StatusName = x.s84_Status.StatusName,
                                      Completed = x.Completed
                                  }).Orderby(t => t.CustomerExpectedDate).ToList()
                              });

            var finalList = groupedData.ToList().Where(x => x.Products.Last().Completed == false).ToList();

            List<s84_Report_Project_POCO> lst = finalList.OrderBy(x => x.Products.First().ProductDate).ToList();

score 1 · Answer 3 · answered Nov 18 '15 at 16:52

All SQL queries (and hence Linq queries, when attached to a SQL database) have a random order, unless you sort them.

Products is not sorted - hence it has a random order. You sort by Products.First(), but Products has a random order, so your sort will also be random.

Make sure Products is sorted within the query, and you should be ok.

  Products = grouped.Select(....)
                    .OrderBy(x => x.ProductDate)
                    .ToList()

LINQ not sorting List<> properly

3 Answers3