LINQTOSql Missing parameter question

Question

I have a long LinqtoSQl query in which several parameters I'm not forcing the user to specify anything. I started using a Select Case statement that would test rather or not a parameter string's length > 0 or if it's an int > 0. But I realized that I would have to test for each possibility and create queries based on each.

I did some searching and ran across a post in which the person answering the post was saying to negate a portion of the query use ||. After doing some more searching (and realizing with little c# skills I do have || is the OR conditional), I realized that wouldn't help me.

I guess what I want to do is something like

Dim r = From x in db.List _
        (if firstName.Length < 1 then ignore query
         else)where x.firstName = firstName _
        (if lastName.Length < 1 then ignore query
         else)where x.LastName = lastName _
        Select x

I knw there has to be a better way than IfElse'ing my way through this...I was about to do some funky stuff with a StringBuilder, but I'm not sure it would "fire", ie:

Dim sb as New StringBuilder
sb.Append("Dim r = From x in db.List _")
If firstName.Length < 1 then 
  sb.Append("Where x.firstName = firstName") 

ughh, please tell me there's a better way...

Thanks for your help!

Answer 1

Use the fact that queries are composable. I'll write this in C# to start with, then translate it into VB afterwards if you need that. The principle would be the same.

IQueryable<YourEntityType> query = db.List;
if (firstName != "")
{
    query = query.Where(x => x.firstName == firstName)
}
if (lastName != "")
{
    query = query.Where(x => x.lastName == lastName)
}

Now just read from query appropriately. (I've changed the nature of the string conditions just because it simpler to understand "is this string the empty string" than "is this string's length greater than 0" - both will work, obviously.)

Note that you can't do this sort of conditional call in a query expression, but it's easy when you're just calling the extension methods explicitly.

Answer 2

How about...

Dim r = From x in db.List _
        where (x.firstName = firstName Or firstName = "") _
        And (x.LastName = lastName Or lastName = "") _
        Select x