Use variable in where clause only if NOT empty? A kind of dynamic where clause?

Question

Is it possible to include a clause in the where (LINQ) but only if its "NOT" empty ?

i.e.

  where c.Code == sCode && p.Code == code

In this case the variable (standard c#) is called code ... if its NOT empty then the above where is great.. but if its empty then i don't want to include in it the where

i.e.

  where c.Code == sCode

In SQL its done like this

       AND ( ( @code = '' )
                  OR ( @code <> ''
                       AND C.Code = @code
                     )

Answer 1

where c.Code == sCode && (string.IsNullOrEmpty(code) || p.Code == code)

That's for standard LINQ, I have no idea if it works for LINQ to SQL.

Edit: This works through short circuit evaluation

if c.Code == sCode is false, it stops evaluating and returns false
otherwise, if string.IsNullOrEmpty(code) is true, it stops evaluating and returns true
otherwise, return p.Code == code

Answer 2

Put that in an IQueryable<T> extension method to keep it really simple:

public static IQueryable<Code> WithCodeIfNotEmpty(this IQueryable<Code> source, string code)
{
   if (string.IsNullOrEmpty(code))
      return source;
   else
      return source.Where(x => x.Code == code);
}

Usage:

var query = something.WithCodeIfNotEmpty("someCode");

Answer 3

As stated by others :

(string.IsNullOrEmpty(code) || p.Code == code)

btw sql can be optimised to

   C.Code = isnull(ltrim(rtrim(@code)), c.Code)