Solved

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

Posted on 2014-01-29
3
305 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
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you so much, can you please answer my question above?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now