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>

How to remove items from an enumerable object that are equal to items of another enumerable object

Let’s say we have two integer arrays:

int[] a = { 1, 2, 3, 4, 5, 6, 7, 8, 9 }
int[] b = { 3, 4, 5, 8, 9, 10, 45 }

C# provides a handy extension method that we can call that will compare the two arrays and then return a new enumerable object that will only contain integers from “a” that are NOT in “b”.

Example:

void Main()
{
 int[] a = { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
 int[] b = { 3, 4, 5, 8, 9, 10, 45 };

 // Remove items from "a" that are not in "b"
 var c = a.Except(b);
 foreach(int i in c)
 {
 Console.WriteLine(i);
 }

 Console.WriteLine("--------");

 // Remove items from "b" that are not in "a"
 c = b.Except(a);
 foreach(int i in c)
 {
 Console.WriteLine(i);
 }
}

As you can see in the code above, the handy extension method is called “Except”. The formal definition of this extension method is:

Enumerable.Except(Of TSource) Method (IEnumerable(Of TSource), IEnumerable(Of TSource))
Produces the set difference of two sequences by using the default equality comparer to compare values.

Namespace: System.Linq
Assembly: System.Core (in System.Core.dll)

Return Custom Class in LINQ

Here’s an example of how to use LINQ with a custom class:

static void GetPeople(int minAge, int maxAge)

{

    var people = CreatePersonList(1000);

 

    var peopleList = from p in people

                where p.Age >= minAge

                    && p.Age <= maxAge

                orderby p.Age,p.Name

                select p;

 

    foreach (Person p in peopleList)

    {

        Console.WriteLine("\r\nName: {0}\r\nAge: {1}\r\nGender: {2}\r\n", p.Name, p.Age, p.Gender);

    }

}

The Three Stages of a LINQ Query

Every LINQ query requires three stages:

1. Obtain the data source(s)
2. Create the query
3. Execute the query

For example:

static void NumberGreaterThan5()

{

//The data source will be an array of integers

int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 };

//Create the query

var ng5 = from n in numbers

where n > 5

orderby n

select n;

//Execute the query

foreach (int i in ng5)

{

Console.WriteLine(i);

}

}

Breaking down the code example:

The data source

int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 };

The LINQ query

static void NumberGreaterThan5()

var ng5 = from n in numbers

where n > 5

orderby n

select n;

1. The var keyword is used to define a strong-typed variable without having to identify that type – the compiler will figure it out based on the right-hand side of the equal sign.
   
2. ng5 is our strong-typed object that will contain the results of our LINQ query
3. n is our iterator variable which iterates through the numbers array. Notice how we do not specify the type of n either! The compiler will determine the type of n when the query is executed.
4. The where statement defines our condition – Our condition in this example is to get all values from the numbers array which are greater than the number 5.
5. The orderby statement will sort the results in ascending order
6. The select statement specifies what values will be added to the ng5 object. Note that we could do interesting things to the select statement such as this:

   static void NumberGreaterThan5()

   var ng5 = from n in numbers

   where n > 5

   orderby n

   select n * 2;

Execute the LINQ query

It is not until we run the foreach statement that the LINQ query gets executed.