LINQ Equivalent to SQL “IN” Operator

This is an example of how you can use the LINQ “Contains” in a similar way to how you use the SQL “IN” operator.

The code example below will use an XDocument object as our data that we would like to query. The XDocument contains a set of books where each book as a unique ID. 

We are going to use the .Contains extension to filter out the books that have an ID of 1, 3, and 5.


void Main()
{
	XDocument doc = GetBooks();
	
	Console.WriteLine(string.Format("Show all books\r\n{0}", doc));
	
	// A string array that will be used to as our Book ID filter. 
	// You can think of this array as the arguments that are used in the SQL "IN" operator
	string[] bookIds = new string[] {"1","3","5"};
	
	// Get books out of the xml object that have an ID of 1, 3, or 5
	var books = from b in doc.Descendants("Book")
				where bookIds.Contains(b.Element("ID").Value)
				select b;
			
	Console.WriteLine("\r\nShow the filtered books");
	foreach(var b in books)
		Console.WriteLine(b);
}

XDocument GetBooks()
{ 
  // Manually create an XML document that contain books
	XDocument doc = new XDocument(
		new XElement("Books",
			new XElement("Book",
				new XElement("Title", "Don Quixote"),
				new XElement("ID", 1),
				new XElement("Author", "Miguel de Cervantes")
				),
			new XElement("Book",
				new XElement("Title", "A Tale of Two Cities"),
				new XElement("ID", 2),
				new XElement("Author", "Charles Dickens")
				),
			new XElement("Book",
				new XElement("Title", "The Little Prince"),
				new XElement("ID", 3),
				new XElement("Author", "Antoine de Saint-Exupéry")
				),
			new XElement("Book",
				new XElement("Title", "The Da Vinci Code"),
				new XElement("ID", 4),
				new XElement("Author", "Dan Brown")
				),
			new XElement("Book",
				new XElement("Title", "The Catcher in the Rye"),
				new XElement("ID", 5),
				new XElement("Author", "J.D. Salinger")
				)));
				
		return doc;
}

This is the output of the code above…

Show all books
<Books>
  <Book>
    <Title>Don Quixote</Title>
    <ID>1</ID>
    <Author>Miguel de Cervantes</Author>
  </Book>
  <Book>
    <Title>A Tale of Two Cities</Title>
    <ID>2</ID>
    <Author>Charles Dickens</Author>
  </Book>
  <Book>
    <Title>The Little Prince</Title>
    <ID>3</ID>
    <Author>Antoine de Saint-Exupéry</Author>
  </Book>
  <Book>
    <Title>The Da Vinci Code</Title>
    <ID>4</ID>
    <Author>Dan Brown</Author>
  </Book>
  <Book>
    <Title>The Catcher in the Rye</Title>
    <ID>5</ID>
    <Author>J.D. Salinger</Author>
  </Book>
</Books>

Show the filtered books

<Book>
  <Title>Don Quixote</Title>
  <ID>1</ID>
  <Author>Miguel de Cervantes</Author>
</Book>
<Book>
  <Title>The Little Prince</Title>
  <ID>3</ID>
  <Author>Antoine de Saint-Exupéry</Author>
</Book>
<Book>
  <Title>The Catcher in the Rye</Title>
  <ID>5</ID>
  <Author>J.D. Salinger</Author>
</Book>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s