public static async Task <IList <T> > SqlQuery <T>(this DbContext db, string sql, params object[] parameters) where T : class
{
using (var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection()))
{
return(await db2.Query <T>().FromSql(sql, parameters).ToListAsync());
}
}
public static async Task <T[]> SqlQuery <T>(this DbContext db, string sql, params object[] parameters) where T : class
{
using (var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection(), db.Database.CurrentTransaction))
{
return(await db2.Set <T>().FromSqlRaw(sql, parameters).ToArrayAsync());
}
}
/// <summary>
/// 使用给定的参数执行给定的查询;
/// .Net472 下类型为 DbRawSqlQuery
/// .coreapp 下类型为 IQueryable,
/// 使用2者向上的公共类型 IEnumerable
/// </summary>
public static IEnumerable <T> SqlQuery <T>(this DatabaseFacade database, string sql, params object[] parameters)
where T : class
{
using (var db2 = new ContextForQueryType <T>(database.GetDbConnection()))
{
return(db2.Query <T>().FromSql(sql, parameters));
}
}
private static async Task <List <T> > SqlQueryValueInternalAsync <T>(this DbContext db, string sql, object[] parameters = null, CancellationToken cancellationToken = default) // where T : class
{
using var db2 = new ContextForQueryType <ValueReturn <T> >(db.Database.GetDbConnection(), db.Database.CurrentTransaction);
db2.Database.SetCommandTimeout(db.Database.GetCommandTimeout());
var result = await db2.Set <ValueReturn <T> >().FromSqlRaw(sql, parameters).ToListAsync(cancellationToken).ConfigureAwait(false);
return(result.Select(v => v.Value).ToList());
}
public static async Task <IList <T> > SqlQueryAsync <T>(this DbContext db, string sql, params object[] parameters) where T : class
{
var concurrencyDetector = db.Database.GetService <IConcurrencyDetector>();
using (var concurrency = await concurrencyDetector.EnterCriticalSectionAsync(CancellationToken.None))
using (var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection()))
{
return(await db2.Query <T>().FromSql(sql, parameters).ToListAsync());
}
}
/// <summary>
/// Creates a raw SQL query that will return elements of the given generic type.
/// The type can be any type that has properties that match the names of the columns returned
/// from the query.
///
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.SqlQuery<Post>("SELECT * FROM dbo.Posts WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.
/// context.Database.SqlQuery<Post>("SELECT * FROM dbo.Posts WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <typeparam name="TElement"> The type of object returned by the query. </typeparam>
/// <param name="sql"> The SQL query string. </param>
/// <param name="parameters">
/// The parameters to apply to the SQL query string.
/// </param>
/// <returns>
/// A <see cref="List{T}" /> object that will contain the result of the query.
/// </returns>
public static async Task <List <T> > SqlQueryAsync <T>(this DbContext db, string sql, object[] parameters = null, CancellationToken cancellationToken = default) where T : class
{
if (parameters is null)
{
parameters = new object[] { };
}
using (var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection(), db.Database.CurrentTransaction))
{
db2.Database.SetCommandTimeout(db.Database.GetCommandTimeout());
return(await db2.Set <T>().FromSqlRaw(sql, parameters).ToListAsync(cancellationToken));
}
}
private static async Task <List <T> > SqlQueryInternalAsync <T>(this DbContext db, string sql, object[] parameters = null, CancellationToken cancellationToken = default)
where T : class
{
if (parameters is null)
{
parameters = Array.Empty <object>();
}
using var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection(), db.Database.CurrentTransaction);
db2.Database.SetCommandTimeout(db.Database.GetCommandTimeout());
return(await db2.Set <T>().FromSqlRaw(sql, parameters).ToListAsync(cancellationToken).ConfigureAwait(false));
}
public static async Task <IList <TResultSet> > SqlQueryAsync <TResultSet>(this DbContext dbContext, string sql, IEnumerable <SqlParameter> sqlParameters) where TResultSet : class
{
try
{
using (var dbContextForQueryTypeObj = new ContextForQueryType <TResultSet>(dbContext.Database.GetDbConnection()))
{
return(await dbContextForQueryTypeObj.Set <TResultSet>().FromSqlRaw(sql, sqlParameters.Cast <Object>().ToArray()).ToListAsync());
}
}
catch
{
throw;
}
}
public static async Task <IList <TResultSet> > SqlQueryAsync <TResultSet>(this DbContext dbContext, string sql) where TResultSet : class
{
try
{
using (var dbContextForQueryTypeObj = new ContextForQueryType <TResultSet>(dbContext.Database.GetDbConnection()))
{
return(await dbContextForQueryTypeObj.Set <TResultSet>().FromSqlRaw(sql).ToListAsync());
}
}
catch
{
throw;
}
}
public static async Task <T[]> SqlQueryAsync <T>(this DbContext db, string sql, object[] parameters = null, CancellationToken cancellationToken = default) where T : class
{
parameters ??= new object[] { };
if (typeof(T).GetProperties().Any())
{
using (var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection(), db.Database.CurrentTransaction))
{
return(await db2.Set <T>().FromSqlRaw(sql, parameters).ToArrayAsync(cancellationToken));
}
}
else
{
await db.Database.ExecuteSqlRawAsync(sql, parameters, cancellationToken);
return(default);
/// <summary>
/// Creates a raw SQL query that will return elements of the given generic type.
/// The type must be a simple primitive type. In additon, the name of the returned type in the SQL statement must be 'Value'
///
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.SqlQuery<Post>("SELECT Id as Value FROM dbo.Posts WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.
/// context.Database.SqlQuery<Post>("SELECT Name AS Value FROM dbo.Posts WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <typeparam name="TElement"> The type of object returned by the query. </typeparam>
/// <param name="sql"> The SQL query string. </param>
/// <param name="parameters">
/// The parameters to apply to the SQL query string.
/// </param>
/// <returns>
/// A <see cref="List{T}" /> object that will contain the result of the query.
/// </returns>
public static async Task <List <T> > SqlQueryValueAsync <T>(this DbContext db, string sql, object[] parameters = null, CancellationToken cancellationToken = default) // where T : class
{
if (parameters is null)
{
parameters = new object[] { };
}
if (typeof(T).IsValueType || typeof(T) == typeof(string))
{
using (var db2 = new ContextForQueryType <ValueReturn <T> >(db.Database.GetDbConnection(), db.Database.CurrentTransaction))
{
db2.Database.SetCommandTimeout(db.Database.GetCommandTimeout());
var result = await db2.Set <ValueReturn <T> >().FromSqlRaw(sql, parameters).ToListAsync(cancellationToken);
return(result.Select(v => v.Value).ToList());
}
}
throw new NotSupportedException("Invalid operation, supplied type is not a value type");
}
public static IList <T> SqlQuery <T>(this DbContext db, string sql, params object[] parameters) where T : class
{
using var db2 = new ContextForQueryType <T>(db.Database.GetDbConnection());
return(db2.Set <T>().FromSqlRaw(sql, parameters).ToList());
}
