Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot Figure out how to add an Order By statement to a LINQ query

Posted on 2014-01-29
3
Medium Priority
?
350 Views
Last Modified: 2016-02-10
Hello.
So, I have this Linq query that works well:

Dim listOfFiles = (From sfn In xdoc.Descendants("File") _
                   Where sfn.Attribute("FileName").Value.ToLower().Contains(searchKey) _
                   Select New With _
                   { _
                       .FileName = sfn.Attribute("FileName"), _
                       .FolderAncestors = sfn.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path)_
                  }).ToList()

Open in new window



I cannot figure out how to do Order By FolderAncestors.

The XML File has the following structure (if that help)

<?xml version="1.0" encoding="utf-8"?>
<Folders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" FolderName="myTestFdv" FolderCreatedOn="3/2/2013 1:58:35 AM" FolderId="0f828a3a-b361-480c-982c-db4f92d7bb9b">
  <FolderList>
    <Folder FolderName="12345" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="fe261d0e-7ca8-4a65-bad0-df9eed59bfc0">
      <FolderList>
        <Folder FolderName="images" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="fa9932e1-0ea9-4eef-bd61-4d9e1717fa72">
          <FolderList>
            <Folder FolderName="icons" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="79b240d4-6e49-49e0-a569-be667376acbc">
              <FolderList />
              <FileList>
                <File FileName="accdb.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="584322_accdb.jpg" Size="927" />
                <File FileName="bmp.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="338435_bmp.jpg" Size="276" />
                <File FileName="docx.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="428127_docx.jpg" Size="932" />
                <File FileName="gif.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="995346_gif.jpg" Size="822" />
                <File FileName="jpg.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="40293_jpg.jpg" Size="818" />
                <File FileName="png.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="607512_png.jpg" Size="893" />
                <File FileName="pptx.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="174831_pptx.jpg" Size="927" />
                <File FileName="psd.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="742050_psd.jpg" Size="809" />
                <File FileName="tif.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="786896_tif.jpg" Size="768" />
                <File FileName="txt.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="354216_txt.jpg" Size="506" />
                <File FileName="xlsx.gif" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="921434_xlsx.jpg" Size="949" />
              </FileList>
            </Folder>
            <Folder FolderName="thumbnails" FolderCreatedOn="3/13/2013 12:25:41 AM" FolderId="f19b98a3-f76c-4c10-a6c0-b6fd903c4080">
              <FolderList />
              <FileList>
                <File FileName="570153_Untitled.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="488754_570153_Untitled.jpg" Size="1012" />
                <File FileName="60259_yellow.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="578446_60259_yellow.jpg" Size="1946" />
                <File FileName="884076_Untitled.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="145766_884076_Untitled.jpg" Size="1009" />
                <File FileName="941401_photoshop1.jpg" FileCreatedOn="3/13/2013 12:25:41 AM" FileUpdatedOn="3/13/2013 12:25:41 AM" FileType="image" Thumbnail="712985_941401_photoshop1.jpg" Size="228" />
              </FileList>
            </Folder>
          </FolderList>
          <FileList />
        </Folder>
      </FolderList>
      <FileList />
    </Folder>
    <Folder FolderName="Subfolder1" FolderCreatedOn="3/2/2013 1:58:48 AM" FolderId="7e21b53b-8ff2-45e9-8e11-20dcdd0b9dde">
      <FolderList>
        <Folder FolderName="SubSubFolder1_1" FolderCreatedOn="3/3/2013 9:35:47 PM" FolderId="bf7812df-c98f-4f0e-b348-a2a1eb9d99ef">
          <FolderList />
          <FileList>
            <File FileName="ExcelWorksheet.xlsx" FileCreatedOn="3/3/2013 9:36:01 PM" FileUpdatedOn="3/3/2013 9:36:01 PM" FileType="file" Size="8746" />
          </FileList>
        </Folder>
      </FolderList>
      <FileList />
    </Folder>
    <Folder FolderName="Subfolder2" FolderCreatedOn="3/2/2013 1:58:51 AM" FolderId="015b93c2-9324-46fd-8a0c-8a8a9ecc10a1">
      <FolderList />
      <FileList>
        <File FileName="PPPresentation.pptx" FileCreatedOn="3/2/2013 1:59:19 AM" FileUpdatedOn="3/2/2013 1:59:19 AM" FileType="file" Size="27140" />
        <File FileName="Untitled.jpg" FileCreatedOn="3/7/2013 9:31:59 PM" FileUpdatedOn="3/7/2013 9:31:59 PM" FileType="image" Thumbnail="639073_Untitled.jpg" Size="45956" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder3" FolderCreatedOn="3/2/2013 1:58:53 AM" FolderId="f5f20a02-5c27-4e13-a63f-86173431b12b">
      <FolderList>
        <Folder FolderName="SubSubFolder3_1" FolderCreatedOn="3/2/2013 2:05:30 AM" FolderId="f95411b4-47b8-4261-8a2c-9101dc7a6acf">
          <FolderList />
          <FileList />
        </Folder>
        <Folder FolderName="SubSubFolder3_2" FolderCreatedOn="3/2/2013 2:05:33 AM" FolderId="552f12cd-d3d7-49b6-8a4d-f453ae5efb54">
          <FolderList />
          <FileList>
            <File FileName="New Microsoft Access Database.accdb" FileCreatedOn="3/2/2013 2:05:42 AM" FileUpdatedOn="3/2/2013 2:05:42 AM" FileType="file" Size="495616" />
            <File FileName="New Microsoft Excel Worksheet.xlsx" FileCreatedOn="3/2/2013 2:05:46 AM" FileUpdatedOn="3/2/2013 2:05:46 AM" FileType="file" Size="8746" />
            <File FileName="Untitled.bmp" FileCreatedOn="3/7/2013 9:31:33 PM" FileUpdatedOn="3/7/2013 9:31:34 PM" FileType="image" Thumbnail="87635_Untitled.jpg" Size="2239542" />
            <File FileName="yellow.tif" FileCreatedOn="3/8/2013 7:35:36 PM" FileUpdatedOn="3/8/2013 7:35:39 PM" FileType="image" Thumbnail="392459_yellow.jpg" Size="144078324" />
          </FileList>
        </Folder>
      </FolderList>
      <FileList />
    </Folder>
    <Folder FolderName="Subfolder4" FolderCreatedOn="3/2/2013 1:58:59 AM" FolderId="05e39966-9661-493c-80a2-be7dff925828">
      <FolderList />
      <FileList>
        <File FileName="photoshop1.png" FileCreatedOn="3/7/2013 10:11:05 PM" FileUpdatedOn="3/7/2013 10:11:08 PM" FileType="image" Thumbnail="796074_photoshop1.jpg" Size="4760" />
        <File FileName="photoshop1.psd" FileCreatedOn="3/7/2013 10:07:32 PM" FileUpdatedOn="3/7/2013 10:07:32 PM" FileType="file" Size="47654" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder5" FolderCreatedOn="3/2/2013 1:58:56 AM" FolderId="5d3e48b3-ecb1-4ec1-b52c-e45bc3dd9c59">
      <FolderList />
      <FileList>
        <File FileName="myAccessDatabase.accdb" FileCreatedOn="3/2/2013 1:59:37 AM" FileUpdatedOn="3/2/2013 1:59:37 AM" FileType="file" Size="495616" />
        <File FileName="myTextDocument.txt" FileCreatedOn="3/2/2013 1:59:41 AM" FileUpdatedOn="3/2/2013 1:59:41 AM" FileType="file" Size="0" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder6" FolderCreatedOn="3/5/2013 10:49:08 PM" FolderId="96fd5601-d0ad-4b71-b05a-a9272b440fa3">
      <FolderList />
      <FileList>
        <File FileName="Untitled.png" FileCreatedOn="3/7/2013 9:31:17 PM" FileUpdatedOn="3/7/2013 9:31:17 PM" FileType="image" Thumbnail="379174_Untitled.jpg" Size="33093" />
      </FileList>
    </Folder>
    <Folder FolderName="Subfolder7" FolderCreatedOn="3/5/2013 10:49:19 PM" FolderId="9812d6e3-e535-4ea0-8715-b0f3fc1efaeb">
      <FolderList />
      <FileList />
    </Folder>
  </FolderList>
  <FileList>
    <File FileName="MyRootTextDocument.txt" FileCreatedOn="3/2/2013 1:59:08 AM" FileUpdatedOn="3/2/2013 1:59:08 AM" FileType="file" Size="0" />
    <File FileName="MyRootWordDocument.docx" FileCreatedOn="3/2/2013 1:59:03 AM" FileUpdatedOn="3/2/2013 1:59:03 AM" FileType="file" Size="0" />
  </FileList>
</Folders>

Open in new window







P.S.  Also, how can I dynamically change the order by clause? Or the Where clause for that matter.
0
Comment
Question by:vituxa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 2000 total points
ID: 39819541
To sort just add a call to .OrderBy() before you do the ToList().

        Dim listOfFiles = (From sfn In xdoc.Descendants("File") _
                   Where sfn.Attribute("FileName").Value.ToLower().Contains(searchKey) _
                   Select New With _
                   { _
                       .FileName = sfn.Attribute("FileName"), _
                       .FolderAncestors = sfn.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path) _
                  }).OrderBy(Function(sfn) sfn.FileName).ToList()

Open in new window


If you want to supply the Where and Order criteria at run time you'd be better off to switch to the lambda syntax. Then you can pass in the lambda's as run-time parameters.

' With hard-coded Where() and OrderBy()

        Dim listOfFiles = xdoc.Descendants("File") _
            .Where(Function(sfn) sfn.Attribute("FileName").Value.ToLower().Contains(searchKey)) _
            .Select(Function(sfn) New With _
            { _
                .FileName = sfn.Attribute("FileName"), _
                .FolderAncestors = sfn.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path) _
            }) _
            .OrderBy(Function(sfn) sfn.FileName) _
            .ToList()


' With run-time provided Where() and OrderBy()

        Dim listOfFiles = xdoc.Descendants("File") _
            .Where(myWhereCriteria) _
            .Select(Function(sfn) New With _
            { _
                .FileName = sfn.Attribute("FileName"), _
                .FolderAncestors = sfn.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path) _
            }) _
            .OrderBy(myOrderByCriteria) _
            .ToList()

Open in new window

0
 
LVL 1

Author Comment

by:vituxa
ID: 39819754
I am sorry, can you please give me an example of what myOrderByCriteria and myWhereCriteria would be, and what would it look like if there are multiple where and order by criterias?
0
 
LVL 1

Author Closing Comment

by:vituxa
ID: 39819755
Thank you so much, can you please answer my question above?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question