/// <summary>
/// Prepares the WHERE clause of the SQL query from the <see cref="Filter"/> argument: WHERE ABC
/// </summary>
private string PrepareWhereSql(QxCompilationContext ctx)
{
var ps = ctx.Parameters;
// WHERE is cached
if (_cachedWhere == null)
{
string whereFilter = null;
string whereInIds = null;
string whereInParentIds = null;
string whereInPropValues = null;
if (Filter != null)
{
whereFilter = Filter.Expression.CompileToBoolean(ctx);
}
if (Ids != null && Ids.Any())
{
if (Ids.Count() == 1)
{
string paramName = ps.AddParameter(Ids.Single());
whereInIds = $"[P].[Id] = @{paramName}";
}
else
{
var isIntKey = KeyType == KeyType.Int; // (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
var isStringKey = KeyType == KeyType.String;
// Prepare the ids table
DataTable idsTable = isIntKey ? RepositoryUtilities.DataTable(Ids.Select(id => new IdListItem {
Id = (int)id
}))
: isStringKey?RepositoryUtilities.DataTable(Ids.Select(id => new StringListItem {
Id = id.ToString()
}))
: throw new InvalidOperationException("Only string and Integer Ids are supported");
//
var idsTvp = new SqlParameter("@Ids", idsTable)
{
TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("Only string and Integer Ids are supported"),
SqlDbType = SqlDbType.Structured
};
ps.AddParameter(idsTvp);
whereInIds = $"[P].[Id] IN (SELECT Id FROM @Ids)";
}
}
if (ParentIds != null)
{
if (!ParentIds.Any())
{
if (IncludeRoots)
{
whereInParentIds = $"[P].[ParentId] IS NULL";
}
}
else if (ParentIds.Count() == 1)
{
string paramName = ps.AddParameter(ParentIds.Single());
whereInParentIds = $"[P].[ParentId] = @{paramName}";
if (IncludeRoots)
{
whereInParentIds += " OR [P].[ParentId] IS NULL";
}
}
else
{
var isIntKey = KeyType == KeyType.Int; // (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
var isStringKey = KeyType == KeyType.String;
// Prepare the data table
var parentIdsTable = new DataTable();
string idName = "Id";
var idType = KeyType switch
{
KeyType.String => typeof(string),
KeyType.Int => typeof(int),
_ => throw new InvalidOperationException("Bug: Only string and Integer ParentIds are supported"),
};
var column = new DataColumn(idName, idType);
if (isStringKey)
{
column.MaxLength = 450; // Just for performance
}
parentIdsTable.Columns.Add(column);
foreach (var id in ParentIds.Where(e => e != null))
{
DataRow row = parentIdsTable.NewRow();
row[idName] = id;
parentIdsTable.Rows.Add(row);
}
// Prepare the TVP
var parentIdsTvp = new SqlParameter("@ParentIds", parentIdsTable)
{
TypeName = KeyType == KeyType.Int ? "[dbo].[IdList]" : KeyType == KeyType.String ? "[dbo].[StringList]" : throw new InvalidOperationException("Bug: Only string and Integer ParentIds are supported"),
SqlDbType = SqlDbType.Structured
};
ps.AddParameter(parentIdsTvp);
whereInParentIds = $"[P].[ParentId] IN (SELECT Id FROM @ParentIds)";
if (IncludeRoots)
{
whereInParentIds += " OR [P].[ParentId] IS NULL";
}
}
}
if (!string.IsNullOrWhiteSpace(PropName) && Values != null && Values.Any())
{
var propDesc = ResultDescriptor.Property(PropName);
var propType = propDesc.Type;
var isIntKey = propType == typeof(int?) || propType == typeof(int);
var isStringKey = propType == typeof(string);
// Prepare the ids table
DataTable valuesTable =
isStringKey ? RepositoryUtilities.DataTable(Values.Select(id => new StringListItem {
Id = id.ToString()
})) :
isIntKey?RepositoryUtilities.DataTable(Values.Select(id => new IdListItem {
Id = (int)id
})) :
throw new InvalidOperationException("Only string and Integer Ids are supported");
var valuesTvp = new SqlParameter("@Values", valuesTable)
{
TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("Only string and Integer values are supported"),
SqlDbType = SqlDbType.Structured
};
ps.AddParameter(valuesTvp);
whereInPropValues = $"[P].[{propDesc.Name}] IN (SELECT Id FROM @Values)";
}
// The final WHERE clause (if any)
string whereSql = "";
var clauses = new List <string> {
whereFilter, whereInIds, whereInParentIds, whereInPropValues
}.Where(e => !string.IsNullOrWhiteSpace(e));
if (clauses.Any())
{
whereSql = clauses.Aggregate((c1, c2) => $"{c1}) AND ({c2}");
whereSql = $"WHERE ({whereSql})";
}
_cachedWhere = whereSql;
}
return(_cachedWhere);
}
/// <summary>
/// Prepares the WHERE clause of the SQL query from the <see cref="Filter"/> argument: WHERE ABC
/// </summary>
private string PrepareWhere(Func <Type, string> sources, JoinTree joinTree, SqlStatementParameters ps, int userId, DateTime?userToday)
{
// WHERE is cached
if (_cachedWhere == null)
{
string whereFilter = null;
string whereInIds = null;
string whereInParentIds = null;
if (Filter != null)
{
whereFilter = QueryTools.FilterToSql(Filter, sources, ps, joinTree, userId, userToday);
}
if (Ids != null && Ids.Count() >= 1)
{
if (Ids.Count() == 1)
{
string paramName = ps.AddParameter(Ids.Single());
whereInIds = $"[P].[Id] = @{paramName}";
}
else
{
var isIntKey = (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
var isStringKey = KeyType == typeof(string);
// Prepare the ids table
DataTable idsTable = isIntKey ? RepositoryUtilities.DataTable(Ids.Select(id => new { Id = (int)id }))
: isStringKey?RepositoryUtilities.DataTable(Ids.Select(id => new { Id = id.ToString() }))
: throw new InvalidOperationException("Only string and Integer Ids are supported");
//
var idsTvp = new SqlParameter("@Ids", idsTable)
{
TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("Only string and Integer Ids are supported"),
SqlDbType = SqlDbType.Structured
};
ps.AddParameter(idsTvp);
whereInIds = $"[P].[Id] IN (SELECT Id FROM @Ids)";
}
}
if (ParentIds != null)
{
if (!ParentIds.Any())
{
if (IncludeRoots)
{
whereInParentIds = $"[P].[ParentId] IS NULL";
}
}
else if (ParentIds.Count() == 1)
{
string paramName = ps.AddParameter(ParentIds.Single());
whereInParentIds = $"[P].[ParentId] = @{paramName}";
if (IncludeRoots)
{
whereInParentIds += " OR [P].[ParentId] IS NULL";
}
}
else
{
var isIntKey = (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
var isStringKey = KeyType == typeof(string);
// Prepare the data table
DataTable parentIdsTable = new DataTable();
string propName = "Id";
var column = new DataColumn(propName, KeyType);
if (isStringKey)
{
column.MaxLength = 450; // Just for performance
}
parentIdsTable.Columns.Add(column);
foreach (var id in ParentIds.Where(e => e != null))
{
DataRow row = parentIdsTable.NewRow();
row[propName] = id;
parentIdsTable.Rows.Add(row);
}
// Prepare the TVP
var parentIdsTvp = new SqlParameter("@ParentIds", parentIdsTable)
{
TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("Only string and Integer ParentIds are supported"),
SqlDbType = SqlDbType.Structured
};
ps.AddParameter(parentIdsTvp);
whereInParentIds = $"[P].[ParentId] IN (SELECT Id FROM @ParentIds)";
if (IncludeRoots)
{
whereInParentIds += " OR [P].[ParentId] IS NULL";
}
}
}
// The final WHERE clause (if any)
string whereSql = "";
var clauses = new List <string> {
whereFilter, whereInIds, whereInParentIds
}.Where(e => e != null);
if (clauses.Any())
{
whereSql = clauses.Aggregate((c1, c2) => $"{c1}) AND ({c2}");
whereSql = $"WHERE ({whereSql})";
}
_cachedWhere = whereSql;
}
return(_cachedWhere);
}