I've got a query that on the face of it should be pretty simple, but I'm struggling.
I'm taking user input from a textbox, now if there's something in the textbox, that's fine, I can query against that, but what if there is nothing in there.
I'd like to be able to say if blank or NULL.
The logic I'm thinking of is:
if textbox is not blank then use contents of textbox
else search for blank or NULL
I'm not quite sure how to implement this in a linq to sql structure of code.
I could simply have a different linq to sql code section depending on what the user has typed in, but I'm sure there must be a more sensible way, as the larger a form gets the more difficult it will be to achieve.
Thank you for your reply, but I don't quite understand what you've done there.
Perhaps it would help if I showed you the query I currently have:
Dim locationQuery = From lq In myDataContext.userCreatedLocations
Where lq.locationName = txtLocationName.Text
txtLocationName.Text is the textbox which will contain the user input.
But I will have several fields which will have the same issue, i.e. where I will want to return a different result depending on whether the field is blank (which would exclude that field from the query) or has content in (Which would use that field in an
AND query.
That simply checks if text is empty, if empty null will be returned from If function. You can create a extension method for doing that validation and use it for all other fields as well.
Imports System.Runtime.CompilerServices
Module ControlsHelper
<Extension()> _
Public Function GetValue(ByVal str As String) As String
Return If(String.IsNullOrEmpty(str.Trim()), Nothing, "")
End Function
End Module
And you can use it like this
Dim locationQuery = From lq In myDataContext.userCreatedLocations _
Where lq.locationName = txtLocationName.Text.GetValue()
Perhaps I'm misunderstanding, but I don't think that would work.
I need to construct the query in such a way that if a field is left blank, then it's excluded from the query.
For example:
if txtlocationname.text = "bob" then the query would be as follows
Dim locationQuery = From lq In myDataContext.userCreatedLocations _
Where lq.locationName = txtLocationName.Text
However, if txtlocationname.text = "" then the query should be as follows:
dim locName = if txtlocationname.text ="" or txtlocationname.text = " " then
locname=""
else
locname= txtlocationname.text
endif
dim locNameLinq = if locname="" then
else
locNameLinq = "lq.Locationname = txtlocationname.text"
endif
This way I can somehow programatically construct the linq statement, so if a field is to be excluded from the linq query, it will be excluded, and if not, it will be included.
This must be a common question and if someone types nothing into a search box, they will expect everything back, not nothing.
Yes that would work, but it would mean I would have to perform 1 query per textbox that I'm checking.
So if I've got 10 text boxes I'll be creating and running 10 queries rather than 1, it would work, but there must be a tidier solution?
***Edit***
I've just had a thought, I don't think it would work really as I'd be joining results rather than getting 1 result.
For example,what if in 1 query I say "where name ='john'", and in another query I say "where age =50", 1 result will give me only johns, but the other result will give me people who are aged 50, rather than showing me only records that contain john AND aged
50, I'll just be sticking the 2 unrelated results together.
erm, I'm having trouble wrapping my brain around that one.
That would work if there way content in the textbox, but I don't think it would give the desired end result if no content is in the textbox.
If I'm understanding your above code correctly, it seems as though my linq query would already have been established, which would exlude many records as I'll be searching for where the fields = "" (If there is no content), rather than simply excluding that
field from the linq query.
For example, if there is nothing in the textbox for the name, then I don't want it to even be a part of my linq query.
Is some kind of concatenation of a query possible, as per my previous post?
stevex33
Member
356 Points
337 Posts
if statements within Linq query?
Oct 05, 2010 08:59 PM|LINK
Hi Guys,
I've got a query that on the face of it should be pretty simple, but I'm struggling.
I'm taking user input from a textbox, now if there's something in the textbox, that's fine, I can query against that, but what if there is nothing in there.
I'd like to be able to say if blank or NULL.
The logic I'm thinking of is:
if textbox is not blank then use contents of textbox
else search for blank or NULL
I'm not quite sure how to implement this in a linq to sql structure of code.
I could simply have a different linq to sql code section depending on what the user has typed in, but I'm sure there must be a more sensible way, as the larger a form gets the more difficult it will be to achieve.
Any ideas are welcome.
sansan
All-Star
53942 Points
8147 Posts
Re: if statements within Linq query?
Oct 05, 2010 11:09 PM|LINK
You can do it this way
VB:
Dim result = records.Where(Function(a) _ a.MyColumn = If(String.IsNullOrEmpty(TextBox1.Text), Nothing, TextBox1.Text))C#:
var result = records.Where(a => a.MyColumn == (string.IsNullOrEmpty(TextBox1.Text) ? null : TextBox1.Text));stevex33
Member
356 Points
337 Posts
Re: if statements within Linq query?
Oct 06, 2010 06:22 PM|LINK
Thank you for your reply, but I don't quite understand what you've done there.
Perhaps it would help if I showed you the query I currently have:
Dim locationQuery = From lq In myDataContext.userCreatedLocations Where lq.locationName = txtLocationName.TexttxtLocationName.Text is the textbox which will contain the user input.
But I will have several fields which will have the same issue, i.e. where I will want to return a different result depending on whether the field is blank (which would exclude that field from the query) or has content in (Which would use that field in an AND query.
sansan
All-Star
53942 Points
8147 Posts
Re: if statements within Linq query?
Oct 06, 2010 06:38 PM|LINK
That simply checks if text is empty, if empty null will be returned from If function. You can create a extension method for doing that validation and use it for all other fields as well.
Imports System.Runtime.CompilerServices Module ControlsHelper <Extension()> _ Public Function GetValue(ByVal str As String) As String Return If(String.IsNullOrEmpty(str.Trim()), Nothing, "") End Function End ModuleAnd you can use it like this
Dim locationQuery = From lq In myDataContext.userCreatedLocations _ Where lq.locationName = txtLocationName.Text.GetValue()stevex33
Member
356 Points
337 Posts
Re: if statements within Linq query?
Oct 06, 2010 07:26 PM|LINK
Perhaps I'm misunderstanding, but I don't think that would work.
I need to construct the query in such a way that if a field is left blank, then it's excluded from the query.
For example:
if txtlocationname.text = "bob" then the query would be as follows
Dim locationQuery = From lq In myDataContext.userCreatedLocations _ Where lq.locationName = txtLocationName.TextHowever, if txtlocationname.text = "" then the query should be as follows:
This way I can somehow programatically construct the linq statement, so if a field is to be excluded from the linq query, it will be excluded, and if not, it will be included.
This must be a common question and if someone types nothing into a search box, they will expect everything back, not nothing.
what do you think?
medelbrock
Member
637 Points
138 Posts
Re: if statements within Linq query?
Oct 06, 2010 09:02 PM|LINK
Why not have an 'if' statement that runs the first query and the 'else' statement runs the second. You don't have to run it all at once.
stevex33
Member
356 Points
337 Posts
Re: if statements within Linq query?
Oct 06, 2010 09:22 PM|LINK
Yes that would work, but it would mean I would have to perform 1 query per textbox that I'm checking.
So if I've got 10 text boxes I'll be creating and running 10 queries rather than 1, it would work, but there must be a tidier solution?
***Edit***
I've just had a thought, I don't think it would work really as I'd be joining results rather than getting 1 result.
For example,what if in 1 query I say "where name ='john'", and in another query I say "where age =50", 1 result will give me only johns, but the other result will give me people who are aged 50, rather than showing me only records that contain john AND aged 50, I'll just be sticking the 2 unrelated results together.
medelbrock
Member
637 Points
138 Posts
Re: if statements within Linq query?
Oct 06, 2010 09:48 PM|LINK
You could do post-filtering
List<People> list = Where(x=>x.Name.Equals("John")).ToList();
-then-
list.Where(x=>x.Age.Equals(50));
That sort of thing. LINQ is an IQueryable but can easily be cast into IEnumerables.
Edit: I completely spaced out, you could just make it
IQueryable people = Where(x=>x.Name.Equals("John"))
without casting it.
stevex33
Member
356 Points
337 Posts
Re: if statements within Linq query?
Oct 07, 2010 07:51 PM|LINK
erm, I'm having trouble wrapping my brain around that one.
That would work if there way content in the textbox, but I don't think it would give the desired end result if no content is in the textbox.
If I'm understanding your above code correctly, it seems as though my linq query would already have been established, which would exlude many records as I'll be searching for where the fields = "" (If there is no content), rather than simply excluding that field from the linq query.
For example, if there is nothing in the textbox for the name, then I don't want it to even be a part of my linq query.
Is some kind of concatenation of a query possible, as per my previous post?
sansan
All-Star
53942 Points
8147 Posts
Re: if statements within Linq query?
Oct 07, 2010 07:56 PM|LINK
You can check this link for building dynamic query
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
</div>