/// <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); } } }