Solved

Dynamically insert Where and OrderBy clauses in Linq. How?

Posted on 2014-01-30
9
437 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 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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