public QueryInfo BuildQuery <TValue>(dynamic parameters) where TValue : class
{
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingFor <TValue>();
var table = mapping.DataSource;
var count = mapping.CountField;
return(new QueryInfo($"SELECT COUNT({count}) AS {count} FROM {table};"));
}
public IEnumerable <TValue> BuildItems <TValue>(dynamic parameters, IDataReader dataSource)
where TValue : class, new()
{
Guard.ThrowIfNull <string>("parameters", parameters);
Guard.ThrowIfNull("dataSource", dataSource);
IEnumerable <TableObjectMapping> tableObjectMappings = parameters.TableObjectMappings;
IDictionary <object, TValue> parents = new Dictionary <object, TValue>();
var type = typeof(TValue);
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingFor <TValue>();
var primaryKeyMappings = mapping.PropertyMappings
.Where(p => p.IsPrimaryKey);
var primaryKeyProperties = primaryKeyMappings
.Select(p => type.GetProperty(p.PropertyName))
.ToList();
if (primaryKeyProperties.IsNullOrEmpty())
{
throw new InvalidOperationException("Type {0} either has no FieldMetaDataAttributes or no Primary Key defined."
.FormatString(type.ToString()));
}
if (primaryKeyProperties.Count() > 1)
{
throw new InvalidOperationException("Type {0} has too many primary keys defined."
.FormatString(type.ToString()));
}
while (dataSource.Read())
{
BuildParent(dataSource, parents, tableObjectMappings, primaryKeyProperties.First());
}
return(parents.Values.ToList());
}
public QueryInfo BuildQuery <TValue>(dynamic parameters = null) where TValue : class
{
PagingInfo pagingInfo = parameters?.PagingInfo;
IEnumerable <string> desiredFields = parameters?.DesiredFields;
Expression <Func <TValue, bool> > predicate = parameters?.Predicate;
bool canDirtyRead = parameters?.CanDirtyRead;
bool includeParameters = parameters?.IncludeParameters;
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingFor <TValue>();
var queryBuilder = new StringBuilder();
var fields = FieldHelper.BuildFields <TValue>(desiredFields);
var orderByClause = BuildPagedOrderByClause(mapping);
var countClause = BuildCountClause(mapping);
var whereClause = String.Empty;
var databaseParameters = new List <IDbDataParameter>();
if (predicate.IsNotNull())
{
var container = _predicateBuilder.BuildContainer(predicate, typeof(TValue), includeParameters);
whereClause = container.WhereClause;
if (includeParameters)
{
databaseParameters.AddRange(container.Parameters.ToSafeList());
}
}
queryBuilder.Append("SET @sortColumn = LOWER(@sortColumn); ");
queryBuilder.Append("SET @sortOrder = LOWER(@sortOrder); ");
queryBuilder.Append(BuildCommonTableExpression(fields, orderByClause, countClause, canDirtyRead, whereClause));
queryBuilder.Append("SELECT ");
queryBuilder.Append(fields.FieldMappings
.Select(f => f.Value.FieldName)
.ToDelimitedString(", "));
queryBuilder.Append(", ");
queryBuilder.Append("[TotalRecords], ");
queryBuilder.Append("([TotalRecords] + @rowsPerPage - 1) / @rowsPerPage AS NumberOfPages ");
queryBuilder.AppendFormat("FROM {0} ", ItemNames.SortedPageCteName);
queryBuilder.AppendFormat(
"WHERE {0}.RowNumber BETWEEN ((@pageNumber - 1) * @rowsPerPage) + 1 AND @rowsPerPage * @pageNumber ", ItemNames.SortedPageCteName);
if (whereClause.IsNotNullOrEmpty())
{
queryBuilder.Append(" ");
queryBuilder.Append("AND {0} ".FormatString(whereClause));
}
queryBuilder.Append(orderByClause);
queryBuilder.Append(";");
databaseParameters.Add(new SqlParameter(Parameters.RowsPerPage, SqlDbType.Int)
{
Value = pagingInfo.RowsPerPage
});
databaseParameters.Add(new SqlParameter(Parameters.PageNumber, SqlDbType.Int)
{
Value = pagingInfo.PageNumber
});
databaseParameters.Add(new SqlParameter(Parameters.SortColumn, SqlDbType.NVarChar)
{
Value = pagingInfo.SortColumn
});
databaseParameters.Add(new SqlParameter(Parameters.SortOrder, SqlDbType.NVarChar)
{
Value = pagingInfo.SortOrder
});
return(new QueryInfo(queryBuilder.ToString().Trim(), fields, databaseParameters));
}
protected override Expression VisitMethodCall(MethodCallExpression m)
{
if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
{
Visit(m.Arguments[0]);
var lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
Visit(lambda.Body);
return(m);
}
if (m.Method.Name == "Take")
{
if (ParseTakeExpression(m))
{
Expression nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
}
else if (m.Method.Name == "Skip")
{
if (ParseSkipExpression(m))
{
Expression nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
}
else if (m.Method.Name == "OrderBy")
{
if (ParseOrderByExpression(m, "ASC"))
{
Expression nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
}
else if (m.Method.Name == "OrderByDescending")
{
if (ParseOrderByExpression(m, "DESC"))
{
Expression nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
}
else if (m.Method.Name == "StartsWith")
{
var field = ParseStartsWithExpression(m);
var memberExpression = (MemberExpression)m.Object;
var property = memberExpression.Member as PropertyInfo;
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingForType(_declaringType);
var propertyMapping = mapping.PropertyMappings
.FirstOrDefault(p => p.PropertyName == property.Name);
_queryBuilder.Append($"{BuildFieldName(propertyMapping.Field)} LIKE ('{field}%')");
var nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
else if (m.Method.Name == "Contains")
{
var field = ParseStartsWithExpression(m);
var memberExpression = (MemberExpression)m.Object;
var property = memberExpression.Member as PropertyInfo;
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingForType(_declaringType);
var propertyMapping = mapping.PropertyMappings
.FirstOrDefault(p => p.PropertyName == property.Name);
_queryBuilder.Append($"{BuildFieldName(propertyMapping.Field)} LIKE ('%{field}%')");
var nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
else if (m.Method.Name == "EndsWith")
{
var field = ParseStartsWithExpression(m);
var memberExpression = (MemberExpression)m.Object;
var property = memberExpression.Member as PropertyInfo;
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingForType(_declaringType);
var propertyMapping = mapping.PropertyMappings
.FirstOrDefault(p => p.PropertyName == property.Name);
_queryBuilder.Append($"{BuildFieldName(propertyMapping.Field)} LIKE ('%{field}')");
var nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
else if (m.Method.Name == "Equals")
{
var field = ParseEqualsExpression(m);
var memberExpression = (MemberExpression)m.Object;
var property = memberExpression.Member as PropertyInfo;
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingForType(_declaringType);
var propertyMapping = mapping.PropertyMappings
.FirstOrDefault(p => p.PropertyName == property.Name);
// Handle date type.
if (propertyMapping.IsNull() &&
((PropertyInfo)((MemberExpression)memberExpression.Expression).Member).PropertyType == typeof(DateTime) &&
(property.Name == "Day" || property.Name == "Month" || property.Name == "Year"))
{
var parentMemberExpression = memberExpression.Expression as MemberExpression;
var parentProperty = parentMemberExpression.Member as PropertyInfo;
var fieldName = GetFieldName(parentProperty);
_queryBuilder.Append($"{property.Name.ToUpper()}({BuildFieldName(fieldName)}) = {BuildField(field)}");
}
else
{
var fieldValue = BuildField(field);
if (fieldValue.IsNullOrEmpty())
{
_queryBuilder.AppendFormat("{0} IS NULL", BuildFieldName(propertyMapping.Field));
}
else if (propertyMapping.Field.IsNotNull())
{
_queryBuilder.AppendFormat("{0} = {1}",
BuildFieldName(propertyMapping.Field), BuildField(field));
}
}
var nextExpression = m.Arguments[0];
return(Visit(nextExpression));
}
throw new NotSupportedException($"The method '{m.Method.Name}' is not supported");
}