How do you implement the equivalent of SQL IN() using .net

In .net (c# or vb) expressions, how would you implement SQL’s handy IN() functionality?

i.e. value in (1, 2, 4, 7)

  • how to find the size of image in sql table?
  • Parameterized SQL, ORACLE vs SQL Server with regular expression
  • sql conversion of a varchar data type to a datetime data type out-of-range
  • SQL query on rows with timestamp relative to another timestamp
  • Can I change a column from NOT NULL to NULL without dropping it?
  • How can I disable update table for all user
  • rather than:

    value = 1 or value = 2 or value = 4 or value = 7

  • How to Edit user DSN Programmatically in .Net?
  • Add multiple SQL values with same parameterized query?
  • sql permissions to a login user to further create logins and application roles
  • ExecuteReader requires an open and available Connection. The connection's current state is Connecting
  • .net: Best practice for exception handling while using Transactions?
  • Multiple databases with slightly changing models. How do I allow Entity Framework to remove columns at run-time?
  • 9 Solutions collect form web for “How do you implement the equivalent of SQL IN() using .net”

    using System;
    using System.Linq;
    
    static class SqlStyleExtensions
    {
        public static bool In(this string me, params string[] set)
        {
           return set.Contains(me);
        }
    }
    

    Usage:

    if (Variable.In("AC", "BC", "EA"))
    {
    
    } 
    

    I have made an extension method for this that I find quite useful. However, it is not much more than syntactic sugar wrapping the existing IEnumerable.Contains() function.

    /// <summary>
    /// Returns true if the value is represented in the provided enumeration.
    /// </summary>
    /// <typeparam name="T">Type of the value</typeparam>
    /// <param name="obj">The object to check if the enumeration contains</param>
    /// <param name="values">The enumeration that might contain the object</param>
    /// <returns>True if the object exists in the enumeration</returns>
    public static bool In<T>(this T obj, IEnumerable<T> values) {
        return values.Contains(obj);
    }
    

    Edit:
    Someone beat me to it, damnit.
    I’ll keep by post here though since it’s a more generic version.

    I know there are LOADS of answers here, but here’s my take on the subject, used daily in SubSonic. it’s an extension method:

    public static IQueryable<T> WhereIn<T, TValue>(
                    this IQueryable<T> query,
                    Expression<Func<T, TValue>> selector, 
                    params TValue[] collection) where T : class
    {
        if (selector == null) throw new ArgumentNullException("selector");
        if (collection == null) throw new ArgumentNullException("collection");
        ParameterExpression p = selector.Parameters.Single();
    
        if (!collection.Any()) return query;
    
        IEnumerable<Expression> equals = collection.Select(value =>
           (Expression)Expression.Equal(selector.Body,
                Expression.Constant(value, typeof(TValue))));
    
        Expression body = equals.Aggregate(Expression.Or);
        return query.Where(Expression.Lambda<Func<T, bool>>(body, p));
    }
    

    and WhereNotIn:

    public static IQueryable<T> WhereNotIn<T, TValue>(
                    this IQueryable<T> query, 
                    Expression<Func<T, TValue>> selector, 
                    params TValue[] collection) where T : class
    {
        if (selector == null) throw new ArgumentNullException("selector");
        if (collection == null) throw new ArgumentNullException("collection");
        ParameterExpression p = selector.Parameters.Single();
    
        if (!collection.Any()) return query;
    
        IEnumerable<Expression> equals = collection.Select(value =>
           (Expression)Expression.NotEqual(selector.Body,
                Expression.Constant(value, typeof(TValue))));
    
        Expression body = equals.Aggregate(Expression.And);
    
        return query.Where(Expression.Lambda<Func<T, bool>>(body, p));
    }
    

    usage:

    var args = new [] { 1, 2, 3 };
    var bookings = _repository.Find(r => r.id > 0).WhereIn(x => x.BookingTypeID, args);
    // OR we could just as easily plug args in as 1,2,3 as it's defined as params
    var bookings2 = _repository.Find(r => r.id > 0).WhereIn(x => x.BookingTypeID, 1,2,3,90);
    
    var bookings3 = _repository.Find(r => r.id > 0).WhereNotIn(x => x.BookingTypeID, 20,30,60);
    

    this really makes me smile every time i review it 🙂

    jim

    [edit] – originally sourced from here on SO but modified to use iqueryable and params:
    'Contains()' workaround using Linq to Entities?

    if((new int[] {1, 2, 4, 7}).Contains(value))
    {
        // Do some work.
    }
    

    As others have pointed out, you could create an In() Extension method (I’ll keep it generic so you can use it on any type):

    public static bool In<T>(T this obj, IEnumerable<T> col)
    {
        return col.Contains(obj);
    }
    

    So the initial example becomes:

    if(value.In(new int[] {1, 2, 4, 7}))
    {
        // Do some work.
    }
    

    Or using System.Linq

    (VB.NET)

    Enumerable.Contains({1, 2, 4, 7}, value)
    

    or

    {1, 2, 4, 7}.Contains(value)
    

    (C#)

    Enumerable.Contains(new int[]{1, 2, 4, 7}, value);
    

    or

    new int[] {1, 2, 4, 7}.Contains(value);
    

    You can use Contains() method on the list.

        int myValue = 1;
        List<int> checkValues = new List<int> { 1, 2, 3 };
    
        if (checkValues.Contains(myValue))
            // Do something 
    

    Using LINQ

    var q = from x in collection
            where (new int[] { 1, 2, 4, 7}).Contains(x.value)
            select x
    

    If you will do many lookups on the same dataset it is good from a performance perspective to use HashSet<T>.

    HashSet<int> numbers = new HashSet<int> { 1, 2, 4, 7 };
    bool is5inSet = numbers.Contains(5);
    

    Here’s some simple Linq with some pseudo code. No need to re-invent the wheel.

    int[] values = new int[]{1, 2, 4, 7};
    int target = 2;
    bool contains = values.Any(v => v == target);
    

    Or use .Contains as some have suggested.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.