/// <summary>
/// execute Raw SQL queries: Non-model types
/// https://github.com/aspnet/EntityFrameworkCore/issues/1862
/// </summary>
public static List <T> SqlQuery <T>(this DbContext context, string query, ILogger logger, params SqlParameter[] sqlParams)
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
if (sqlParams != null)
{
command.Parameters.AddRange(sqlParams);
}
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
var sw = new Stopwatch();
sw.Start();
var list = new List <T>();
var mapper = new DataReaderMapper <T>(result);
while (result.Read())
{
list.Add(mapper.MapFrom(result));
}
sw.Stop();
logger?.LogInformation($"Executed ({sw.ElapsedMilliseconds}ms)");
logger?.LogInformation($"{query}");
return(list);
}
}
}
public static IEnumerable <T> Execute <T>(this IBaitkmDbContext context, string query, params SqlParameter[] sqlParams) where T : class, IStoredProcedureResponse, new()
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
if (sqlParams != null)
{
command.Parameters.AddRange(sqlParams);
}
using (context.Database.OpenConnectionAsync())
{
using (var reader = command.ExecuteReader())
{
if (!reader.Any())
{
yield break;
}
var mapper = new DataReaderMapper <T>(reader);
do
{
yield return(mapper.MapFrom(reader));
} while (reader.Read());
}
}
}
}
public static async Task <IEnumerable <T> > FromSqlAsync <T>(this DbContext context, string query, List <SqlParameter> sqlParams)
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
if (sqlParams != null)
{
command.Parameters.AddRange(sqlParams.ToArray());
}
await context.Database.OpenConnectionAsync();
using (var result = await command.ExecuteReaderAsync())
{
var list = new List <T>();
var mapper = new DataReaderMapper <T>(result);
while (await result.ReadAsync())
{
list.Add(mapper.MapFrom(result));
}
return(list);
}
}
}
