[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Dynamically insert Where and OrderBy clauses in Linq. How?

Posted on 2014-01-30
9
Medium Priority
?
441 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
[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
  • 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 64

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

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 64

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

656 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