Lambda expression to retrieve database rows depending on checkbox values

Question

I have a database table that contains product models. All model names adhere to a strict nomenclature. The code position I am dealing with is the very last character of the model name and I need to retrieve only those model names using the following criteria:

Last char == "E"
Last char <> "E"
All model names

There are 2 checkboxes on the UI. At least one checkbox must be ticked. The user can also tick both. If both checkboxes are ticked then all product models need to be returned. If one checkbox is ticked only those product models ending with "E" should be returned. If the other checkbox is ticked product models not ending with "E" should be returned. Kind of like, true or false, or both.

Only product models ending with "E"       model.checkbox1 && !model.checkbox2;
Only product models not ending with "E"   !model.checkbox1 && model.checkbox2;
All product models                        model.checkbox1 && model.checkbox2;

Is there a way of combining the above logic into a single Lambda expression? My existing Lambda to retrieve all product models is:

IList<SelectListItem> items = db.Products
    .Select(m => new SelectListItem() { Text = m.Name, Value = m.Id.ToString() })
    .ToList();

I know I can use .Where(m => m.Name.EndsWith("E")) but that only takes care of the first condition above.

possible duplicate of [Dynamic Where clause lambdas in C#](http://stackoverflow.com/questions/7200245/dynamic-where-clause-lambdas-in-c-sharp) — Antarr Byrd, Oct 04 '14 at 07:11
@Antarr Byrd, your pointer to the post about dynamic Lambda queries is not exactly a match to my question. I think a dynamic query will be my answer, yes. I can see how to create dynamic queries now and I appreciate that, but it doesn't answer my question. I could edit my question and ask now, how would I do this? With .EndsWith()? If so, how can .EndsWith() match ANY or everything except "E"? Or, what other option might work? — rwkiii, Oct 04 '14 at 08:15

Erti-Chris Eelmaa · Answer 1 · 2014-11-09T12:08:05.093

First, give a bit more meaningful names to things:

modelsEndingWithE    =  model.checkbox1 && !model.checkbox2;
modelsEndingWithoutE  = !model.checkbox1 && model.checkbox2;
allModels  = modelsEndingWithE && modelsEndingWithoutE;

Ternary expressions can be used, but only for theoretical sense. Seeing this in production code will get you fired :-).

.Where(m => (allModels) ? true : // true - select all.
              (modelsEndingWithoutE) ?  // otherwise
                 !m.Name.EndsWith("E") : // all elements not ending with 'E'
                  m.Name.EndsWith("E")); // all elements ending with E

The second way is to use short-circuiting.

.Where(x => allModels || (modelsEndingWithE && x.Name.EndsWith("E")) ||
             (modelsEndingWithoutE && !x.Name.EndsWith("E"))

The third way is to compose the query step, by step.

IQueryable<SelectListItem> query = db.Products;
if(!allModels)
{
  if(modelsEndingWithE) query = query.Where(model => model.Name.EndsWith("E"));
  if(modelsEndingWithoutE) query = query.Where(model => !model.Name.EndsWith("E"));
}

query = query.Select(m => new SelectListItem() { Text = m.Name, Value = m.Id.ToString() })
IList<SelectListItem> results= query.ToList();

Lambda expression to retrieve database rows depending on checkbox values

1 Answers1