CODEWITHSUNDEEP
sqllinqlinq-to-sql
Craig G.
Craig G.

Reputation: 157

LINQ Query with dynamic number of boolean ANDs

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.

Upvotes: 1

Views: 375

Answers (4)

Dustin Laine
Dustin Laine

Reputation: 38503

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;

Upvotes: 4

kbrimington
kbrimington

Reputation: 25652

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;

Upvotes: 3

Gabe
Gabe

Reputation: 86718

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.

Upvotes: 2

Lasse Espeholt
Lasse Espeholt

Reputation: 17782

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

Upvotes: 3

Related Questions