C# Linq syntax help - How can I exclude two values?

Question

Hi all,

I have this linq query that works well (although it may be written better, pls say so if you notice something)

var qry = BenefitCodes
    .Where(b => b.BenInterest != 'E' 
       && (b.BenProductLine == CoverageProductLine || b.BenProductLine == null) )
    .Select(b => b)
    .OrderBy(b => b.BenDesc);

A new requirement came down the pipeline to exclude two BenCodes ( 1001, 1009), BenCodes is just another column in the SQL table.

Am I supposed to use some variation of ".Contains", I would have to do !Contains or something. Can anyone point me in the right direction?

Thanks, ~ck in San Diego

Answer 1

You could just simply add another line to the Where clause and exclude every itm with a BenCodes of 1001 or 1009.

Like this:

var qry = BenefitCodes.Where(b => b.BenInterest != 'E' && (b.BenProductLine == CoverageProductLine || b.BenProductLine == null) && b.BenCodes != 1001 && b.BenCodes != 1009 ).Select(b => b).OrderBy(b => b.BenDesc);

Answer 2

Yes, one way to handle this is the following (for brevity and readability, I am excluding the remainder of your query):

var excludedBenCodes = new List<int>() { 1001, 1009 };
var query = BenefitCodes.Where(b => !excludedBenCodes.Contains(b.BenCodes));

I believe this to be more readable and more maintainable than the alternative of adding a subclause b.BenCodes != 1001 && b.BenCodes != 1009 to your where clause.

Answer 3

var qry = BenefitCodes
    .Where(b => b.Code != '1001' 
        && b.Code != '1009' 
        && b.BenInterest != 'E' 
        && (  b.BenProductLine == CoverageProductLine 
           || b.BenProductLine == null))
    .OrderBy(b => b.BenDesc);

You don't even need the "Select" when you aren't using the LINQ syntax. as the Where and OrderBy methods already return your IQueryable.

Answer 4

This might make things a bit more readable, I'd change query to

var qry = BenefitCodes.Where(b => FitsCriteria(b)).OrderBy(b => b.BenDesc);

and add method

public bool FitsCriteria(BenefitCode b)
{
return b.BenInterest != 'E' && 
   (b.BenProductLine == CoverageProductLine || b.BenProductLine == null) && 
   b.BenCodes != 1001 && 
   b.BenCodes != 1009;
}

Kindness,

Dan