How To do In and Like clause in Linq To SQL

In clause is one of the common querying feature we use in SQL. But there is no clear explanation of how to write a Linq query which allows you to filter the results based on certain items in a list. Well after digging in through some of the query operators I found that there are various operators that you can use to tell a Linq query to filter the items based on items in a list. Let's have a look at few of those examples.

I will start with using the contain operator and show you how Linq to SQL provider converts that into in clause in SQL server.

image

CWindowssystem32cmd.exe

In the above code I am displaying all the customers in the city of London or Paris. Notice I am using the contains method on the cities array to find the right match. The contains clause gets converted into in clause as shown in the output.

Another possibility of in clause can be a like operator with bunch of or statements. For e.g where city like London or city like Paris. Although it's truly not an in statement but with bunch of or statements you can make it work like an in statement. Here is an example of doing that.

image

 

The results are identical except that I am using or and contains statement which Linq to SQL provider converts it into or and like statement as shown in the output window.

CWindowssystem32cmd.exe (2)

Another way in SQL where you can say show me all the customers who has at least one order that has a ship city of Bern is by making use of an exists clause. It is basically an in clause that evaluates to a sub query which selects only those customers who have orders with a ship city of Bern. Let's see how to write that in a Linq query.

image

 CWindowssystem32cmd.exe (3)

 

 

In the above example I am using where clause with a lambda expression that evaluates to true if there are any orders for a customer that has a ship city of Bern. As you can see in the output window, it gets evaluated to an exists clause in SQL server.

You can also use StartsWith and EndsWith operator to force Linq to SQL to use like operator in comparing a value in a query. Here is an example of that.

image

CWindowssystem32cmd.exe (4)

Notice that Linq to SQL provider converted the StartsWith operator with like clause and value is appended with % at the start. Same thing happens with EndsWith except the value is appended with % at the end.

 

If build in operator does not give you the flexibility of comparing the values, you have the options of using SqlMethods which gives you more control in how you like value should look like. In the query below, I have made use of % before and after the value.

image

 CWindowssystem32cmd.exe (5)

Published Friday, April 18, 2008 4:48 PM by zhirani

Comments

# re: How To do In and Like clause in Linq To SQL

Tuesday, April 29, 2008 5:31 PM by csnyder

Zeeshan,  Nice article.  but I've been looking on the web for a way to do a custom "Where" clause in Linq.

I've got 3 radio buttons, RE, UCC, and Life.

Any one or all of them can be checked.  So I need a way for Linq to filter the database based on which entry is selected:

Where RE=true or Ucc=true.

Where RE=True

Where UCC=True or Life=true.

do you know any way to do this??  I haven't been able to find anyone with a suggestion on how to perform this.

Thanks

chuck

# re: How To do In and Like clause in Linq To SQL

Sunday, October 12, 2008 11:20 AM by brian

I tried your first query with city.Contains(c.City) and get the following exception:

Queries with local collections are not supported.

Any ideas?

# re: How To do In and Like clause in Linq To SQL

Friday, August 28, 2009 4:22 PM by Michel Fornaris

I'm really wonder how you made that code work!

I tried and all that I get is this exception:

LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)' method, and this method cannot be translated into a store expression.

Any idea why that's happening to me?

Thanks,

Michel Fornaris

# re: How To do In and Like clause in Linq To SQL

Monday, October 5, 2009 7:07 AM by Ernesto

Hi Michel

It appears that you are trying to use Entity Framework instead of LINQ to SQL, the EF doesn't have support for Contains neither to UDF as L2Q has.... shame on MS for not including such useful features.