Linq to SQL multiple conditional where clauses

Question

At the moment I am retrieving my results as follows :

public List<claim> GetClaims()
{
    return _db.claims.OrderBy(cl => cl.claimId).ToList();
}

But now I am trying to add up to 8 conditional where clauses based on filters above my listview.

So I turned into:

public List<claim> GetFilteredClaims(string submissionId, string claimId,
                                     string organization, string status,
                                     string filterFromDate, string filterToDate,
                                     string region, string approver)
{
    return _db.claims.Where(cl => cl.submissionId == 5).ToList();
}

How can I do a check for each filter to add a where clause only if they contain a value?

Answer 1

There is no reason why you can't just keep filtering the results by calling .Where several times. Because of the deferred execution of LINQ to SQL it will all be executed in one SQL statement:

public List<claim> GetFilteredClaims(string submissionId, string claimId,
                                     string organization, string status,
                                     string filterFromDate, string filterToDate,
                                     string region, string approver)
{
    IQueryable<claim> filteredClaims = _db.claims;

    if (!string.IsNullOrWhiteSpace(submissionId))
    {
        filteredClaims = filteredClaims.Where(claim => claim.submissionId == submissionId);
    }

    if (!string.IsNullOrWhiteSpace(claimId))
    {
        filteredClaims = filteredClaims.Where(claim => claim.claimId == claimId);
    }

    ...

    return filteredClaims.ToList();
}

If you will ever need to add OR conditions, you could take a look at PredicateBuilder.