Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Bringing the IN clause from SQL to C#

0.00/5 (No votes)
29 Aug 2010 1  
This article shows you how you can implement something similar to the SQL IN clause in your C# code.

Since I have to work with T-SQL (Microsoft SQL Server SQL dialect) a lot, I tend to miss the IN clause in C# sometimes. Checking if a value is in a list of possible values in SQL is quite elegant:

SQL
IF(1 IN(1,2,3))
	PRINT '1 is in the list'

That’s not the case in C#, where the above example would traditionally be rewritten like this:

C#
if(1 == 1 || 1 == 2 || 1 == 3)
	Console.WriteLine("1 is in the list");

The Alternatives

When the value list gets bigger and bigger, the above method gets not very readable and contains lots of duplication. It would be nicer to have something closer to the IN clause. Let’s see the alternatives.

The first thing we can do is put all the possible values into an array. Prior to LINQ, arrays did not have a public method to check if it contains some value, so we have to write some code for ourselves and it becomes even more messy:

C#
int[] values = new int[] { 1, 2, 3 };
bool contains = false;
 
foreach(int value in values)
{
	if(value == 1)
	{
		contains = true;
		break;
	}
}
 
if(contains)
	Console.WriteLine("1 is in the list");

That’s not much better. Next, we can try to use a class with a Contains() method, like List<t>, for example:

C#
int[] values = new int[] { 1, 2, 3 };
List<int> valueList = new List<int>(values);
 
if(valueList.Contains(1))
	Console.WriteLine("1 is in the list");

That’s better, but it involves having the data twice, for the array and for the list, since the list copies values for its own use internally. To avoid that, we can take advantage of the fact that Array already implements IList<T> interface, thus simplifying our code to:

C#
IList<int> valueList = (IList<int>)new [] { 1, 2, 3 };
 
if(valueList.Contains(1))
	Console.WriteLine("1 is in the list");

That’s even better. Of course, we could go on and inline the variable, which brings us this piece of code:

C#
if(((IList<int>)new [] { 1, 2, 3 }).Contains(1))
	Console.WriteLine("1 is in the list");

...but I don’t feel pleasure reading it. Using LINQ, we can make the code a little clearer and shorter:

C#
if(new []{ 1, 2, 3 }.Contains(1))
	Console.WriteLine("1 is in the list");

Still not that readable as the IN clause.

So what else can we do to simplify it? I’d like it to look more like the IN syntax, so I came up with an extension method for the object class. Here it is, along with the above example rewritten:

C#
public static class InClauseObjectExtensions
{
	public static bool In<T>(this T @object, params T[] values)
	{
		// this is LINQ expression. If you don't want to use LINQ,
		// you can use a simple foreach and return true 
		// if object is found in the array
		return values.Contains(@object);
	}
 
	public static bool In<T>(this T @object, IEnumerable<T> valueList valueList)
	{
		// this is LINQ expression. If you don't want to use LINQ,
		// you can use a simple foreach and return true if object 
		// is found in the array
		return valueList.Contains(@object);
	}
}
...
if (1.In(1, 2, 3))
	Console.WriteLine("1 is in the list");

That looks almost like the IN clause! I’m using the params keyword, so all the arguments to the method would be put into an array that I can easily search my value in. The other overload that takes an IEnumerable<T> as argument is there in case you want to pass an array or some collection to the In() method. This comes handy when you already have an array, but still want to use the In() method instead of the LINQ Contains() extension method.

Too bad that extension methods are only supported from .NET 3.5, since we have many applications targeting .NET 2.0. :(

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here