Skip to content

Instantly share code, notes, and snippets.

@liamkernighan
Last active March 7, 2019 20:35
Show Gist options
  • Save liamkernighan/2ada18887f1303d0ea9ec97ca38f09c4 to your computer and use it in GitHub Desktop.
Save liamkernighan/2ada18887f1303d0ea9ec97ca38f09c4 to your computer and use it in GitHub Desktop.
How to Entity Framework SqlQuery with a List of parameters
namespace Persistence.Store.SqlRepositories
{
public class RawMsSqlRepository : DbContextManipulator, IRawSqlRepository
{
public RawMsSqlRepository(string connectionString) : base (connectionString)
{
}
public T[] Query<T>(string sqlText, Dictionary<string, object> parametersDict)
{
using (var ctx = CreateContext())
{
var sqlParameters = new SqlParameter[0].AsEnumerable();
var arrayParameters = new Dictionary<string, int>();
foreach (var currentParam in parametersDict)
{
if (!currentParam.Key.StartsWith("@"))
{
throw new InvalidOperationException($"Sql parameter {currentParam.Key} should start with @ character.");
}
if (currentParam.Value is System.Collections.IEnumerable)
{
var index = 0;
foreach(var currentValue in currentParam.Value as System.Collections.IEnumerable)
{
sqlParameters = sqlParameters.Append(new SqlParameter(currentParam.Key + index.ToString(), currentValue));
index++;
}
arrayParameters.Add(currentParam.Key, index);
}
else
{
sqlParameters = sqlParameters.Append(new SqlParameter(currentParam.Key, currentParam.Value));
}
}
foreach(var currentArrayParam in arrayParameters)
{
var newSqlParameters = new HashSet<string>();
for (var i = 0; i < currentArrayParam.Value; i++)
{
newSqlParameters.Add($"{ currentArrayParam.Key }{ i }");
}
sqlText = sqlText.Replace(currentArrayParam.Key, string.Join(",", newSqlParameters.ToArray()));
}
return ctx.Database.SqlQuery<T>(sqlText, sqlParameters.ToArray()).ToArray();
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment