locked
Linq to XML and Dynamic Order By RRS feed

  • Question

  • Hi,

    Our client has asked us to Order a set of XML elements by sending us the Order specification in the XML.

    e.g.

    <SortOrder1>Country</SortOrder1>

    <SortOrder1FirstChar>1</SortOrder1FirstChar>

    <SortOrder1FirstChar>5</SortOrder1FirstChar>

    In this case, we need to Order By the first 5 characters of the Element whose name is Country

    e.g.

    <Data>

    <Country>UK</Country>

    </Data><

    <Data>

    <Country>Australia</Country>

    </Data>

    There may be other sort criteria,

    <SortOrder2>Reference</SortOrder2>

    <SortOrder2FirstChar>3</SortOrder2FirstChar>

    <SortOrder2FirstChar>8</SortOrder2FirstChar>

    My question is, can this type of dynamic Order By clause be generated and if so, how can this be done?

    Any help would be gratefully received and appreciated

    Mike

    Wednesday, February 2, 2011 12:09 AM

Answers

  • Hi Mike,

    According to my understanding of the problem, I wrote some sample codes for your references.

    Here are some key points in my sample codes.  
    1)  I used LINQ extension method Select(Func<T, int, TResult>) to parse the Sort XML because the Sort element can be different like SortOrder1, SortOrder2 and etc... 
    http://msdn.microsoft.com/en-us/library/bb534869.aspx

    2)  In LINQ query, we first use OrderBy extension method to order a collection.  If then we need to order collection by another filter, we use ThenBy. 
    http://msdn.microsoft.com/en-us/library/system.linq.enumerable.thenby.aspx

    3)  We need to save the temp SortBy object because of the deferred execution feature of LINQ IEnumerable<> query.  For detail, please see
    http://msdn.microsoft.com/en-us/library/bb943859.aspx

    =======================================================================
            private List<XElement> DynamicOrderBy()
            {
                XDocument sortDoc = XDocument.Load("Sort.xml");
                List<SortBy> sorts = sortDoc.Root.Elements("Sort").Select((sort, index) =>
                                      new SortBy
                                      {
                                          SortElement = sort.Element("SortOrder" + (index + 1).ToString()).Value,
                                          SortStartCharPos = int.Parse(sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").Value),
                                          SortEndCharPos = int.Parse(((XElement)sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").NextNode).Value)
                                      }).ToList();

                XDocument dataDoc = XDocument.Load("Data.xml");
                IOrderedEnumerable<XElement> result = null;

                if (sorts.Count >= 1)
                {
                    result = dataDoc.Root.Elements("Data").OrderBy(data => GetSortValue(data.Element(sorts[0].SortElement).Value, sorts[0].SortStartCharPos, sorts[0].SortEndCharPos));
                }
                else
                    return null;

                for (int i = 1; i < sorts.Count; i++)
                {
                    SortBy tempSortBy = sorts[i];
                    result = result.ThenBy(data => GetSortValue(data.Element(tempSortBy.SortElement).Value, tempSortBy.SortStartCharPos, tempSortBy.SortEndCharPos));
                }

                return result.ToList();
            }

            // Retrieve the value based on the start position and end position
            private string GetSortValue(string value, int startPos, int endPos)
            {
                if (startPos > endPos || startPos < 1)
                    throw new Exception("Invalid value!");

                if (startPos > value.Length)
                {
                    return value;
                }
                else if (endPos > value.Length)
                {
                    return value.Substring(startPos - 1);
                }
                else
                {
                    return value.Substring(startPos - 1, endPos - startPos + 1);
                }
            }

        // Helper class to hold the sort by information
        class SortBy
        {
            public string SortElement { get; set; }
            public int SortStartCharPos { get; set; }
            public int SortEndCharPos { get; set; }
        }
    =================================================================

    The sample XMLs I am testing are here:

    =================================================================
    Sort XML:

    <?xml version="1.0" encoding="utf-8" ?>
    <Sorts>
      <Sort>
        <SortOrder1>Country</SortOrder1>
        <SortOrder1FirstChar>1</SortOrder1FirstChar>
        <SortOrder1FirstChar>5</SortOrder1FirstChar>
      </Sort>
      <Sort>
        <SortOrder2>Reference</SortOrder2>
        <SortOrder2FirstChar>3</SortOrder2FirstChar>
        <SortOrder2FirstChar>8</SortOrder2FirstChar>
      </Sort>
    </Sorts>


    Data XML:

    <?xml version="1.0" encoding="utf-8" ?>
    <Source>
      <Data>
        <Country>UK</Country>
        <Reference>0123456789</Reference>
      </Data>
      <Data>
        <Country>Australia</Country>
        <Reference>9876543210</Reference>
      </Data>
      <Data>
        <Country>Australia</Country>
        <Reference>0123456789</Reference>
      </Data>
    </Source>
    =================================================================

    If you have any questions, please feel free to let me know.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 2, 2011 7:38 AM
    Moderator
  • Hi Mike,

    You're welcome!   I like creating dynamic LINQ queries, but to make the the query easy to use, we need to spend lots of time on creating/modifying our own dynamic LINQ library.  :)  

    First, we don't need to use Extension Methods all the time.  However, the Select(Func<T, int, TResult>) method, I believe no corresponding query expression can be used.  

    After several hours research, I figure out a workaround for your references.   Based on my understanding, the dynamic GroupBy query here needs to be performed on multiple XML element values like "group XElement by new { Value1, Value2, ... }", right?   If so, I think we can use the Dynamic LINQ Library here, http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx.   Since it's orginally for LINQ to SQL or EF IQueryable queries, to use it in LINQ to XML queries, we need to first call ToQueryable() method.  

    The following sample codes first generates the GroupBy/SortBy data.  Then it will generate the GroupBy pattern which is used in the Dynamic GroupBy method. 
    ================================================================
                XDocument sortDoc = XDocument.Load("Sort.txt");
                List<SortBy> sorts = sortDoc.Root.Elements("Sort").Select((sort, index) =>
                                      new SortBy
                                      {
                                          SortElement = sort.Element("SortOrder" + (index + 1).ToString()).Value,
                                          SortStartCharPos = int.Parse(sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").Value),
                                          SortEndCharPos = int.Parse(((XElement)sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").NextNode).Value)
                                      }).ToList();

                XDocument dataDoc = XDocument.Load("Data.txt");
                foreach (var data in dataDoc.Root.Elements("Data"))
                {
                    foreach (var sort in sorts)
                    {
                        data.Add(new XElement("Group" + sort.SortElement,
                            GetSortValue(data.Element(sort.SortElement).Value, sort.SortStartCharPos, sort.SortEndCharPos)));
                    }
                }

                var array = sorts.Select(s => string.Format("it.Element(XName.Get(\"Group{0}\")).Value as {0}", s.SortElement)).ToArray();
                string groupByPattern = "new (";
                if (array.Length == 1)
                {
                    groupByPattern = groupByPattern + array[0] + ")";
                }
                else if (array.Length > 1)
                {
                    for (int i = 0; i < array.Length - 1; i++)
                    {
                        groupByPattern = groupByPattern + array[0] + ", ";
                    }

                    groupByPattern = groupByPattern + array[array.Length - 1] + ")";
                }
                var list = dataDoc.Root.Elements("Data").AsQueryable().GroupBy(groupByPattern, "it");
    ================================================================

    NOTE:
    1)  Please download the Dynamic LINQ Library source codes and copy the Dynamic.cs file to your project.

    2)  Please add these two lines of codes into Line 567 of the Dynamic.cs file to extend the predefinedTypes for the library.  Also, add the namespace "System.Xml.Linq", please.
    ================================================================
                typeof(XName),
                typeof(XContainer)
    ================================================================

    3) The final result of the query may not be what you are requesting, but I believe it will be easy to convert them later.

     

    Besides, if you want to GroupBy first element(Country) and then the other elements like (Reference), you can refer to the dynamic GroupMany method here:
    http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx
    http://blogs.msdn.com/b/mitsu/archive/2007/12/22/playing-with-linq-grouping-groupbymany.aspx

    Please feel free to let me know if you need any further assitance. 

    Have a nice day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 7, 2011 4:01 AM
    Moderator

All replies

  • Hi Mike,

    According to my understanding of the problem, I wrote some sample codes for your references.

    Here are some key points in my sample codes.  
    1)  I used LINQ extension method Select(Func<T, int, TResult>) to parse the Sort XML because the Sort element can be different like SortOrder1, SortOrder2 and etc... 
    http://msdn.microsoft.com/en-us/library/bb534869.aspx

    2)  In LINQ query, we first use OrderBy extension method to order a collection.  If then we need to order collection by another filter, we use ThenBy. 
    http://msdn.microsoft.com/en-us/library/system.linq.enumerable.thenby.aspx

    3)  We need to save the temp SortBy object because of the deferred execution feature of LINQ IEnumerable<> query.  For detail, please see
    http://msdn.microsoft.com/en-us/library/bb943859.aspx

    =======================================================================
            private List<XElement> DynamicOrderBy()
            {
                XDocument sortDoc = XDocument.Load("Sort.xml");
                List<SortBy> sorts = sortDoc.Root.Elements("Sort").Select((sort, index) =>
                                      new SortBy
                                      {
                                          SortElement = sort.Element("SortOrder" + (index + 1).ToString()).Value,
                                          SortStartCharPos = int.Parse(sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").Value),
                                          SortEndCharPos = int.Parse(((XElement)sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").NextNode).Value)
                                      }).ToList();

                XDocument dataDoc = XDocument.Load("Data.xml");
                IOrderedEnumerable<XElement> result = null;

                if (sorts.Count >= 1)
                {
                    result = dataDoc.Root.Elements("Data").OrderBy(data => GetSortValue(data.Element(sorts[0].SortElement).Value, sorts[0].SortStartCharPos, sorts[0].SortEndCharPos));
                }
                else
                    return null;

                for (int i = 1; i < sorts.Count; i++)
                {
                    SortBy tempSortBy = sorts[i];
                    result = result.ThenBy(data => GetSortValue(data.Element(tempSortBy.SortElement).Value, tempSortBy.SortStartCharPos, tempSortBy.SortEndCharPos));
                }

                return result.ToList();
            }

            // Retrieve the value based on the start position and end position
            private string GetSortValue(string value, int startPos, int endPos)
            {
                if (startPos > endPos || startPos < 1)
                    throw new Exception("Invalid value!");

                if (startPos > value.Length)
                {
                    return value;
                }
                else if (endPos > value.Length)
                {
                    return value.Substring(startPos - 1);
                }
                else
                {
                    return value.Substring(startPos - 1, endPos - startPos + 1);
                }
            }

        // Helper class to hold the sort by information
        class SortBy
        {
            public string SortElement { get; set; }
            public int SortStartCharPos { get; set; }
            public int SortEndCharPos { get; set; }
        }
    =================================================================

    The sample XMLs I am testing are here:

    =================================================================
    Sort XML:

    <?xml version="1.0" encoding="utf-8" ?>
    <Sorts>
      <Sort>
        <SortOrder1>Country</SortOrder1>
        <SortOrder1FirstChar>1</SortOrder1FirstChar>
        <SortOrder1FirstChar>5</SortOrder1FirstChar>
      </Sort>
      <Sort>
        <SortOrder2>Reference</SortOrder2>
        <SortOrder2FirstChar>3</SortOrder2FirstChar>
        <SortOrder2FirstChar>8</SortOrder2FirstChar>
      </Sort>
    </Sorts>


    Data XML:

    <?xml version="1.0" encoding="utf-8" ?>
    <Source>
      <Data>
        <Country>UK</Country>
        <Reference>0123456789</Reference>
      </Data>
      <Data>
        <Country>Australia</Country>
        <Reference>9876543210</Reference>
      </Data>
      <Data>
        <Country>Australia</Country>
        <Reference>0123456789</Reference>
      </Data>
    </Source>
    =================================================================

    If you have any questions, please feel free to let me know.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 2, 2011 7:38 AM
    Moderator
  • Hi Mike,

    I am writing to check the status of the issue.  If you need any further assistance, please feel free to let me know.

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 4, 2011 6:52 AM
    Moderator
  • Hi Michael,

    This is great - Much appreciated!

    Just to check, for this to work you have to construct your queries using Method Syntax and not query expressions?

    One further question - They have also asked us to do the same thing with GroupBy i.e. GroupBy(XElement("Test").Value.SubString etc etc but the twist is I need to end up with multiple levels in the Group By outcome i.e. From the example above, end up with something like

    <Group groupedBy="Country" groupValue="Engla"> --> Here its grouped by the first 5 characters 

    <Data ...records for this group

    However if there are two groupby's defined, you would end up with 

    <Group groupedBy="Country" groupValue="Engla"> --> Here its grouped by the first 5 characters 

    <Group groupedBy="Reference" groupValue="2345678"> --> Here its grouped by characters  3 - 8

    <Data ...records for this group

     

    Thanks again  - This has so far been really helpful!

    Have a good weekend as well

    Mike

    Friday, February 4, 2011 11:57 AM
  • Hi Mike,

    You're welcome!   I like creating dynamic LINQ queries, but to make the the query easy to use, we need to spend lots of time on creating/modifying our own dynamic LINQ library.  :)  

    First, we don't need to use Extension Methods all the time.  However, the Select(Func<T, int, TResult>) method, I believe no corresponding query expression can be used.  

    After several hours research, I figure out a workaround for your references.   Based on my understanding, the dynamic GroupBy query here needs to be performed on multiple XML element values like "group XElement by new { Value1, Value2, ... }", right?   If so, I think we can use the Dynamic LINQ Library here, http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx.   Since it's orginally for LINQ to SQL or EF IQueryable queries, to use it in LINQ to XML queries, we need to first call ToQueryable() method.  

    The following sample codes first generates the GroupBy/SortBy data.  Then it will generate the GroupBy pattern which is used in the Dynamic GroupBy method. 
    ================================================================
                XDocument sortDoc = XDocument.Load("Sort.txt");
                List<SortBy> sorts = sortDoc.Root.Elements("Sort").Select((sort, index) =>
                                      new SortBy
                                      {
                                          SortElement = sort.Element("SortOrder" + (index + 1).ToString()).Value,
                                          SortStartCharPos = int.Parse(sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").Value),
                                          SortEndCharPos = int.Parse(((XElement)sort.Element("SortOrder" + (index + 1).ToString() + "FirstChar").NextNode).Value)
                                      }).ToList();

                XDocument dataDoc = XDocument.Load("Data.txt");
                foreach (var data in dataDoc.Root.Elements("Data"))
                {
                    foreach (var sort in sorts)
                    {
                        data.Add(new XElement("Group" + sort.SortElement,
                            GetSortValue(data.Element(sort.SortElement).Value, sort.SortStartCharPos, sort.SortEndCharPos)));
                    }
                }

                var array = sorts.Select(s => string.Format("it.Element(XName.Get(\"Group{0}\")).Value as {0}", s.SortElement)).ToArray();
                string groupByPattern = "new (";
                if (array.Length == 1)
                {
                    groupByPattern = groupByPattern + array[0] + ")";
                }
                else if (array.Length > 1)
                {
                    for (int i = 0; i < array.Length - 1; i++)
                    {
                        groupByPattern = groupByPattern + array[0] + ", ";
                    }

                    groupByPattern = groupByPattern + array[array.Length - 1] + ")";
                }
                var list = dataDoc.Root.Elements("Data").AsQueryable().GroupBy(groupByPattern, "it");
    ================================================================

    NOTE:
    1)  Please download the Dynamic LINQ Library source codes and copy the Dynamic.cs file to your project.

    2)  Please add these two lines of codes into Line 567 of the Dynamic.cs file to extend the predefinedTypes for the library.  Also, add the namespace "System.Xml.Linq", please.
    ================================================================
                typeof(XName),
                typeof(XContainer)
    ================================================================

    3) The final result of the query may not be what you are requesting, but I believe it will be easy to convert them later.

     

    Besides, if you want to GroupBy first element(Country) and then the other elements like (Reference), you can refer to the dynamic GroupMany method here:
    http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx
    http://blogs.msdn.com/b/mitsu/archive/2007/12/22/playing-with-linq-grouping-groupbymany.aspx

    Please feel free to let me know if you need any further assitance. 

    Have a nice day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 7, 2011 4:01 AM
    Moderator
  • Hi Mike,

    Would you mind letting us know how is the problem now? 

    Please feel free to tell me if you have any questions.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 9, 2011 1:09 AM
    Moderator
  • Hi Michael,

     

    Yes, all working now so thanks for your help.

    I've just logged a question regarding XML streaming if you can help!

    Many thanks

    Mike

    Friday, May 6, 2011 2:00 PM
  • Hi Mike,

    Sure.   But I think Martin has provided a great response, http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/thread/de9ec89f-2800-49f5-8186-a5602a9be06e/.  :)

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, May 6, 2011 3:53 PM
    Moderator