none
orderby IComparer RRS feed

  • Question

  • I am trying to find a way to order the items from queries similar to below by i.name where the name consists of letters and numbers, eg. S72-1, S72-2....S72-10. The list in the datagrid view for the moment is incorrect as S72-10 comes before S72-2. I have read through the very useful blog here

    http://zootfroot.blogspot.com/2009/09/natural-sort-compare-with-linq-orderby.html

    and tried to implement it but without success

     

     var items = ( from i in me.items
                                  join c in me.Categories on i.categoryID equals c.categoryID
                                  join b in me.Brands on i.brandID equals b.brandID
                                  join o in me.Origins on i.originID equals o.originID
                                  join s in me.Status on i.statusID equals s.statusID
                                  join t in me.Types on i.typeID equals t.typeID into sr
                                  from x in sr.DefaultIfEmpty()
                                       //orderby c.categoryID, c.categoryName (NOT ALPHANUMERIC)
                                  select new
                                  {
                                      i.itemID,
                                      i.name,
                                      i.Type.typeName,
                                      b.brandName,
                                      i.model,
                                      i.serialNo,
                                      i.aiNumber,
                                      o.originCountry,
                                      i.cost,
                                      c.categoryName,
                                      s.currentStatus,
                                      i.note,
                                      i.info
                                  } ).OrderBy( z => z.name, new NaturalSortComparer<string>() );
    

    Nothing is loaded into the datagridview. Is it possible to use the class NaturalSortComparer in the blog in a LINQ query syntax? If not is there another way to sort the list alphanumerically?

    Thanks for any help. 

     

     

     


    :-( Still trying to program
    Tuesday, December 27, 2011 6:51 PM

Answers

  • It creates a SQL statement that is sent to the db and server side this NaturalSortComparer is unkown and can't be translated. In this case you would have to first retrieve the data (using ToList()) and then only sort them client side.

    It looks rather unnatural to me. The root cause is that this string seems to be made of distinct parts that needs to be sorted separately. A possible option could be to split those data in your model (i.e. if a code has multiple parts, it's easier to store each part separately to be able to easily process each part rather than storing it in a single column and splitting it, this way you'll be able to just sort on o.part1, o.part2 etc...). 


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Jonsey Thursday, December 29, 2011 10:07 AM
    • Edited by Patrice ScribeMVP Thursday, December 29, 2011 10:08 AM
    Thursday, December 29, 2011 9:53 AM

All replies

  • Hi,

    This is correct S72-10 is before S72-2 exactly as S72-BA would be before S72-C when using an alphanumeric ordering.

    If you want a numeric (and not alphanumeric order) for this part you'll likely have some work to do possibly spliting on the - character and sorting each component separately perhaps what is before - using the alphanumeric ordering and what is behind - using the numeric ordering ?

    Another option would be to always include the leading 0 character (that is S72-10 and S72-02 will sort correctly).

    Finally I'm not sure if you can have something such as S8-10 and S72-10 and if S8-10 should be before ?

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, December 28, 2011 8:25 PM
  • Thanks Patrice.

    This below works on an array

    var sortedList = ( from l in se.Items
                              select new { l.name } ).OrderBy( o => o.name, new NaturalSortComparer<string>() ).ToList();

    but doesn't on a database using LinqToEntity as per my original post (anonymous type I suppose). The error message is "LINQ to Entities does not recognize the method"

    A shame really as I am sure there are many people who would like to see natural sorting implemented in .net.

    I will probably have to go with the leading 0. 

    Thanks for your help.


    :-( Still trying to program
    Thursday, December 29, 2011 6:51 AM
  • It creates a SQL statement that is sent to the db and server side this NaturalSortComparer is unkown and can't be translated. In this case you would have to first retrieve the data (using ToList()) and then only sort them client side.

    It looks rather unnatural to me. The root cause is that this string seems to be made of distinct parts that needs to be sorted separately. A possible option could be to split those data in your model (i.e. if a code has multiple parts, it's easier to store each part separately to be able to easily process each part rather than storing it in a single column and splitting it, this way you'll be able to just sort on o.part1, o.part2 etc...). 


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Jonsey Thursday, December 29, 2011 10:07 AM
    • Edited by Patrice ScribeMVP Thursday, December 29, 2011 10:08 AM
    Thursday, December 29, 2011 9:53 AM
  • I understand now why the method is not known, thank you for explaining it. That will save me some head scratching in the future.

    I think your suggestion to split the string as "S72-" and "1",  "S72-" and "2" etc in two columns is the best so I will crack on and update the table and then the model.

    Many thanks.


    :-( Still trying to program
    Thursday, December 29, 2011 10:27 AM