Contains is smart enough to check for null

Starting with Entity Framework 4.0 you can use Contains clause in your linq query to check if the column value matches any values in a given list. Suppose we have a model with Product and Category as shown below.

Let’s say we want to retrieve products that belong to Beverages or Product Category, we can write the following linq query.

If you capture the sql that is generated for the above linq query, you would notice that list of categories to match, is not passed as parameters to the sql query. Instead the values appears as part of the query. This is different from how linq to sql converts the contains clause where each value is passed in as parameter. I am not quite sure what side effects you might have with the reusability of the execution plan. From what i know, it is always a good practice to use parameters in your query. This way sql server can create one execution plan for the query and reuse it when different values are passed in. However in this case, it probably might not reuse the execution plan. Code below shows the sql captured.

Another neat improvement added with contains clause is the support for null value. Imagine, I want to retrieve all products that are either in the category of Beverages, Produce or have not yet been categorized. Well products that have not yet been categorized will have null value for categoryId or in simple english they are not associated to any category. You can create a list of values and one of the values in the list can be a null. Code below shows the code.

The sql generated for the above linq query checks whether CategoryName matches any values in the list or CategoryName is null.