/// <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); }