Only do Where condition if a value is passed in

Question

I have the following LINQ statement that does on where on the date and a LabID.

I'm passing in a list of LABS and a date, however they are not required, and I could potentially only pass in a date, and no lab, in which case I'd like to get results for all labs for that particular lab.

here is what I have now:

List<dExp> lstDatExp = (from l in ctx.dExp.Include("datLab")
                        where values.Contains(l.datL.Lab_ID)
                            && l.reportingPeriod == reportingPeriod
                        select l).ToList<dExp>();

But this breaks if the value getting passed in is not there. How do I change this to make sure both of my where statements are optional?

Answer 1

Here are two ways to do that.

But first, please don't use a single lowercase l as an identifier. It is way too easy to confuse it with the number 1. More generally, stp using abbrevs in yr cde, it mks it hrdr to rd.

First technique:

var query = from lab in ctx.dExp.Include("datLab")
            where values == null || values.Contains(lab.datL.Lab_ID)
            where reportingPeriod == null || lab.reportingPeriod == reportingPeriod
            select lab;
var list = query.ToList<dExp>();

Second technique:

IEnumerable<dExp> query = ctx.dExp.Include("datLab");
if (values != null)
    query = query.Where(lab=>values.Contains(lab.datL.Lab_ID));
if (reportingPeriod != null)
    query = query.Where(lab=>lab.reportingPeriod == reportingPeriod);
var list = query.ToList<dExp>();

Answer 2

What we do is something like (l.reportingPeriod == reportingPeriod || reportingPeriod == null) So you check to see if the parameter is its default meaning it hasnt been used or if there is something there check it against the database.

Answer 3

You need to check if your values are null before doing the query, and if they are, don't do the extra condition.

List<dExp> lstDatExp = 
    (from l in ctx.dExp.Include("datLab")
     where 
         (values == null || values.Contains(l.datL.Lab_ID)) &&
         (reportingPeriod == null || l.reportingPeriod == reportingPeriod)
     select l).ToList<dExp>();

This way if values or reportingPeriod are null they are essentially optional.

Answer 4

With IQueryable you can simply add conditions in steps:

int? reportingPeriod = ...;

IQueryable<dExp> resultsQuery =         // don't use `var` here.
        ctx.dExp.Include("datLab");   

if (values != null)
   resultsQuery = resultsQuery.Where(exp => values.Contains(exp.datL.Lab_ID));

if (reportingPeriod.Hasvalue)
   resultsQuery = resultsQuery.Where(exp => exp.reportingPeriod == reportingPeriod.Value);

// additional .Where(), .OrderBy(), .Take(), .Skip() and .Select()

// The SQL query is made and executed on the line below
// inspect the string value in the debugger
List<dExp> results = resultsQuery.ToList();