locked
Dynamic OrderBy with linq & XML RRS feed

  • Question

  • User1196439756 posted

    Hello Pros!

    I have an asp.net forms page that contains a gridview. This gridview shows a list of active players in a game server and feed from an XML file that lives on the web server. I would like to add dynamic sorting to the gridivew so the end user can sort by multiple columns (in which ever order they prefer). The problem is I cannot figure out how to write my linq statement to sort dynamically and account for data type and ascending/descending options.

    It orders fine if the values are hard coded in the Order By block, but as this is going to be dynamic it cannot be hard coded.

                Dim xmlPlayers As XDocument = XDocument.Load("PathToMyXMLFiles\players.xml")
                Dim playerresult = From a In xmlPlayers.Descendants("PlayerRecord")
                                   Order By CType(a.Element("Score"), Integer) Descending, CType(a.Element("PlayerName"), String) Descending
                Select New With
                       {Key .PlayerID = a.Element("PlayerID").Value,
                        Key .HeroID = a.Element("HeroID").Value,
                        Key .PlayerName = a.Element("PlayerName").Value,
                        Key .Class = a.Element("Class").Value,
                        Key .Level = a.Element("Level").Value,
                        Key .Ping = a.Element("Ping").Value,
                        Key .Score = a.Element("Score").Value,
                        Key .Kills = a.Element("Kills").Value,
                        Key .Deaths = a.Element("Deaths").Value,
                        Key .Suicides = a.Element("Suicides").Value,
                        Key .Role = a.Element("Role").Value,
                        Key .Team = a.Element("Team").Value,
                        Key .IsAlive = a.Element("IsAlive").Value,
                        Key .Slot = a.Element("Slot").Value,
                        Key .Idle = a.Element("Idle").Value}
    
                PlayerGV.DataSource = playerresult
                PlayerGV.DataBind()

    Sample XML:

    <?xml version="1.0" standalone="yes"?>
    <DocumentElement>
      <PlayerRecord>
        <PlayerID>1000064523264</PlayerID>
        <HeroID>892381267</HeroID>
        <PlayerName>OneFryShort</PlayerName>
        <Class>Commando</Class>
        <Level>25</Level>
        <Ping>70</Ping>
        <Score>75</Score>
        <Kills>0</Kills>
        <Deaths>0</Deaths>
        <Suicides>0</Suicides>
        <Role>Regular</Role>
        <Team>Royal</Team>
        <IsAlive>1</IsAlive>
        <Slot>0</Slot>
        <Idle>0</Idle>
      </PlayerRecord>
      <PlayerRecord>
        <PlayerID>1000144311374</PlayerID>
        <HeroID>922199443</HeroID>
        <PlayerName>compa_24</PlayerName>
        <Class>Gunner</Class>
        <Level>23</Level>
        <Ping>115</Ping>
        <Score>885</Score>
        <Kills>4</Kills>
        <Deaths>0</Deaths>
        <Suicides>0</Suicides>
        <Role>Regular</Role>
        <Team>National</Team>
        <IsAlive>1</IsAlive>
        <Slot>8</Slot>
        <Idle>0</Idle>
      </PlayerRecord>
      <PlayerRecord>
        <PlayerID>1000306952891</PlayerID>
        <HeroID>996575933</HeroID>
        <PlayerName>17bruce</PlayerName>
        <Class>Gunner</Class>
        <Level>23</Level>
        <Ping>0</Ping>
        <Score>0</Score>
        <Kills>0</Kills>
        <Deaths>0</Deaths>
        <Suicides>0</Suicides>
        <Role>Regular</Role>
        <Team>National</Team>
        <IsAlive>0</IsAlive>
        <Slot>12</Slot>
        <Idle>0</Idle>
      </PlayerRecord>
    </DocumentElement>

    Ideally I would like to be able to just do something like this, but I have tried and cannot get it to work:

    http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library

    I have been trying my best at google foo, but have fallen short of an answer. Any help would be very much appreciated!

    Tuesday, January 27, 2015 7:29 PM

Answers

  • User1508394307 posted

    linq is not designed for such kind of "dynamic" things.

    My proposal will work and that fact that you have multiple columns is irrelevant here. I think you will have the same problem with a huge "IF" with any approach.

    I think you should read xml into a datatable and then sort as required without using linq.

    Example

    Dim ds As New DataSet()
    ds.ReadXml(Server.MapPath("~/.../players.xml"))
    
    DataView dv = ds.Tables(0).DefaultView
    dv.Sort = "Column1 ASC, Column2 DESC"
    
    PlayerGV.DataSource = dv
    PlayerGV.DataBind()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 3, 2015 2:13 AM

All replies

  • User1711366110 posted

    Hi rdavidson22,
      As per your case, you can try like below codings :

    Dim xmlPlayers As XDocument = XDocument.Load("PathToMyXMLFiles\players.xml")
    Dim playerresult = (From a In xmlPlayers.Descendants("PlayerRecord")
                      Order By Integer.Parse(a.Element("Score").Value) Descending, 
                              (a.Element("PlayerName").Value) Descending
                Select New With
                  {
                        Key .PlayerID = a.Element("PlayerID").Value,
                        Key .HeroID = a.Element("HeroID").Value,
                        Key .PlayerName = a.Element("PlayerName").Value,
                        Key .Class = a.Element("Class").Value,
                        Key .Level = a.Element("Level").Value,
                        Key .Ping = a.Element("Ping").Value,
                        Key .Score = a.Element("Score").Value,
                        Key .Kills = a.Element("Kills").Value,
                        Key .Deaths = a.Element("Deaths").Value,
                        Key .Suicides = a.Element("Suicides").Value,
                        Key .Role = a.Element("Role").Value,
                        Key .Team = a.Element("Team").Value,
                        Key .IsAlive = a.Element("IsAlive").Value,
                        Key .Slot = a.Element("Slot").Value,
                        Key .Idle = a.Element("Idle").Value
                  })
    
                PlayerGV.DataSource = playerresult
                PlayerGV.DataBind()

    For more information, Click here to check the Linq to XML order by descending
    --
    with regards,
    Edwin

    Thursday, January 29, 2015 2:44 AM
  • User1196439756 posted

    Hi Edwin,

    Thanks for the reply, but I don't see how I can make this any more dynamic then the order by code I already have working. I won't always know which columns the end user will sort by so I need to be able to pass them into the linq order by clause dynamically as variables.

    Thursday, January 29, 2015 7:21 PM
  • User1196439756 posted

    Giving out hugs for anyone who can help with this oneLaughing

    Sunday, February 1, 2015 9:48 AM
  • User1508394307 posted

    In principle it's very easy.

    You set your gridview as

    <asp:GridView ID="PlayerGV" AllowSorting="true" OnSorting="PlayerGV_Sorting" ...

    That will call a method PlayerGV_Sorting where you could find the selected column (SortExpression) which you could use to sort in linq query

    Instead of specifing 

    Order By CType(a.Element("Score"), Integer) Descending, CType(a.Element("PlayerName"), String) Descending

    you set 

    Order By a.Element(sortElement).Value

    and that should work.

    You cannot set Ascending/Descending dynamically, but you could change the order using some tricks, e.g.

    Dim playerresult = From ...
    
    If Not sortAscending Then
        playerresult = playerresult.AsEnumerable().Reverse()
    End If
    
    PlayerGV.DataSource = playerresult
    PlayerGV.DataBind()

    That's all,

    but that will work well for string data only. For example, if Slot has 0, 8, 12 then they will be sorted as strings as 0, 12, 8 because "8">"1", etc. So, you either need to create a list of specific type/class where you will specify data type for each of your fields, or use some tricks. For example, you could have 2 queries:

    Dim playerresult As IEnumerable(Of Object)
    
    If sortElement = "Slot" Or sortElement = "Score" Or ... Then  
        playerresult = From a In xmlPlayers.Descendants("PlayerRecord")
                                    Order By CType(a.Element(sortElement).Value, Integer)
                                    Select New With
                                    ...
    Else
        playerresult = From a In xmlPlayers.Descendants("PlayerRecord")
                                    Order By a.Element(sortElement).Value
                                    Select New With
                                    ...
    End If

    In this case it should sort according to the data type.

    Hope this helps.

    Sunday, February 1, 2015 12:25 PM
  • User1196439756 posted

    Hi smirnov,

    I wish this had answered my question, but that would be a huge select case or if statement because I want to allow multiple column sorting and each column's data type varies.

    For example my order by could be this:

    Order By CType(a.Element("Score"), Integer) Descending, CType(a.Element("PlayerName"), String

    or it coule be this:

    Order By CType(a.Element("Ping"), Integer) Descending, CType(a.Element("Team"), String Ascending, CType(a.Element("Class"), String Descending

    or it could be as simple as this:

    Order By CType(a.Element("Deaths"), Integer) Descending

    I just wish there was a way to build my order by statement in a string then pass the string into the order by like in this example:

    http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library

    Monday, February 2, 2015 6:06 PM
  • User1508394307 posted

    linq is not designed for such kind of "dynamic" things.

    My proposal will work and that fact that you have multiple columns is irrelevant here. I think you will have the same problem with a huge "IF" with any approach.

    I think you should read xml into a datatable and then sort as required without using linq.

    Example

    Dim ds As New DataSet()
    ds.ReadXml(Server.MapPath("~/.../players.xml"))
    
    DataView dv = ds.Tables(0).DefaultView
    dv.Sort = "Column1 ASC, Column2 DESC"
    
    PlayerGV.DataSource = dv
    PlayerGV.DataBind()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 3, 2015 2:13 AM
  • User1196439756 posted

    AHH HA! I don't know why I didn't think about converting it to a dataset. I guess I was just hung up on getting linq to do it. Thanks smirnov for the work around!

    As promised!

      ____(```\        .-'""""`-.        /```)____
     (____     \_____ /  (O  O)  \ _____/     ____)
    (____            (      )     )            ____)
     (____     _______\  \____/  /_______     ____)
       (______/        `-.____.-'        \______)
    Tuesday, February 3, 2015 4:57 PM
  • User2000438054 posted

    Thank you for the information very helpful at all

    regards

    sutopo sasuke

    Thursday, February 19, 2015 9:39 PM