How to pass a dynamic/variable list of values as SqlParameters with FromSqlRaw in EF Core

As a sort of follow up to my blog posts here and here I will show how to use a dynamic list of values a parameters when using FromSqlRaw. The condition in this case that you may sometimes be calling your method with 5 parameters, sometimes with 20 etc. Keep in mind that the maximum number of parameters with SQL Server is 2098!

The solution is to create both the SqlParameters and the raw SQL parameters placeholders at run time, something similar to the following:

var items = new int[] { 1, 2, 3 };

var parameters = new string[items.Length];
var sqlParameters = new List<SqlParameter>();
for (var i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@p{0}", i);
    sqlParameters.Add(new SqlParameter(parameters[i], items[i]));
}

var rawCommand = string.Format("SELECT * from dbo.Shippers WHERE ShipperId IN ({0})", string.Join(", ", parameters));

var shipperList = db.Set<ShipperSummary>()
    .FromSqlRaw(rawCommand, sqlParameters.ToArray())
    .ToList();

The generated SQL statement ends up looking like this:

SELECT * from dbo.Shippers WHERE ShipperId IN (@p0, @p1, @p2)

If you used this method a lot in your app, with a variable number of parameters, you could consider parameter padding to avoid plan cache solution, for example by using a method like in the sample here.

Comments or questions?