public static IQueryable <T> CreateContainsIdsQuery <T>(
this DbSet <T> dbSet,
IList ids,
string additionalWhereClause = "",
string queryIdColumnName = "Id",
string tableNameOverride = null)
where T : class
{
if (ids.Count > 0)
{
var values = new StringBuilder();
values.AppendFormat("'{0}'", ids[0]);
for (int i = 1; i < ids.Count; i++)
{
values.AppendFormat(", '{0}'", ids[i]);
}
// TODO: Refactor this to use SQL parameterization; need to ensure that additional 'where'
// clauses can be passed in to this method.
var whereClause = queryIdColumnName;
if (!string.IsNullOrEmpty(additionalWhereClause))
{
whereClause = additionalWhereClause + " AND " + whereClause;
}
var tableName = tableNameOverride ?? typeof(T).Name.Pluralize();
var sql = $"SELECT * FROM {tableName} WHERE {whereClause} IN ({values})";
return(dbSet.FromSqlRaw(sql));
}
else
{
return(Enumerable.Empty <T>().AsAsyncQueryable());
}
}
public IQueryable <TEntity> Join(DbSet <TEntity> rootQuery, List <object[]> keys, char delimiter)
{
ValidateCompositeKeys(keys);
return(rootQuery.FromSqlRaw($@"
DECLARE @Keys TABLE ({string.Join(", ", PrimaryKey.Keys.Select(k => $"{k.ColumnName} {k.ColumnType}"))})
{ CreateBatchInsertStatement("INSERT INTO @Keys VALUES", keys) }
SELECT a.*
FROM dbo.{TableName} a
JOIN @Keys k ON {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" a.{k.ColumnName} = k.{k.ColumnName}"))}"));
}
public IQueryable <TEntity> Join(DbSet <TEntity> rootQuery, List <object[]> keys, char delimiter)
{
ValidateCompositeKeys(keys);
return(rootQuery.FromSqlRaw($@"
CREATE TEMP TABLE _Keys ({string.Join(", ", PrimaryKey.Keys.Select(k => $"{k.ColumnName} {k.ColumnType}"))});
INSERT INTO _Keys VALUES {string.Join(",", keys.Select(k => $@"
({string.Join(", ", k.Select(val => StringifyKeyVal(val)))})"))};
SELECT a.*
FROM {TableName} a
JOIN _Keys k ON {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" a.{k.ColumnName} = k.{k.ColumnName}"))}"));
}
/// <summary>
/// Uses raw SQL queries to fetch the specified <typeparamref name="TEntity" /> data.
/// </summary>
/// <param name="sql">The raw SQL.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>An <see cref="IQueryable{TEntity}" /> that contains elements that satisfy the condition specified by raw SQL.</returns>
public IQueryable <TEntity> FromSql(string sql, params object[] parameters) => _dbSet.FromSqlRaw(sql, parameters);