SQL Like 'In' Function For .NET

SQL has a wonderful terse and natural syntax for checking if an item is inside of a collection by using the IN keyword. Which can be expressed like this:

expression IN (value_1, value_2, .... value_n)

In .NET, we can preform the same operation with .Contains() method on any enumerable collection which:

Determines whether a sequence contains a specified element

Beginning with VB 2010, we can even do this on the fly by using the Collection Initializers.

For example, to check whether a variable called personName was either "Sally" or "Jenny", we could use the following expression:

{"Sally","Jenny"}.Contains(personName)

However, I think this syntax leaves something to be desired. The verbiage is all wrong. I don’t really care if a collection contains some item. I care if an item is in a collection. Of course, logically, this is performing the same operation, but I want the personName variable to be in the drivers seat. I want personName to be the subject that is verbing against the other items.

For a bit of syntactic sugar, we can add a generic extension method to take in an ParamArray and check if the extended element falls inside that array.

Here’s the In method:

Note: In needs to be inside of square brackets because it is a Protected Keyword.

Visual Basic

''' <summary>
''' Determines if the Item is contained within the listed array
''' </summary>
''' <typeparam name="T">The type of object</typeparam>
''' <param name="item">The calling item</param>
''' <param name="range">An array or comma separated list of the items to check against the calling</param>
''' <returns>True if item is found in list</returns>
''' <remarks>Provides syntatic sugar by reordering the subject of the IEnumerable.Contains method</remarks>
<Extension()>
Public Function [In](Of T)(ByVal item As T, ByVal ParamArray range() As T) As Boolean
    Return range.Cast(Of T).Contains(item)
End Function

C Sharp

public static class Extensions
{
    /// <summary>
    /// Determines if the Item is contained within the listed array
    /// </summary>
    /// <typeparam name="T">The type of object</typeparam>
    /// <param name="item">The calling item</param>
    /// <param name="range">An array or comma separated list of the items to check against the calling</param>
    /// <returns>True if item is found in list</returns>
    /// <remarks>Provides syntatic sugar by reordering the subject of the IEnumerable.Contains method</remarks>
    public static bool In<T>(this T item, params T[] range)
    {
        return range.Contains(item);
    }
}

Throw this inside any module in your assembly, preferably one named something like Utilities or ExtensionMethods. Now we can call like this:

personName.In("Sally","Jenny")

If you’re checking against a predefined list you can pass that in as a parameter and cast back into an array.

Personally, I take this utility method with me wherever I go. I find it incredibly helpful for checking if an item exists within a defined range. Once you start using it, you won’t stop, and I think that’s a good thing! For my money, it substantially improves readability, especially if you find yourself working on older code bases without collection initializers.

List (Of LINQ Enumerable Methods)

Here’s a grouped listing of all the methods available on the IEnumerable Class.
Methods which can be composed using VB Query Syntax are generally listed first and are also highlighted yellow.

Projection Operations

  • Select - Projects each element of a sequence into a new form.
  • SelectMany - Projects each element of a sequence to an IEnumerable(Of T) and flattens the resulting sequences into one sequence.

Partitioning Data

  • Skip - Bypasses a specified number of elements in a sequence and then returns the remaining elements.
  • SkipWhile - Bypasses elements in a sequence as long as a specified condition is true and then returns the remaining elements.
  • Take - Returns a specified number of contiguous elements from the start of a sequence.
  • TakeWhile - Returns elements from a sequence as long as a specified condition is true.

Join Operations

  • Join - Correlates the elements of two sequences based on matching keys. The default equality comparer is used to compare keys.
  • GroupJoin - Correlates the elements of two sequences based on equality of keys and groups the results.

Grouping Data

  • GroupBy - Groups the elements of a sequence according to a specified key selector function.

Filtering Data

  • Where - Filters a sequence of values based on a predicate.
  • OfType - Filters the elements of an IEnumerable depending on their ability to be cast to a specified type.

Sorting Data

  • OrderBy - Sorts the elements of a sequence in ascending order according to a key.
  • OrderByDescending - Sorts the elements of a sequence in descending order according to a key.
  • ThenBy - Performs a subsequent ordering of the elements in a sequence in ascending order according to a key.
  • ThenByDescending - Performs a subsequent ordering of the elements in a sequence in descending order, according to a key.
  • Reverse - Inverts the order of the elements in a sequence.

Aggregation Operations

  • Average - Computes the average of a sequence of values.
  • Count - Returns the number of elements in a sequence.
  • LongCount - Returns an Int64 that represents the total number of elements in a sequence.
  • Max - Returns the maximum value in a sequence of values.
  • Min - Returns the minimum value in a sequence of values.
  • Sum - Computes the sum of a sequence of values.
  • Aggregate - Applies an accumulator function over a sequence.

Set Operations

  • Distinct - Returns distinct elements from a sequence by using the default equality comparer to compare values.
  • Except - Produces the set difference of two sequences by using the default equality comparer to compare values.
  • Intersect - Produces the set intersection of two sequences by using the default equality comparer to compare values.
  • Union - Produces the set union of two sequences by using the default equality comparer.
  • Concat - Concatenates two sequences.
  • Zip - Applies a specified function to the corresponding elements of two sequences, producing a sequence of the results.

Quantifier Operations

  • All - Determines whether all elements of a sequence satisfy a condition.
  • Any - Determines whether a sequence contains any elements.
  • Contains - Determines whether a sequence contains a specified element by using the default equality comparer.

Generation Operations

  • DefaultIfEmpty - Returns the elements of the specified sequence or the type parameter’s default value in a singleton collection if the sequence is empty.
  • Empty - Returns an empty IEnumerable(Of T) that has the specified type argument.
  • Range - Generates a sequence of integral numbers within a specified range.
  • Repeat - Generates a sequence that contains one repeated value.

Element Operations

  • First - Returns the first element of a sequence.
  • FirstOrDefault - Returns the first element of a sequence, or a default value if the sequence contains no elements.
  • Last - Returns the last element of a sequence.
  • LastOrDefault - Returns the last element of a sequence, or a default value if the sequence contains no elements.
  • ElementAt - Returns the element at a specified index in a sequence.
  • ElementAtOrDefault - Returns the element at a specified index in a sequence or a default value if the index is out of range.
  • Single - Returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.
  • SingleOrDefault - Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.

Equality Operations

  • SequenceEqual - Determines whether two sequences are equal by comparing the elements by using the default equality comparer for their type.

Converting Data Types

  • Cast - Casts the elements of an IEnumerable to the specified type.
  • AsEnumerable - Returns the input typed as IEnumerable(Of T).
  • AsQueryable - Converts a (generic) IEnumerable to a (generic) IQueryable.
  • ToArray - Creates an array from a IEnumerable(Of T).
  • ToDictionary - Creates a Dictionary(Of TKey, TValue) from an IEnumerable(Of T)according to a specified key selector function.
  • ToList - Creates a List(Of T) from an IEnumerable(Of T).
  • ToLookup - Creates a Lookup(Of TKey, TElement) from an IEnumerable(Of T)according to a specified key selector function.

Explanation of using FOR XML PATH('') to Concatenate Rows in SQL

I was recently shown a cool way to concatenate an unlimited number of rows into a single cell in SQL, but was left wondering how it worked under the hood. The trick involves using FOR XML PATH('') to build multiple values and then cast them back into a single string. Starting from a simple query, and getting increasingly more complex, let’s look at how this actually works.

I’ll do everything on SQLFiddle. You can follow along by starting your own fiddle or clicking the results link for each step. Let’s start off by creating a basic table called Person with two columns and three rows of data:

Create A Table

Start by making a table:

CREATE TABLE Person 
(
  FirstName varchar(20), 
  LastName varchar(20)
);
INSERT INTO Person
  (FirstName, LastName)
VALUES
  ('Kyle', 'Mit'),
  ('Bob', 'Builder'),
  ('Jimi', 'Hendrix');

Plain SQL Query

This is just a run of the mill SQL query returning 3 rows of data:

SELECT FirstName, LastName
FROM Person 

Results:

FirstName LastName
Kyle Mit
Bob Builder
Jimi Hendrix

SQL Query as XML Grouped by ‘Person’

Here, the output of the Query is formatted into XML with each row represented as a Root node named ‘Person’ and each column as a child element:

SELECT FirstName, LastName
FROM Person 
FOR XML PATH('Person')

Results:

<Person>
    <FirstName>Kyle</FirstName>
    <LastName>Mit</LastName>
</Person>
<Person>
    <FirstName>Bob</FirstName>
    <LastName>Builder</LastName>
</Person>
<Person>
    <FirstName>Jimi</FirstName>
    <LastName>Hendrix</LastName>
</Person>

XML Query With Custom Named Child Nodes

The name of each child node under person is determined by the final column name (this is important later). When columns are selected, those are used by default, however you can manually give any column a specific name.
Note: The name of the child nodes has changed based on our select statement:

SELECT FirstName AS First, LastName AS Last
FROM Person 
FOR XML PATH('Person')

Results:

<Person>
    <First>Kyle</First>
    <Last>Mit</Last>
</Person>
<Person>
    <First>Bob</First>
    <Last>Builder</Last>
</Person>
<Person>
    <First>Jimi</First>
    <Last>Hendrix</Last>
</Person>

XML Query with comma and NAMED child node

In SQL, when you perform any kind of aggregation or selection function to a column, it no longer applies a default name. We’d like to concatenate our fields with a comma, so we’ll add a comma to the select. As an intermediary step, we’ll explicitly specify a name just to show that the comma itself isn’t modifying the result set:

SELECT ',' + LastName AS Last
FROM Person 
FOR XML PATH('Person')

Results:

<Person>
    <Last>,Mit</Last>
</Person>
<Person>
    <Last>,Builder</Last>
</Person>
<Person>
    <Last>,Hendrix</Last>
</Person>

XML Query with comma and UNNAMED child node

By removing the explicit namimg, SQL isn’t able to guess the column name. Consequently, the data is just stuffed into the Person Element.
From the FOR XML PATH documentation:

Any column without a name will be inlined. For example, computed columns or nested scalar queries that do not specify column alias will generate columns without any name.

NOTE: This is a very important step towards concatenation. We need to somehow get rid of the xml markup around our data and we’ve dropped an entire node. In the next step we’ll find out how to get rid of the second one.

SELECT ',' + LastName
FROM Person 
FOR XML PATH('Person')

Results:

<Person>,Mit</Person>
<Person>,Builder</Person>
<Person>,Hendrix</Person>

XML Query with no root node

The command we’ve been using all along is FOR XML which has four different modes:

  1. RAW
  2. AUTO
  3. EXPLICIT
  4. PATH

In this case, we’re using PATH, which will wrap the data elements in a parent element named for the table from which it came. Optionally, you can add a string parameter to Path to override the root element name. The last trick:

If you specify a zero-length string, the wrapping element is not produced.

By manually specifying the path as an empty string, all the data elements are shown right next to each other.
Note the file name: this is still returning XML (just poorly formatted XML)

SELECT ',' + LastName
FROM Person 
FOR XML PATH('')

Results:

XML_F52E2B61-18A1-11D1-B105-00805F49916B
,Mit,Builder,Hendrix

Get result of the XML sub query

By selecting the result of the entire query, we transform the XML into a value:

SELECT (
        SELECT ',' + LastName
        FROM Person 
        FOR XML PATH('')
)

Results:

COLUMN_0
,Mit,Builder,Hendrix

Remove the First Comma

To remove the leading comma, we’ll use STUFF(character_expression, start, length, replaceWith_expression). The following query will start at position 1 and replace the 1st character with '':

SELECT STUFF((
              SELECT ',' + LastName
              FROM Person 
              FOR XML PATH('')
), 1, 1, '')

Results:

COLUMN_0
Mit,Builder,Hendrix

Cast into VARCHAR for type safety

Finally, we’ll take the whole query and make sure it’s of type VARCHAR. Also, for good measure, we’ll give the returned column a name:

SELECT CAST(STUFF((
              SELECT ',' + LastName
              FROM Person 
              FOR XML PATH('')
), 1, 1, '') AS VARCHAR(MAX)) AS LastNames

Results:

LastNames
Mit,Builder,Hendrix

And that’s how to go from constructing XML to a relatively simple concatenation. There are other ways to do this and other customizations you can add to this query, but this should help you understand a little bit more of the fundamentals underneath it all.