Linq with optional Where clauses and Sql Server CE

Question

I have a search form with an optional User Name field. If the User Name is not supplied then all matches should be returned.

I am using Linq and Sql Server CE 4.0.

The linq code looks like the following ->

from p in context.Accounts
where (name==string.Empty || p.UserName.Contains(name))

With Sql Server CE this throws the following error

"A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement."

Is there some other approach I can take to have optional Where clauses in Linq?

FYI the following

from p in context.Accounts
where (string.IsNullOrEmpty(name) || p.UserName.Contains(name))

gives me the error

"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = isnull ]"}

This is due to Sql Server CE not supporting IsNull. I simply do the below if the Name parameter is Null.

if (name == null)
    name = string.Empty;

Answer 1

Try this:

var query = from p in context.Accounts
            select p;

if (!string.IsNullOrEmpty(name)) {
    query = query.Where(p => p.UserName.Contains(name));
}

There's no rule saying the query has to be in a single statement. You can add on to an existing query up until the point you actually execute it.