Solved

Dynamically insert Where and OrderBy clauses in Linq. How?

Posted on 2014-01-30
9
430 Views
Last Modified: 2016-02-10
Hello.
I have this Lambda ExpressionSelect Code that works very well.
Dim listOfFiles = xdoc.Descendants("File") _
            .Select(Function(sfn) New With _
            { _
                .FileExtension = sfn.Attribute("Ext"), _
                .FileName = sfn.Attribute("FileName"), _
                .FileSize = sfn.Attribute("Size"), _
                .FolderAncestors = sfn.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path) _
            }).ToList()

Open in new window


What if, depending on User's selection I would have to add a Where statement? For example Where FileName Like '%blue%'
Or more than one Where Statement, for example: Where FileName Like '%blue%' And FileExtension = '.doc'
How can I pass in such parameters and dynamically create such statement, no matter how many parameters?

Same thing with Order by - this time a User wants to Order By FileName Asc, and the other time by FolderAncestors Desc, FileExtension Asc.

Please help with examples.
0
Comment
Question by:vituxa
  • 4
  • 4
9 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39821097
0
 
LVL 1

Author Comment

by:vituxa
ID: 39821245
Can you please take my example and add a dynamic where and orderby to it?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39821468
Hi vituxa;

In your original XML file you do not have a "Ext" Attribute in the File node. Have you changed that so that File nodes do have Ext Attribute? If so can you please post new XML document so I can test the query.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 1

Author Comment

by:vituxa
ID: 39821742
Oh please don't worry about the ext.  If you can give me the example of any other attribute - size, FileCreatedOn... Anything really.

Thank you Prodigy.
0
 
LVL 1

Author Comment

by:vituxa
ID: 39821748
And is there a sign like in SQL you have a % sign, so I can query something like 'mico%sf' where % is being whatever is in the middle
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39821830
Hi vituxa;

To be able to do what you want we need to add a class which matches the values being returned by the select statement, in the below code I call this class XMLFileData. We also need to build the query in stages only adding the Where clause's when needed.

Dim xdoc As XDocument = XDocument.Load("...\db.xml")

' Variable to add Where clause's to the base query
Dim searchFileType = "image"
Dim searchFileName = "txt"

' Starts the process of building the query
Dim listOfFilesBase = xdoc.Descendants("File")

' If we need to search for a file name then this Where clause gets added
If Not String.IsNullOrEmpty(searchFileName) Then
    listOfFilesBase = listOfFilesBase.Where(Function(fn) fn.Attribute("FileName").Value.Contains(searchFileName))
End If

' If we need to search for a file type then this Where clause gets added
If Not String.IsNullOrEmpty(searchFileType) Then
    listOfFilesBase = listOfFilesBase.Where(Function(fe) fe.Attribute("FileType").Value.Contains(searchFileType))
End If

' Now that all the Where clause's have been added complete by adding the select clause
Dim listOfFiles = listOfFilesBase.Select(Function(d) New XMLFileData With _
{ _
    .FileType = d.Attribute("FileType"), _
    .FileName = d.Attribute("FileName"), _
    .FileSize = d.Attribute("Size"), _
    .FolderAncestors = d.Ancestors("Folder").Aggregate("", Function(path As String, current As XElement) current.Attribute("FolderName").Value & "\" & path) _
}).ToList()

' Class to build the result set
Public Class XMLFileData
    Public FileType As XAttribute
    Public FileName As XAttribute
    Public FileSize As XAttribute
    Public FolderAncestors As String
End Class

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39821859
To your question, "And is there a sign like in SQL you have a % sign, so I can query something like 'mico%sf' where % is being whatever is in the middle", Well you do not have a sign but the Contains method translate to a SQL %. This can match in the beginning, middle or end. You can also use the methods StartsWith and EndsWith to match the beginning and the end of a string.
0
 
LVL 1

Author Closing Comment

by:vituxa
ID: 39821938
Thank you Prodigy!!! Once again saved me!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39821958
Not a problem  vituxa, glad to be of help.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

792 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