LINQ Query with dynamic number of boolean ANDs

Question

I am working on a search web page for my site. The requirements state that users can enter text for any combination of 9+ fields and the search should do an 'AND' match when querying the database. I could fairly quickly write this as a stored procedure using 'ISNULL' but I'm trying to figure out how to accomplish the same thing in LINQ. I thought I could query the results of a query, but I'm getting the error

"Only arguments that can be evaluated on the client are supported for the String.Contains method"

Here's my example

var people = db.People

if(null != fname)
{
people= from e in people
   where e.FirstName.Contains(fname)
   select e;
}

if(null != lname)
{
people= from e in people
   where e.LastName.Contains(lname)
   select e;
}

return people;

Can I query the resultset of a previous query? Is there a better approach I'm just not thinking of?

Thanks in advance.

Answer 1

How is your fname and lname defined?

You should look at PredicateBuilder here - especially if you also want OR at some time:

http://www.albahari.com/nutshell/predicatebuilder.aspx

Answer 2

This should work and seems simpler:

people = from e in db.People
    where (lname == null || e.LastName.Contains(lname))
       && (fname == null || e.FirstName.Contains(fname))
    select e;

The code you provided doesn't have anything obvious wrong with it, as there is no reason you can't query the results of another query. It looks like fname or lname are being defined in some way that the SQL generator doesn't understand.

Answer 3

This should do it:

var people = db.People;
if(!String.IsNullOrEmpty(fname))
    people = people.Where(p => p.FirstName.Contains(fname));
if(!String.IsNullOrEmpty(lname))
    people = people.Where(p => p.LastName.Contains(lname));
return people;

Answer 4

I'll sometimes accomplish the same thing in fewer lines by calling the extension methods directly:

var people = from e in db.People select e;

if(null != fname)
{
   people = people.Where(e => e.FirstName.Contains(fname));
}

if(null != lname)
{
    people = people.Where(e => e.LastName.Contains(lname));
}

return people;