Solved

Dynamically insert Where and OrderBy clauses in Linq. How?

Posted on 2014-01-30
9
429 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 62

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 62

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 62

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 62

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Moving code from one TFS branch to another 3 25
Footer for each row on Gridview 2 21
VB.net and sql server 4 35
Send SMS from VB via Sierra Wireless Modem 2 21
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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:…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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