/// <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)
{
string whereSql = QueryTools.FilterToSql(Filter, sources, ps, joinTree, userId, userToday) ?? "";
// Add the "WHERE" keyword
if (!string.IsNullOrEmpty(whereSql))
{
whereSql = "WHERE " + whereSql;
}
return(whereSql);
}
/// <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);
}