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?

Take a look at the answer here: stackoverflow.com/a/8841339/138938 — Jon Crowell
– Jon Crowell, Commented Apr 25, 2012 at 20:20

kevev22 · Accepted Answer · 2012-04-25 22:14:45Z

34

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.

answered Apr 25, 2012 at 22:14

kevev22

3,76524 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grasshopper Over a year ago

Just thought I'd say thanks for this answer. This helped me out a lot with a problem I was trying to solve.

Simon_Weaver Over a year ago

+1 for PredicateBuilder - that is really the most flexible way

Jason · Accepted Answer · 2012-04-25 20:30:02Z

0

You could group each filter option with an OR null condition, and chain them all together with AND, like so:

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 == submissionId || submissionId == null)
            && (cl.claimId == claimId || claimId == null)
            && so on and so on... ).ToList();

    }

So if submissionId == null and claimId == "123", it would only filter the results on claimId. You could replace each null with an empty string or whatever your "no value" condition is.

answered Apr 25, 2012 at 20:30

Jason

1,3256 silver badges14 bronze badges

Comments

Community · Accepted Answer · 2015-12-01 10:36:08Z

Take a look at LINQKIT http://www.albahari.com/nutshell/linqkit.aspx It will allow you to build predicate

Here is a code that I use but read the documentation above. The predicate will allow you to chain up a bunch of OR or AND or combination of it.

private static IEnumerable<SurveyResult> filterData(string firstName, string lastName, List<SurveyResult> results)
{
    var predicate = PredicateBuilder.True<SurveyResult>();


    IEnumerable<SurveyResult> a;
    if (excludeBadData)

    if (firstName != string.Empty)
    {
        predicate = predicate.And(p => p.User.FirstName.ToLower().Contains(firstName.ToLower()));
    }

    if (lastName != string.Empty)
    {
        predicate = predicate.And(p => p.User.LastName.ToLower().Contains(lastName.ToLower()));
    }


    a = from r in results.AsQueryable().Where(predicate) select r;
    return a;
}

Collectives™ on Stack Overflow

Linq to SQL multiple conditional where clauses

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related