/// <summary>
/// Executes the specified stored procedure against a database
/// and returns an enumerable of T representing the data returned.
/// </summary>
/// <typeparam name="T">Type of the data returned from the stored procedure.</typeparam>
/// <param name="database">The database to execute against.</param>
/// <param name="storedProcedure">The stored procedure to execute.</param>
/// <returns></returns>
public static IEnumerable <T> ExecuteStoredProcedure <T>(this DatabaseFacade database, object storedProcedure) where T : class
{
var contextField = database.GetType().GetField("_context", BindingFlags.Instance | BindingFlags.NonPublic);
var context = (DbContext)contextField.GetValue(database);
var entityType = FindModelEntityType(context, typeof(T));
if (entityType == null)
{
return(database.InternalExecuteStoredProcedure <T>(storedProcedure));
}
if (storedProcedure == null)
{
throw new ArgumentNullException("storedProcedure");
}
var info = StoredProcedureParser.BuildStoredProcedureInfo(storedProcedure);
List <T> result = context.Set <T>().FromSqlRaw(info.Sql, info.SqlParameters).AsNoTracking().ToList();
SetOutputParameterValues(info.SqlParameters, storedProcedure);
return(result);
}
/// <summary>
/// Executes the specified stored procedure against a database asynchronously
/// and returns an enumerable of T representing the data returned.
/// </summary>
/// <typeparam name="T">Type of the data returned from the stored procedure.</typeparam>
/// <param name="database">The database to execute against.</param>
/// <param name="storedProcedure">The stored procedure to execute.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns></returns>
public static async Task <IEnumerable <T> > ExecuteStoredProcedureAsync <T>(this DatabaseFacade database, object storedProcedure, CancellationToken cancellationToken = default) where T : class
{
var contextField = database.GetType().GetField("_context", BindingFlags.Instance | BindingFlags.NonPublic);
var context = (DbContext)contextField.GetValue(database);
var entityType = FindModelEntityType(context, typeof(T));
// TODO: Review this code which can make some existing code crashing
if (entityType == null || true)
{
return(await database.InternalExecuteStoredProcedureAsync <T>(storedProcedure).ConfigureAwait(false));
}
if (storedProcedure == null)
{
throw new ArgumentNullException("storedProcedure");
}
var info = StoredProcedureParser.BuildStoredProcedureInfo(storedProcedure);
List <T> result = await context.Set <T>().FromSqlRaw(info.Sql, info.SqlParameters).AsNoTracking().ToListAsync(cancellationToken).ConfigureAwait(false);
SetOutputParameterValues(info.SqlParameters, storedProcedure);
return(result);
}
/// <summary>
/// Executes the specified stored procedure against a database.
/// </summary>
/// <param name="database">The database to execute against.</param>
/// <param name="storedProcedure">The stored procedure to execute.</param>
public static void ExecuteStoredProcedure(this DatabaseFacade database, object storedProcedure)
{
if (storedProcedure == null)
{
throw new ArgumentNullException("storedProcedure");
}
var info = StoredProcedureParser.BuildStoredProcedureInfo(storedProcedure);
database.ExecuteSqlRaw(info.Sql, info.SqlParameters);
SetOutputParameterValues(info.SqlParameters, storedProcedure);
}
internal static IEnumerable <T> InternalExecuteStoredProcedure <T>(this DatabaseFacade database, object storedProcedure)
{
if (storedProcedure == null)
{
throw new ArgumentNullException("storedProcedure");
}
List <T> result = new List <T>();
var info = StoredProcedureParser.BuildStoredProcedureInfo(storedProcedure);
// from : https://github.com/Fodsuk/EntityFrameworkExtras/pull/23/commits/dce354304aa9a95750f7d2559d1b002444ac46f7
using (var command = database.GetDbConnection().CreateCommand())
{
command.CommandText = info.Sql;
int?commandTimeout = database.GetCommandTimeout();
if (commandTimeout.HasValue)
{
command.CommandTimeout = commandTimeout.Value;
}
command.CommandType = CommandType.Text;
command.Parameters.AddRange(info.SqlParameters);
command.Transaction = database.CurrentTransaction?.GetDbTransaction();
database.OpenConnection();
using (var resultReader = command.ExecuteReader())
{
T obj = default(T);
while (resultReader.Read())
{
obj = Activator.CreateInstance <T>();
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
var val = GetValue(resultReader, prop.Name);
if (!object.Equals(val, DBNull.Value))
{
prop.SetValue(obj, val, null);
}
}
result.Add(obj);
}
}
}
SetOutputParameterValues(info.SqlParameters, storedProcedure);
return(result);
}
/// <summary>
/// Executes the specified stored procedure against a database
/// and returns an enumerable of T representing the data returned.
/// </summary>
/// <typeparam name="T">Type of the data returned from the stored procedure.</typeparam>
/// <param name="database">The database to execute against.</param>
/// <param name="storedProcedure">The stored procedure to execute.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns></returns>
public static async Task ExecuteStoredProcedureAsync(this DatabaseFacade database, object storedProcedure, CancellationToken cancellationToken = default)
{
if (storedProcedure == null)
{
throw new ArgumentNullException("storedProcedure");
}
var info = StoredProcedureParser.BuildStoredProcedureInfo(storedProcedure);
var listParam = info.SqlParameters != null && info.SqlParameters.Length > 0
? info.SqlParameters.ToList()
: null;
var task = await database.ExecuteSqlRawAsync(info.Sql, listParam, cancellationToken).ConfigureAwait(false);
SetOutputParameterValues(info.SqlParameters, storedProcedure);
}
