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