Linq select objects in list where exists IN (A,B,C)

Question

I have a list of orders.
I want to select orders based on a set of order statuses.

So essentially select orders where order.StatusCode in ("A", "B", "C")

// Filter the orders based on the order status
var filteredOrders = from order in orders.Order
                     where order.StatusCode.????????("A", "B", "C")
                     select order;

Solution 1

Your status-codes are also a collection, so use Contains:

var allowedStatus = new[]{ "A", "B", "C" };
var filteredOrders = orders.Order.Where(o => allowedStatus.Contains(o.StatusCode));

or in query syntax:

var filteredOrders = from order in orders.Order
                     where allowedStatus.Contains(order.StatusCode)
                     select order;

Solution 2

NB: this is LINQ to objects, I am not 100% sure if it works in LINQ to entities, and have no time to check it right now. In fact it isn't too difficult to translate it to x in [A, B, C] but you have to check for yourself.

So, instead of Contains as a replacement of the ???? in your code you can use Any which is more LINQ-uish:

// Filter the orders based on the order status
var filteredOrders = from order in orders.Order
                     where new[] { "A", "B", "C" }.Any(s => s == order.StatusCode)
                     select order;

It's the opposite to what you know from SQL this is why it is not so obvious.

Of course, if you prefer fluent syntax here it is:

var filteredOrders = orders.Order.Where(order => new[] {"A", "B", "C"}.Any(s => s == order.StatusCode));

Here we again see one of the LINQ surprises (like Joda-speech which puts select at the end). However it is quite logical in this sense that it checks if at least one of the items (that is any) in a list (set, collection) matches a single value.

Solution 3

var statuses = new[] { "A", "B", "C" };

var filteredOrders = from order in orders.Order
                             where statuses.Contains(order.StatusCode)
                             select order;

Solution 4

Try with Contains function;

Determines whether a sequence contains a specified element.

var allowedStatus = new[]{ "A", "B", "C" };
var filteredOrders = orders.Order.Where(o => allowedStatus.Contains(o.StatusCode));

View more solutions

Comments

[MartinS]

Thanks to all that answered so quickly. Esp for the lambda solution. I've not done anything with lambda expressions as yet. I assume I'd do a NOT contains using (o => !(statuses.Contains(o.OrderHeaderOrderStatusCode)))

[Jay Shah]

new[] { "B", "AA" }.Contains("A") will return false, NOT true.

[Jay Shah]

I would say that use HashSet instead of array for allowedStatus because HashSet's contains method is fastest and there'll be performance issues with array if it contains more than 1000 items. var allowedStatus = new HashSet<string> { "A", "B", "C" };

[Tim Schmelter]

@JayShah: late answer: yes, in general you are right, if you have thousands of items it might be better to use a HashSet<T> in the first place. But it's not a good idea always: You need another collection, so more memory, if you pass in already an array or list. You need to fill that HashSet<T>, so more cpu cycles. You might not be able to use thousands of items anyway if this is not Linq-To-Objects but Linq-To-Entities(the Contains is translated to an sql-IN clause which has limitations).

[Guilherme]

@JayShah also, this does not change the performance when using entity framework. It will be converted to the SQL "IN" clause and will be executed by the database engine. In this case, the array will be a bit faster to create.