IIF in where clause of a Linq query

BTW: to make this kind of queries more readable, it is common practice to the whole boolean logic part (the part in the where statement) inside a function:

Function CheckValues(val1 As String, val2 As String) As Boolean
  ' do your thing here
End Function

and then, the where part becomes:

where CheckValues(value1, value2)

etc.

abel, I'll check your example this morning but had a question on your second statement. is the following what you're talking about.
private sub run()
     dim vw1 = from vw in table1 _
     where CheckValues(value1, value2)  _
     select vw.value1
end sub

Function CheckValues(val1 As String, val2 As String) As Boolean
   
End Function

I'm not following because the function would return true or false so how does this translate to the string for the where clause like where str = 'ABC'

I'm running my code to see if example works but not sure I explain the issue correct. for the second value I have this:    (value2 is nothing or value2 = strvalue2)  but what I need is something like this:

strvalue2 = Change.SelectedValue

dim vw1 = from vw in table1 _
where value1 = strvalue1 and  (IIF(strvalue2 is nothing, value2 is nothing, value2 = strvalue2))  _
select vw.value1
if the strvalue2 is nothing, I can't use value2= strvalue2. It has to be value2 is nothing. as far as I know.
But I can't get the IIF to work and not sure of another way. The example you gave is working the same as with what I have.   Just to make sure I explain this right, what I'm trying to do is determine what strvalue2 is.  strvalue2 can either be a number or it can be null. The null value is the problem. if i breakdown my IIF statement it would be like this.
If strvalue2 is nothing then value2 is nothing
else value2 = strvalue2

>  so how does this translate to the string for the where clause like where str = 'ABC'

that part goes in the function. The where clause must return true or false anyway. You are doing some logic on two values, which is why I suggested to use a function.

The statement you gave was exactly what I meant (apart from that the function CheckValues must do something and that it seems that you didn't yet give the correct parameters to the function).

I like to help, but the statement you have been posting here was not a correct or working LINQ statement, so I figured you made it up for the sake of explaining your situation. But now I think you have some trouble getting the statement correct. It should be something like this (i.e., you must do something with the object "vw" that is different in each iteration, just like in a normal SQL statement). Something like this (just an example), which tests for nothingness and for either "hello" or "world", if either is in the item, it goes into vw1. The loop at the end with print "hello" and "world".

If you'd follow up with a descriptive example of what's in your table1 (the type of object) and how you want to filter it, I can help you setting up the where statement, without the use of IIF (which should be avoided almost always).

-- Abel --

Dim table1 As String() = New String() {"ttt", "", "hello", Nothing, "test", "world"}
Dim strValue1 As String = "hello"
Dim strValue2 As String = "world"
Dim vw1 = From vw In table1 _
          Where vw IsNot Nothing AndAlso _
          (vw = strValue1 OrElse vw = strValue2) _
          Select vw
 
For Each s In vw1
    Debug.WriteLine(s)
Next
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

I had a feeling I was explaining this wrong.  What you mention is your last post is what I'm doing but its not what I want to do. I'm trying to change it. If I break it into to two queries, maybe it will be more clear.
dim str1 as string = cmbobox1.text
if str1 = "" then str1 is nothing

if str1 is nothing then
   dim Results = from vw In table1 _
                         Where vw.value1 = str2 and vw.value2 is nothing
                          Select vw.value1, vw.value2
else
   dim Results = from vw In table1 _
                         Where vw.value1 = str2 and vw.value2 = str1
                          Select vw.value1, vw.value2
end if  

the only difference in the two queries is if str1 is nothing I can't use = str1 because I get no results. the value in the table is null in these cases. So what I'm trying to do is combine the two queries into one but only want the result based on the one condition not both. Hope this makes more sense.

As a result you want both result sets, right?

So, just to see if I get this straight, for an item to be included, it must have:

-  value1 equal to str2 AND
-  value2 equal to nothing or equal to str1

Put in another way: value2 can either be nothing and if it isn't nothing, it must equal str2. In LINQ this looks like this:

Dim Results = From vw In table1 _
              Where vw.value1 = str2 
              AndAlso (vw.value2 Is Nothing OrElse vw.value2 = str2)
              Select vw.value1, vw.value2

                                              

1:
2:
3:
4:

Sorry abel, never got an email about a response.  not sure this is correct "value2 can either be nothing and if it isn't nothing, it must equal str2".   str2 is either nothing or equals something. if str2 is nothing, then I only want the records that have value2 is nothing. if str2 has a value than I only want records that equal that value. So if str2 has a value, I don't want to see the nothing records.

Sorry, you mean to say that the input string, the one you test for, should be tested for nothingness? I don't mind, but that's the reverse of what you wrote so far. Let's make a little table, because I think we're getting very confused by trying to put boolean logic into english words.

To make it clear what we talk about, I call the data in the list datavalue1 and datavalue2 and I call the query strings search1 and search2 (what you and I have called str1 and str2 in the last comments).

// search1 contains a value
search2 is nothing, return if datavalue2 is nothing and datavalue1 is search1
search2 contains value, return if datavalue2 equals search2 and datavalue1 equals search1
 
// search1 contains nothing
search2 is nothing, return if datavalue2 is nothing and datavalue1 is nothing
search2 contains value, return if datavalue2 is search2 and datavalue1 is nothing

                                              

1:
2:
3:
4:
5:
6:
7:

In your penultimate comment, you showed a snippet where it seemed that you accidentally mixed the value1 == str2, where it should've been value1 == str1. I did the same in the follow up comment, but in your original statement there's nothing of that sort.

To summarize the above and if we just consider Nothing another value, then we just have to return everything that equals both values. If that's the case, it looks like this:

Dim Results = From vw In table1 _
    Where 
    ((str1 Is Nothing AndAlso vw.value1 Is Nothing)
    OrElse (str1 IsNot Nothing AndAlso vw.value1 = str1))
    AndAlso
    ((str2 Is Nothing AndAlso vw.value2 Is Nothing)
    OrElse (str2 IsNot Nothing AndAlso vw.value2 = str2))
    Select vw.value1, vw.value2

                                              

1:
2:
3:
4:
5:
6:
7:
8:

I knew as these post where continuing everything was getting mixed up. sorry about that.  To try to make it more clear, lets forget about what you're calling SearchName. That part is just a simple part that I have no issue with. in reality, I have other parts to my where clause. I just cant get the syntax write for the searchCity. I'm going to look more closely at your last example and try it. I think this example will work just reading it. At least for the part I'm having an issue with. I was working on creating two strings for the query and calling the one I need but I need to create a union and that causes other issues.

Ok. Just make sure you use that AndAlso and OrElse abundantly. It prevents problems with accessing nulls (though that was not the real issue here). Also, if things get tricky, you can extract them and put them in a function which returns a boolean. But that you knew already, I believe ;)

abel, that does the trick. thanks alot. also thanks for putting up with my confusing questions.