Lambda Expression using OR operator

Question

I believe there is a better way to write this but I am experiencing a mental block.

int num = 0;

using(var db = new TestDB())
{
    num = db.Table.Where(x => x.FavoriteSport == "Baseball" &&
                             (x.FavoriteColor == "Green" || 
                              x.FavoriteColor == "Blue" || 
                              x.FavoriteColor == "Red")).Count();
}

return num;

Is there a better way to write the OR statements? I have tried:

x.FavoriteColor == "Green" || "Blue" || "Red"

but the compiler says Operator || cannot be applied to operands of type 'bool' and 'string'

Any help is appreciated.

Go for readability. I believe your current example is both readable and maintainable. It's nothing wrong with that. Do note if there is nothing between the `using` and the `return` statement you can just do `return db.Table.Where...` — default, Mar 01 '16 at 20:09
Thank you all for the quick comments and answers. I didn't expect all of those answers to happen so rapidly haha but I guess that's why SO is #1. @Default Thanks for note. I will be sure to use that. Thanks again — Grizzly, Mar 01 '16 at 20:14
@Servy good point, I missed that. Removed the comment to avoid confusion — Jeroen Vannevel, Mar 02 '16 at 15:50

score 11 · Accepted Answer · edited May 23 '17 at 11:53

11

You can use Contains method of array/list/hashset.

var colors = new List<string> {"Green", "Red", "Blue" };

db.Table.Where(x => x.FavoriteSport == "Baseball" &&
                         (colors.Contains (x.FavoriteColor)).Count()

It will generate SQL query like

SELECT ... WHERE FavoriteColor = 'Baseball' AND FavoriteColor in ("Green", "Red", "Blue")

I'd like to add that if you work with datasets which are stored in a memory you should bear in mind that List's Contains takes O(N) iteration to get result. So if colors contains a lot of elements you should use set HashSet instead with O(1).

var colors = new HashSet<string> {"Green", "Red", "Blue", .... };

someDataSet.Where(x => x.FavoriteSport == "Baseball" &&
                         (colors.Contains (x.FavoriteColor)).Count()

You can find List-HashSet performance comparison here

edited May 23 '17 at 11:53

Community

1
1

answered Mar 01 '16 at 20:06

Valentin

5,380
2
24
38

Always show the best example first IMO. You don't need to edit to the end of your answer. – default Mar 01 '16 at 20:16
2

Out of curiosity, would a hashset have a performance boost? This is a query to the database, which is translated to dynamic SQL so it should iterate through the entire collection anyway. – Derek Van Cuyk Mar 02 '16 at 14:29
1

@Valentin Ok, I agree in memory, a search through a hashset is more efficient (at times). The point I'm making is that this command should generate SQL (i.e., OP is using EF). The query generated should be "SELECT ... WHERE FavoriteSport = "Baseball" AND FavoriteColor IN ( Red", "Blue", "Green"). It would have to iterate through the entire collection to generate the IN statement. – Derek Van Cuyk Mar 02 '16 at 14:53
@DerekVanCuyk Yes, thanks.As I consider both of the cases build same sql query with the same performance. – Valentin Mar 02 '16 at 15:03

Ashkan Mobayen Khiabani · Answer 2 · 2016-03-01T20:09:09.377

4

string[] FavColor = new string[]{"Green","Red","Blue"};

int num = 0;

    using(var db = new TestDB())
    {
        num = db.Table.Where(x => x.FavoriteSport == "Baseball" &&FavColor.Any(x.FavoriteSport)).Count();
    }

    return num;

edited Mar 01 '16 at 20:09

answered Mar 01 '16 at 20:07

Ashkan Mobayen Khiabani

33,575
33
102
171

score 3 · Answer 3 · answered Mar 01 '16 at 20:07

You can use a container of objects and use the Contains method. For example:

var favoriteColors = new List<string>
{
   "Blue", "Green", "Red"
};
var num = 0;

using(var db = new TestDB())
{
  num = db.Table.Where(x => x.FavoriteSport == "Baseball" && favoriteColors.Contains(x.FavoriteColor)).Count();
}

I would check a profile to make sure that the generated SQL is using teh IN statement though.

score 3 · Answer 4 · answered Mar 01 '16 at 20:11

pretty much what everyone has said - you can make a collection of valid strings and see if your string is in that collection. You can do it inline:

num = db.Table.Count(x => x.FavoriteSport == "Baseball" &&
                       new []{"Green","Red","Blue"}.Contains(x.FavoriteColor);

Worth noting that you can swap your Where out for Count directly

Lambda Expression using OR operator

4 Answers4