Tag Archives: SQL

Performing a WHERE IN with Entity Framework or on a List

by Bryan | Leave a comment

WHERE IN is a very useful and commonly used feature of SQL, it looks like this –

SELECT * FROM ORDER WHERE OrderId IN (10248, 10249, 10250, 10251)

The will return up to four rows of data, showing just the orders that have an OrderId in the list you passed to the select statement.

You can do the same with Entity Framework by using the Contains predicate with a Where.

First you need to put the OrderIds you are looking for in some sort of enumerable.

IEnumerable ordersToFind = new List { 10248, 10249, 10250, 10251 };

Then you use Where on the Orders DbSet and check the orderIds against the list you just created.

var orders = _northwindContext.Orders.Where(o => ordersToFind.Contains(o.OrderId));

That’s all there is too it. You don’t have to use this on just Entity Framework, it also works on simple lists, arrays, enumerables, etc.

Parameterized SQL WHERE IN clause c#

by Bryan | Leave a comment

If you are using some legacy SQL in C# you’ll occasionally need to write a WHERE IN.
Yon don’t want to end up writing something like the below, either directly or through some sort of loop.

string cmdText = "SELECT * FROM Members WHERE MemberId IN (100, 200, 300, 400);
SqlCommand cmd = new SqlCommand(cmdText);

Instead you should parameterize the sql. This involves two steps.

  1. Add parameters to the sql string.
  2. Add the parameters to SqlCommand.

Add a class to your project like SqlWhereInParamBuilder, I chose to use a static method and an extension method, but you can do it in any number of other ways.

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace SqlWhereInParameters
{
    public static class SqlWhereInParamBuilder
    {
        public static string BuildWhereInClause<T>(string partialClause, string paramPrefix, IEnumerable<T> parameters)
        {
            string[] parameterNames = parameters.Select(
                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
                .ToArray();

            string inClause = string.Join(",", parameterNames);
            string whereInClause = string.Format(partialClause.Trim(), inClause);
            
            return whereInClause;
        }

        public static void AddParamsToCommand<T>(this SqlCommand cmd, string paramPrefix, IEnumerable<T> parameters)
        {
            string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();

            string[] parameterNames = parameterValues.Select(
                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
                ).ToArray();

            for (int i = 0; i < parameterNames.Length; i++)
            {
                cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
            }
        }
    }
}

This is the usage

using System.Collections.Generic;
using System.Data.SqlClient;

namespace SqlWhereInParameters
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.RunCommandBulderForFullSelectExample();
        }

        private void RunCommandBulderForFullSelectExample()
        {
            List<long> memberIds = new List<long> { 100, 200, 300, 400 };
            string parameterPrefix = "MemberId";
            string cmdText = "SELECT * FROM Member WHERE Name IN ({0})";

            cmdText = SqlWhereInParamBuilder.BuildWhereInClause(cmdText, parameterPrefix, memberIds);

            SqlCommand cmd = new SqlCommand(cmdText);
            cmd.AddParamsToCommand(parameterPrefix, memberIds);
        }
    }
}