locked
C# LINQ order by not working for a SQL table with a primary key RRS feed

  • Question

  • Hi All,

    I retrieve some data from a SQL table using the following C# statement. 

     List<MyObject> myList = myClass.getList();// get the List of objects from SQL MYTable
                

    Here is the schema(or the table creation query) I used to generate the table named MyTable.

    CREATE TABLE [dbo].[MyTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [I] [float] NULL,
    [J] [float] NULL,

     CONSTRAINT [PK_MyTABLE] PRIMARY KEY CLUSTERED 
    (
    [id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    After getting data from MyTable I want to order the myList in the descending order of I values. So I use the following C# statement.

    myList .OrderByDescending(i => i.I).ToList();

    BUT, this is NOT executed as I want. Instead it order it in the ascending order of the primary key that is id. Can you please point out the problem and try to fix this.

    If I edit the SQL command in the SQL string it works. For example, when I get the list from the SQL table I can use something like and it works.

    StringBuilder sql = new StringBuilder("SELECT * FROM MyTable ORDER BY I DESC)

    If I use a command like myList.Reverse(); it works as well.

    But why does not the following LINQ statement work in my scenario?

    myList .OrderByDescending(i => i.I).ToList();

    I hope you can help me out to understand this and try to fix the problem.  

    Also, is there any advantage in adding the ToList(); method at the end of the above LINQ statement performance wise?

    I thank you so much for your help on this.

    Clive


    • Edited by Clive2334 Monday, November 5, 2012 6:10 AM
    Monday, November 5, 2012 6:09 AM

Answers

  • @List<MyObject>myDescendingList = myList.OrderByDescending(i=>i.I)

    This only return u an IOrderedEnumerable<TSource>.

    Compared with this, "var" will "intellisense" know which type u wanna fetch

    So u have to use ToList():

    List<MyObject>myDescendingList = myList.OrderByDescending(i=>i.I).ToList();

    我的博客园
    慈善点击,点击此处
    和谐拯救危机,全集下载,净化人心

    • Edited by ThankfulHeart Monday, November 5, 2012 7:21 AM
    • Marked as answer by Clive2334 Monday, November 5, 2012 7:29 AM
    Monday, November 5, 2012 7:20 AM
  • The actual type returned by the OrderByDescending<T>() query is

    IOrderedEnumerable<T>

    so there is a slight loss of information to call it List<T> but I don't think it will matter.

    You could use

    List<MyObject> intead of var

    and many people do.  It tends to be a matter of personal preference as to whether you want to do the extra typing.


    Paul Linton

    • Marked as answer by Clive2334 Monday, November 5, 2012 7:28 AM
    Monday, November 5, 2012 7:24 AM
  • @Hi 编程志愿者Which statement is faster? Var type of List type?

    The same. "var" is just a syntax that will be analyzed to a List type.


    我的博客园
    慈善点击,点击此处
    和谐拯救危机,全集下载,净化人心

    • Marked as answer by Clive2334 Monday, November 5, 2012 7:29 AM
    Monday, November 5, 2012 7:28 AM
  • myList.OrderByDescending(... does not change the order of myList.  It returns a new list which has the desired order (myList is not changed).  You need something like

    var myDescendingList = myList.OrderByDescending(i=>i.I);


    Paul Linton

    • Proposed as answer by ThankfulHeart Monday, November 5, 2012 6:50 AM
    • Marked as answer by Clive2334 Monday, November 5, 2012 7:16 AM
    Monday, November 5, 2012 6:48 AM

All replies

  • Try this:

    myList.OrderByDescending(i => i.I*1.0).ToList();

    //If doesn't solve ur problem, plz tell us why and what result?


    我的博客园
    慈善点击,点击此处
    和谐拯救危机,全集下载,净化人心

    Monday, November 5, 2012 6:17 AM
  • myList.OrderByDescending(... does not change the order of myList.  It returns a new list which has the desired order (myList is not changed).  You need something like

    var myDescendingList = myList.OrderByDescending(i=>i.I);


    Paul Linton

    • Proposed as answer by ThankfulHeart Monday, November 5, 2012 6:50 AM
    • Marked as answer by Clive2334 Monday, November 5, 2012 7:16 AM
    Monday, November 5, 2012 6:48 AM
  • Hi,

    Hi I tried your statement and unfortunately it does not solve the problem. I always get the list in the ascending order of the primary key no matter how I order using LINQ statement.

    Do I have to fix something in the SQL table design? Specially the way I have set up the primary key which is id? 

    Do I have to fix up my primary key? I mean this part of the table design;

    CONSTRAINT [PK_MyTABLE] PRIMARY KEY CLUSTERED 
    (
    [id] ASC

    Please let me know.

    Thank you very much

    Monday, November 5, 2012 7:03 AM
  • Show us the rows from the table, the value of myList and the value of myDescendingList.

    By the way, Linqpad (www.linqpad.net) is quite useful for playing around with linq.


    Paul Linton

    Monday, November 5, 2012 7:06 AM
  • Hi PaulLinton,

    Thanks so much for your answer. It worked. 

    I have once more question. Is it also possible to use, 

    List<MyObject> myDescendingList = myList.OrderByDescending(i=>i.I);

    instead of 

    var myDescendingList = myList.OrderByDescending(i=>i.I);

    What is the difference between these two statements?

    Thank you so much again.


    Monday, November 5, 2012 7:18 AM
  • @List<MyObject>myDescendingList = myList.OrderByDescending(i=>i.I)

    This only return u an IOrderedEnumerable<TSource>.

    Compared with this, "var" will "intellisense" know which type u wanna fetch

    So u have to use ToList():

    List<MyObject>myDescendingList = myList.OrderByDescending(i=>i.I).ToList();

    我的博客园
    慈善点击,点击此处
    和谐拯救危机,全集下载,净化人心

    • Edited by ThankfulHeart Monday, November 5, 2012 7:21 AM
    • Marked as answer by Clive2334 Monday, November 5, 2012 7:29 AM
    Monday, November 5, 2012 7:20 AM
  • The actual type returned by the OrderByDescending<T>() query is

    IOrderedEnumerable<T>

    so there is a slight loss of information to call it List<T> but I don't think it will matter.

    You could use

    List<MyObject> intead of var

    and many people do.  It tends to be a matter of personal preference as to whether you want to do the extra typing.


    Paul Linton

    • Marked as answer by Clive2334 Monday, November 5, 2012 7:28 AM
    Monday, November 5, 2012 7:24 AM
  • Which statement is faster? Var type of List type? 

    You are correct. When I used List<MyObject> myDescendingList = myList.OrderByDescending(i=>i.I); 

    it gave me following error.

    Error 5 Cannot implicitly convert type 'System.Linq.IOrderedEnumerable<Class.MyObject>' to 'System.Collections.Generic.List<Class.MyObject>'. An explicit conversion exists (are you missing a cast?)

    Thank you so much for your explanation.

    Monday, November 5, 2012 7:26 AM
  • @Hi 编程志愿者Which statement is faster? Var type of List type?

    The same. "var" is just a syntax that will be analyzed to a List type.


    我的博客园
    慈善点击,点击此处
    和谐拯救危机,全集下载,净化人心

    • Marked as answer by Clive2334 Monday, November 5, 2012 7:29 AM
    Monday, November 5, 2012 7:28 AM