LINQ parameter == null with SQL CE

SQL CE cannot handle parameters so, the so standard construction

.Where(p => p.Category == category || category == null)

fails because LINQ needs to use a parameter to do that value comparison in-query.  But it is perfectly valid SQL to pass something like this:

.Where(p => p.Category == category || true)

because ‘true’ is always…well…true.  So if you parse your variable to a bool before the LINQ statement and then pass that variable to SQL CE, you’re in good shape:

bool bCategory = (string.IsNullOrEmpty(category)) ? true : false;
Products = repository.Products.Where(p => p.Category == category || bCategory);

This allows you do to all the filtering in the query before returning data, and you can use this trick on as many conditions as you want without it getting messy.

Advertisements

%d bloggers like this: