?
Solved

Dynamically insert Where and OrderBy clauses in Linq. How?

Posted on 2014-01-30
9
Medium Priority
?
440 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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.
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…
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…
Suggested Courses

752 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