Click here to Skip to main content
Click here to Skip to main content

Bringing the IN clause from SQL to C#

, 29 Aug 2010
Rate this:
Please Sign up or sign in to vote.
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:

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:

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:

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, for example:

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:

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:

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:

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:

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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

gedgei
Software Developer
Lithuania Lithuania
No Biography provided

Comments and Discussions

 
Question[My vote of 1] Not enough info Pinmembermsdevtech2-May-12 3:09 
GeneralMy vote of 5 PinmemberJeff.Crawford22-Mar-12 1:30 
GeneralMy vote of 4 Pinmemberredsunbeer12-Sep-10 6:37 
GeneralGood work! PinmemberSumit Kumar Pranav30-Aug-10 20:07 
GeneralExtensionMethods are possible in FW 2.0 PinmemberFZelle30-Aug-10 8:04 
Extension Methods are just syntactic sugar produced by the C#3 compiler, so they work with FW2.
You just need an Attribut for it to work.
 
Just download and use LinQBridge.
GeneralRe: ExtensionMethods are possible in FW 2.0 Pinmembergecka30-Aug-10 8:12 
GeneralLooks good but.... PinmentorSandeep Mewara28-Aug-10 23:44 
GeneralRe: Looks good but.... Pinmembergecka29-Aug-10 0:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 29 Aug 2010
Article Copyright 2010 by gedgei
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid