private static string GetSelectSql <TEntity>(ObjectQuery <TEntity> query, EntityMap entityMap, DbCommand command)
where TEntity : class
{
// TODO change to esql?
// changing query to only select keys
var selector = new StringBuilder(50);
selector.Append("new(");
foreach (var propertyMap in entityMap.KeyMaps)
{
if (selector.Length > 4)
{
selector.Append((", "));
}
selector.Append(propertyMap.PropertyName);
}
selector.Append(")");
var selectQuery = DynamicQueryable.Select(query, selector.ToString());
var objectQuery = selectQuery as ObjectQuery;
if (objectQuery == null)
{
throw new ArgumentException("The query must be of type ObjectQuery.", "query");
}
string innerJoinSql = objectQuery.ToTraceString();
// create parameters
foreach (var objectParameter in objectQuery.Parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = objectParameter.Name;
parameter.Value = objectParameter.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
return(innerJoinSql);
}
public IQueryable Select(string predicate, params object[] values)
{
return(DynamicQueryable.Select(Items.AsQueryable(), predicate, values));
}
private static string GetSelectSql <TEntity>(
DbContext dbContext,
IAbpSession abpSession,
ObjectQuery <TEntity> query,
EntityMap entityMap,
DbCommand command
) where TEntity : class
{
// TODO change to esql?
// changing query to only select keys
var selector = new StringBuilder(50);
selector.Append("new(");
foreach (var propertyMap in entityMap.KeyMaps)
{
if (selector.Length > 4)
{
selector.Append((", "));
}
selector.Append(propertyMap.PropertyName);
}
selector.Append(")");
var selectQuery = DynamicQueryable.Select(query, selector.ToString());
var objectQuery = selectQuery as ObjectQuery;
if (objectQuery == null)
{
throw new ArgumentException("The query must be of type ObjectQuery.", nameof(query));
}
var innerJoinSql = objectQuery.ToTraceString();
// create parameters
foreach (var objectParameter in objectQuery.Parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = objectParameter.Name;
parameter.Value = objectParameter.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
var parameters = new List <SqlParameter>();
var reg = new Regex(@"([\[?\w +\]?\.\[?\w+\]?]+(?:\s*\=\s*\@\w+))", RegexOptions.Compiled);
var matches = reg.Matches(innerJoinSql).Cast <Match>().Where(x =>
(
x.Value.Contains("TenantId") ||
x.Value.Contains("IsDeleted")
) &&
x.Value.Contains("@DynamicFilterParam_")).ToList();
var abpContext = dbContext as DbContext;
if (abpContext != null)
{
foreach (var item in matches)
{
if (item.Value.Contains("TenantId"))
{
var dynamicFilterParam = item.Value.Split('=').ElementAt(1).Trim();
// TenantId, Accourding to TenantId to filter records
var para1 = new SqlParameter(dynamicFilterParam, SqlDbType.Int)
{
Value = abpSession.TenantId
};
var dfp = dynamicFilterParam.Split('_').ElementAt(1);
string strDfp;
if (dfp.Length == 6)
{
var number = int.Parse(dfp) + 1;
strDfp = $"{number:D6}";
}
else
{
strDfp = (int.Parse(dfp) + 1).ToString();
}
var para2 = new SqlParameter("@DynamicFilterParam_" + strDfp, SqlDbType.Bit);
// if true enabled tenant filter; if false disabled tenant filter
if (abpContext.IsFilterEnabled(AbpDataFilters.MayHaveTenant) ||
abpContext.IsFilterEnabled(AbpDataFilters.MustHaveTenant))
{
para2.Value = DBNull.Value;
}
else
{
para2.Value = 1;//disabled tenant filter
}
if (parameters.All(x => x.ParameterName != para1.ParameterName))
{
parameters.Add(para1);
}
if (parameters.All(x => x.ParameterName != para2.ParameterName))
{
parameters.Add(para2);
}
}
if (item.Value.Contains("IsDeleted"))
{
var dynamicFilterParam = item.Value.Split('=').ElementAt(1).Trim();
// IsDeleted = false, It will get records that do not soft deleted records.
var para1 = new SqlParameter(dynamicFilterParam, SqlDbType.Bit)
{
Value = false
};
var dfp = dynamicFilterParam.Split('_').ElementAt(1);
string strDfp;
if (dfp.Length == 6)
{
var number = int.Parse(dfp) + 1;
strDfp = $"{number:D6}";
}
else
{
strDfp = (int.Parse(dfp) + 1).ToString();
}
var para2 = new SqlParameter("@DynamicFilterParam_" + strDfp, SqlDbType.Bit);
// if true enabled SoftDelete; if false disabled SoftDelete
if (abpContext.IsFilterEnabled(AbpDataFilters.SoftDelete))
{
para2.Value = DBNull.Value;
}
else
{
para2.Value = 1;
}
if (parameters.All(x => x.ParameterName != para1.ParameterName))
{
parameters.Add(para1);
}
if (parameters.All(x => x.ParameterName != para2.ParameterName))
{
parameters.Add(para2);
}
}
}
}
command.Parameters.AddRange(parameters.ToArray());
return(innerJoinSql);
}
public IQueryable Select(IQueryable source, string selector, params object[] values)
{
return(DynamicQueryable.Select(source, selector, values));
}
public DynamicQueryable BuildQuery(
IQueryable queryable,
IEnumerable <string> columns,
NodeBase filter,
IEnumerable <OrderItem> sort
)
{
var query = new DynamicQueryable(queryable);
//Get all predicate expressions
var predicateMembers =
filter != null ?
new DynamicProjectionPredicateMemberVisitor().Visit(filter) :
new List <string>();
//Determine target of predicate (source or projection)
var predicateTarget =
predicateMembers.All(x => this.CompiledConfiguration.CompiledMembers.ContainsKey(x)) ?
DynamicProjectionOperationTarget.Source : DynamicProjectionOperationTarget.Projection;
//Determine target of sort (source or projection)
var sortTarget =
(sort ?? Enumerable.Empty <OrderItem>())
.All(x => CompiledConfiguration.CompiledMembers.ContainsKey(x.Expression)) ?
DynamicProjectionOperationTarget.Source : DynamicProjectionOperationTarget.Projection;
//Apply predicate on source if applicable
if (filter != null && predicateTarget == DynamicProjectionOperationTarget.Source)
{
query = query.Where(
new DynamicProjectionPredicateVisitor(this)
.VisitLambda(filter));
}
//Apply sort on source if applicable
if (sort != null && sortTarget == DynamicProjectionOperationTarget.Source)
{
query = sort.Aggregate(query, (acc, next) =>
{
var lambda =
Expression.Lambda(
CompiledConfiguration.CompiledMembers[next.Expression]
.Source.SourceExpression,
CompiledConfiguration.It);
return(next.IsDescending ?
query.OrderByDescending(lambda) :
query.OrderBy(lambda));
});
}
//Append predicate and sort columns to projection if needed
var selectorColumns = columns ?? this.CompiledConfiguration.CompiledMembers.Select(x => x.Value.Member.MemberInfo?.Name);
if (predicateTarget == DynamicProjectionOperationTarget.Projection)
{
selectorColumns = columns.Concat(predicateMembers);
}
if (sortTarget == DynamicProjectionOperationTarget.Projection)
{
selectorColumns = columns.Concat(sort.Select(x => x.Expression));
}
selectorColumns = selectorColumns.Distinct().ToList();
//Apply selector
query = query.Select(compiler.BuildSelector(selectorColumns));
//Apply predicate on projection if applicable
if (filter != null && predicateTarget == DynamicProjectionOperationTarget.Projection)
{
query = query.Where(
new ExpressionNodeVisitor()
.VisitLambda(filter, configuration.ProjectedType));
}
//Apply sort on projection if applicable
if (sort != null && predicateTarget == DynamicProjectionOperationTarget.Projection)
{
query.OrderBy(sort);
}
return(query);
}
//AuditTrail Related Extensions
public static SqlQueryData GetSelectQuery <TEntity>(this ObjectQuery <TEntity> query, EntityMap entityMap)
where TEntity : class
{
var sqlQueryData = new SqlQueryData();
// changing query to only select keys
var selector = new StringBuilder(50);
selector.Append("new(");
foreach (var propertyMap in entityMap.KeyMaps)
{
if (selector.Length > 4)
{
selector.Append((", "));
}
selector.Append(propertyMap.PropertyName);
}
//get security column in the select
string securityLevelColName = "";
if (entityMap.EntityType.GetProperty("SecurityLevelId") != null)
{
securityLevelColName = "SecurityLevelId";
}
if (entityMap.EntityType.GetProperty("FinalSecurityLevel") != null)
{
securityLevelColName = "FinalSecurityLevel";
}
if (!string.IsNullOrEmpty(securityLevelColName))
{
if (selector.Length > 4)
{
selector.Append((", "));
}
selector.Append(securityLevelColName);
}
selector.Append(")");
var selectQuery = DynamicQueryable.Select(query, selector.ToString());
var objectQuery = selectQuery as ObjectQuery;
if (objectQuery == null)
{
throw new ArgumentException("The query must be of type ObjectQuery.", "query");
}
sqlQueryData.QueryString = objectQuery.ToTraceString();
// create parameters
foreach (var objectParameter in objectQuery.Parameters)
{
sqlQueryData.Parameters.Add(objectParameter.Name, objectParameter.Value ?? DBNull.Value);
sqlQueryData.SqlParameters.Add(new SqlParameter()
{
ParameterName = objectParameter.Name, Value = objectParameter.Value ?? DBNull.Value
});
}
return(sqlQueryData);
}