Linq query to db with dynamic where parameters

Question

I have a table with data about cities: In a search input field the user types an arbitrary number of chars then presses "search" button. There is also another field where the user can enter the state via a dropdownlist.

Pressing the search button triggers an ajax call to a server controller which receives both the starting chars of the name and the two chars of the state.

The controller should return the list of cities with name starting with the supplied chars. If the state is passed, the list should contain only those cities whose name starts with the supplied chars AND that are situated in the state.

If the state is not passed all matching cities are returned regardless of the state.

I cannot find a way to write a single Linq statement, because I do not know how to write the part of the query for the state: Currently I do this:

public ActionResult selectCity(string searchString, string stateCode)
{
    List<CityViewModel> mylist = new List<CityViewModel>();
    IQueryable<City> mycities = null;

    if (stateCode == "")
    {
        mylist = (from c in db.Cities
                    where c.name.StartsWith(searchString)
                    select c);
    }
    else
    {
        mylist = (from c in db.Cities
                    where ((c.name.StartsWith(searchString)) &&
                          (c.stateCode == stateCode))
                    select c);
    }

    <snip>
    .....
    .....
    </snip>

   return PartialView("_selComune",elenco);
}

I can't believe that there isn't a way to do this with a single query.

Adriano Repetti · Accepted Answer · 2014-07-31 17:56:35Z

3

Yes, there is.

You want to say: if stateCode is empty or it matches City.stateCode then include it in result moreover City.Name must always begin with searchString. In code:

var mylist = 
    from c in db.Cities
    where 
        c.name.StartsWith(searchString) 
        && (stateCode == "" || c.stateCode == stateCode)
    select c;

Roughly equivalent to this SQL (assuming parameters with proper syntax are provided):

SELECT * FROM Cities
WHERE name LIKE @searchString AND (@stateCode = '' OR stateCode = @stateCode)

SQL Server will optimize second comparison away if first condition is always satisfied (just check Query Execution Plan).

_{I think it's a typo writing code example but myList isn't List<City> unless you also add ToList() to your LINQ query.}

edited Jul 31, 2014 at 17:56

answered Jul 29, 2014 at 19:11

Adriano Repetti

67.5k20 gold badges144 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kranz Over a year ago

... as usual, the answer is simple and straightforward; why bother with wildcards when we have "AND" and "OR".... Thanks for remembering me this golden rule

AFract · Accepted Answer · 2014-07-31 18:03:26Z

Adriano Repetti's answer allows you to write a single query to handle "nullable" parameters but sometimes it has performance drawbacks, translated in SQL the kind of query may prevent indexes to work.

Please note that the following will also work and build the exact needed query on sql side, with only a bit of more code on LINQ side :

    mylist = (from c in db.Cities
              where c.name.StartsWith(searchString)
              select c);

    if (stateCode != "")
    {
        mylist = mylist.Where(c.stateCode == stateCode);
    }

This kind of construct won't replace previous "where" content, it will add any new terms combining them with a "and" operator.

IQeryable is made to allow you to built it in many different lines, not a single one like you did in your code sample.

Collectives™ on Stack Overflow

Linq query to db with dynamic where parameters

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related