Ejemplo n.º 1
0
        protected virtual void GenerateFilterForCondition(DbCommand cmd, ConditionLite condition, StringBuilder sb, ref int paramIndex, ref bool firstCondition, int indentation)
        {
            string parameterName = null;

            if (condition.Filter != null && condition.Filter.IsEmpty())
            {
                return;
            }
            if (firstCondition)
            {
                ;
            }
            else if (condition.LogicalOperator == LogicalOperatorLite.And)
            {
                sb.NewIndentedLine(indentation).Append("AND ");
            }
            else if (condition.LogicalOperator == LogicalOperatorLite.Or)
            {
                sb.NewIndentedLine(indentation).Append("OR ");
            }
            else
            {
                throw new NotImplementedException("Logical operator " + condition.LogicalOperator.ToString() + " not implemented");
            }
            firstCondition = false;

            if (condition.Filter != null)
            {
                sb.Append(GetFilter(cmd, ref paramIndex, condition.Filter, indentation, true));
                return;
            }

            IEnumerable   values       = condition.FieldValue as IEnumerable;
            IQueryBuilder queryBuilder = condition.SubQuery == null ? null : condition.SubQuery.QueryBuilder;

            if (condition.Operator == OperatorLite.In || condition.Operator == OperatorLite.NotIn)
            {
                if (values == null && queryBuilder == null)
                {
                    throw new ArgumentException("The value for In and NotIn operators must be enumerable or a subquery", "expression");
                }
                if (values != null)
                {
                    bool hasAnyValue = values.AreThereMoreThan(0);
                    if (!hasAnyValue)
                    {
                        if (condition.Operator == OperatorLite.In)
                        {
                            sb.Append(" 1=0");
                        }
                        else
                        {
                            sb.Append(" 1=1");
                        }
                        return;
                    }
                }
            }
            PropertyMetadata propertyMetadata = null;
            string           fieldName        = condition.FieldName;
            string           quotedColumnName = null;

            if (fieldName == null && condition.Operator != OperatorLite.Exists && condition.Operator != OperatorLite.NotExists)
            {
                throw new InvalidOperationException("Field Name must be not null for condition");
            }
            if (fieldName != null)
            {
                propertyMetadata = GetPropertyMetadata(this.QueryLite.EntityType, fieldName);
                quotedColumnName = this.QueryLite.DataService.EntityLiteProvider.StartQuote + propertyMetadata.SqlField.ColumnName + this.QueryLite.DataService.EntityLiteProvider.EndQuote;
                if (condition.Operator != OperatorLite.In && condition.Operator != OperatorLite.NotIn)
                {
                    sb.Append(quotedColumnName);
                }
            }

            if (condition.Operator == OperatorLite.IsNull)
            {
                sb.Append(" IS NULL");
                return;
            }
            if (condition.Operator == OperatorLite.IsNotNull)
            {
                sb.Append(" IS NOT NULL");
                return;
            }

            if (condition.Operator == OperatorLite.In || condition.Operator == OperatorLite.NotIn)
            {
                AddListCondition(cmd, condition, sb, ref paramIndex, ref indentation, values, queryBuilder, propertyMetadata, quotedColumnName);
                return;
            }
            else if (condition.Operator == OperatorLite.Exists || condition.Operator == OperatorLite.NotExists)
            {
                if (condition.Operator == OperatorLite.Exists)
                {
                    sb.Append("EXISTS (\n");
                }
                else
                {
                    sb.Append("NOT EXISTS (\n");
                }
                sb.Append(queryBuilder.GetSelectQuery(cmd, ref paramIndex, ++indentation));
                sb.NewIndentedLine(--indentation).Append(')');
                return;
            }

            var fieldReference = condition.FieldValue as FieldReference;

            if (fieldReference == null)
            {
                var parameter = CreateParameter(propertyMetadata, condition.FieldValue, ref paramIndex, out parameterName);
                cmd.Parameters.Add(parameter);
            }
            else
            {
                var              fieldReferenceEntityType       = fieldReference.Alias?.EntityType ?? this.QueryLite.EntityType;
                string           fieldReferenceName             = fieldReference.FieldName;
                PropertyMetadata fieldReferencePropertyMetadata = GetPropertyMetadata(fieldReferenceEntityType, fieldReferenceName);
                var              aliasName = fieldReference.Alias?.Name ?? this.QueryLite.Alias?.Name;
                if (!string.IsNullOrEmpty(aliasName))
                {
                    parameterName = aliasName + ".";
                }
                else
                {
                    parameterName = string.Empty;
                }
                parameterName += this.QueryLite.DataService.EntityLiteProvider.StartQuote + fieldReferencePropertyMetadata.SqlField.ColumnName + this.QueryLite.DataService.EntityLiteProvider.EndQuote;
            }


            switch (condition.Operator)
            {
            case OperatorLite.Contains:
                sb.Append(" LIKE " + QueryLite.DataService.EntityLiteProvider.Concat("'%'", parameterName, "'%'"));
                break;

            case OperatorLite.Equals:
                sb.Append(" = ").Append(parameterName);
                break;

            case OperatorLite.Greater:
                sb.Append(" > ").Append(parameterName);
                break;

            case OperatorLite.GreaterOrEquals:
                sb.Append(" >= ").Append(parameterName);
                break;

            case OperatorLite.Less:
                sb.Append(" < ").Append(parameterName);
                break;

            case OperatorLite.LessOrEquals:
                sb.Append(" <= ").Append(parameterName);
                break;

            case OperatorLite.NotContains:
                sb.Append(" NOT LIKE " + QueryLite.DataService.EntityLiteProvider.Concat("'%'", parameterName, "'%'"));
                break;

            case OperatorLite.NotEquals:
                sb.Append(" <> ").Append(parameterName);
                break;

            case OperatorLite.NotStartsWith:
                sb.Append(" NOT LIKE " + QueryLite.DataService.EntityLiteProvider.Concat(parameterName, "'%'"));
                break;

            case OperatorLite.StartsWith:
                sb.Append(" LIKE " + QueryLite.DataService.EntityLiteProvider.Concat(parameterName, "'%'"));
                break;

            case OperatorLite.IsDescendantOf:
                sb.Append(".IsDescendantOf(" + parameterName + ") = 1");
                break;

            case OperatorLite.IsChildOf:
                sb.Append(".GetAncestor(1) = " + parameterName);
                break;

            case OperatorLite.IsGrandChildOf:
                sb.Append(".GetAncestor(2) = " + parameterName);
                break;

            case OperatorLite.HierarchyLevelEquals:
                sb.Append(".GetLevel() = " + parameterName);
                break;

            case OperatorLite.STEquals:
                sb.Append(".STEquals(" + parameterName + ") = 1");
                break;

            case OperatorLite.STIntersects:
                sb.Append(".STIntersects(" + parameterName + ") = 1");
                break;

            case OperatorLite.STDistanceLess:
            case OperatorLite.STDistanceLessOrEquals:
                string pname = QueryLite.DataService.EntityLiteProvider.ParameterPrefix + "P" + paramIndex.ToString();
                cmd.Parameters.AddWithValue(pname, condition.Parameter);
                paramIndex++;
                sb.Append(".STDistance(" + parameterName + ") " + (condition.Operator == OperatorLite.STDistanceLess ? "< " : "<= ") + pname);
                break;

            default:
                throw new NotImplementedException("operator " + condition.Operator.ToString() + " not implemented yet");
            }
        }
Ejemplo n.º 2
0
 private void AddListCondition(DbCommand cmd, ConditionLite condition, StringBuilder sb, ref int paramIndex, ref int indentation, IEnumerable values, IQueryBuilder queryBuilder, PropertyMetadata propertyMetadata, string quotedColumnName)
 {
     if (values != null)
     {
         bool firstValue         = true;
         var  isNumericField     = propertyMetadata.PropertyInfo.PropertyType.IsNumericType();
         var  undelyingFieldType = propertyMetadata.PropertyInfo.PropertyType.UndelyingType();
         var  isStringField      = propertyMetadata.PropertyInfo.PropertyType == typeof(string);
         if ((isNumericField || isStringField) && values.AreThereMoreThan(8))
         {
             bool firstChunk = true;
             int  valueCount = 0;
             sb.Append("(");
             foreach (object v in values)
             {
                 object value = v;
                 if (valueCount % 1000 == 0)
                 {
                     firstValue = true;
                     if (firstChunk)
                     {
                         firstChunk = false;
                     }
                     else
                     {
                         sb.Append(") ").Append(condition.Operator == OperatorLite.In ? " OR " : " AND ");
                     }
                     sb.Append(quotedColumnName).Append(condition.Operator == OperatorLite.In ? " IN (" : " NOT IN (");
                 }
                 string valueStr = null;
                 if (value == null)
                 {
                     valueStr = "NULL";
                 }
                 else
                 {
                     if (isNumericField && !value.IsNumeric())
                     {
                         try
                         {
                             value = Convert.ChangeType(value, undelyingFieldType, CultureInfo.InvariantCulture);
                         }
                         catch (Exception ex)
                         {
                             throw new ArgumentException($"A non numeric value has been found for {condition.Operator} operator and field {propertyMetadata.PropertyInfo.Name}", ex);
                         }
                     }
                     valueStr = Convert.ToString(value, CultureInfo.InvariantCulture);
                     if (isStringField)
                     {
                         valueStr = "'" + valueStr.Replace("'", "''") + "'";
                     }
                 }
                 if (firstValue)
                 {
                     firstValue = false;
                 }
                 else
                 {
                     sb.Append(", ");
                 }
                 sb.Append(valueStr);
                 valueCount++;
             }
             sb.Append("))");
         }
         else
         {
             sb.Append(quotedColumnName).Append(condition.Operator == OperatorLite.In ? " IN (": " NOT IN (");
             string parameterName;
             foreach (object value in values)
             {
                 var param = CreateParameter(propertyMetadata, value, ref paramIndex, out parameterName);
                 cmd.Parameters.Add(param);
                 if (firstValue)
                 {
                     firstValue = false;
                 }
                 else
                 {
                     sb.Append(", ");
                 }
                 sb.Append(parameterName);
             }
             sb.Append(")");
         }
     }
     else
     {
         sb.Append(quotedColumnName).Append(condition.Operator == OperatorLite.In ? " IN (" : " NOT IN (");
         sb.Append('\n').Append(queryBuilder.GetSelectQuery(cmd, ref paramIndex, ++indentation));
         sb.NewIndentedLine(--indentation);
         sb.Append(")");
     }
 }
Ejemplo n.º 3
0
        protected virtual void GenerateFilterForCondition(DbCommand cmd, ConditionLite condition, StringBuilder sb, ref int paramIndex, ref bool firstCondition)
        {
            if (condition.Filter != null && condition.Filter.IsEmpty()) return;
            if (firstCondition) { ;}
            else if (condition.LogicalOperator == LogicalOperatorLite.And) sb.Append("\n    AND ");
            else if (condition.LogicalOperator == LogicalOperatorLite.Or) sb.Append("\n    OR");
            else throw new NotImplementedException("Logical operator " + condition.LogicalOperator.ToString() + " not implemented");
            firstCondition = false;

            if (condition.Filter != null)
            {
                sb.Append(GetFilter(cmd, ref paramIndex, condition.Filter));
                return;
            }

            IEnumerable values = condition.FieldValue as IEnumerable;
            IQueryBuilder queryBuilder = condition.SubQuery == null ? null : condition.SubQuery.QueryBuilder;
            if (condition.Operator == OperatorLite.In || condition.Operator == OperatorLite.NotIn)
            {
                if (values == null && queryBuilder == null) throw new ArgumentException("The value for In and NotIn operators must be enumerable or a query builder", "expression");
                int count;
                if (values != null)
                {
                    ICollection collection = values as ICollection;
                    Array array = values as Array;
                    if (collection != null) count = collection.Count;
                    else if (array != null) count = array.Length;
                    else count = values.Cast<object>().Count();
                    if (count == 0)
                    {
                        if (condition.Operator == OperatorLite.In)
                        {
                            sb.Append(" 1=0");
                        }
                        else
                        {
                            sb.Append(" 1=1");
                        }
                        return;
                    }
                }
            }
            PropertyMetadata propertyMetadata = null;
            string fieldName = condition.FieldName;
            if (!QueryLite.EntityType.GetEntityMetadata().Properties.TryGetValue(fieldName, out propertyMetadata))
            {
                throw new ArgumentException("Field " + condition.FieldName + " cannot be used in a filter because it is not a property of " + QueryLite.EntityType.Name);
            }
            if (propertyMetadata.IsLocalizedFiled)
            {
                fieldName = CurrentLanguageService.GetSufixedLocalizedFieldName(fieldName);

                if (string.IsNullOrEmpty(fieldName))
                {
                    throw new InvalidOperationException("Cannot sort by localized property " + fieldName + " because no field name has been found for the current language: " + CurrentLanguageService.CurrentLanguageCode);
                }
                if (!QueryLite.EntityType.GetEntityMetadata().Properties.TryGetValue(fieldName, out propertyMetadata))
                {
                    throw new ArgumentException("Field " + fieldName + " cannot be used in a filter because it is not a property of " + QueryLite.EntityType.Name);
                }
            }
            if (propertyMetadata.SqlField == null)
            {
                throw new ArgumentException("Field " + fieldName + " cannot be used in a filter because it has no metadata");
            }

            sb.Append(this.QueryLite.DataService.EntityLiteProvider.StartQuote).Append(propertyMetadata.SqlField.ColumnName).Append(this.QueryLite.DataService.EntityLiteProvider.EndQuote);
            if (condition.Operator == OperatorLite.IsNull)
            {
                sb.Append(" IS NULL");
                return;
            }
            if (condition.Operator == OperatorLite.IsNotNull)
            {
                sb.Append(" IS NOT NULL");
                return;
            }

            if (condition.Operator == OperatorLite.In || condition.Operator == OperatorLite.NotIn)
            {
                if (condition.Operator == OperatorLite.In) sb.Append(" IN (");
                else sb.Append(" NOT IN (");
                if (values != null)
                {
                    bool firstValue = true;
                    foreach (object value in values)
                    {
                        var param = CreateParameter(propertyMetadata, value, ref paramIndex);
                        cmd.Parameters.Add(param);
                        if (firstValue) firstValue = false;
                        else sb.Append(", ");
                        sb.Append(param.ParameterName);
                    }
                }
                else
                {
                    sb.Append(queryBuilder.GetSelectQuery(cmd, ref paramIndex));
                }
                sb.Append(")");
                return;
            }

            var parameter = CreateParameter(propertyMetadata, condition.FieldValue, ref paramIndex);
            cmd.Parameters.Add(parameter);

            switch (condition.Operator)
            {
                case OperatorLite.Contains:
                    sb.Append(" LIKE " + QueryLite.DataService.EntityLiteProvider.Concat("'%'", parameter.ParameterName, "'%'"));
                    break;
                case OperatorLite.Equals:
                    sb.Append(" = ").Append(parameter.ParameterName);
                    break;
                case OperatorLite.Greater:
                    sb.Append(" > ").Append(parameter.ParameterName);
                    break;
                case OperatorLite.GreaterOrEquals:
                    sb.Append(" >= ").Append(parameter.ParameterName);
                    break;
                case OperatorLite.Less:
                    sb.Append(" < ").Append(parameter.ParameterName);
                    break;
                case OperatorLite.LessOrEquals:
                    sb.Append(" <= ").Append(parameter.ParameterName);
                    break;
                case OperatorLite.NotContains:
                    sb.Append(" NOT LIKE " + QueryLite.DataService.EntityLiteProvider.Concat("'%'", parameter.ParameterName, "'%'"));
                    break;
                case OperatorLite.NotEquals:
                    sb.Append(" <> ").Append(parameter.ParameterName);
                    break;
                case OperatorLite.NotStartsWith:
                    sb.Append(" NOT LIKE " + QueryLite.DataService.EntityLiteProvider.Concat(parameter.ParameterName, "'%'"));
                    break;
                case OperatorLite.StartsWith:
                    sb.Append(" LIKE " + QueryLite.DataService.EntityLiteProvider.Concat(parameter.ParameterName, "'%'"));
                    break;
                case OperatorLite.IsDescendantOf:
                    sb.Append(".IsDescendantOf(" + parameter.ParameterName + ") = 1");
                    break;
                case OperatorLite.IsChildOf:
                    sb.Append(".GetAncestor(1) = " + parameter.ParameterName);
                    break;
                case OperatorLite.IsGrandChildOf:
                    sb.Append(".GetAncestor(2) = " + parameter.ParameterName);
                    break;
                case OperatorLite.HierarchyLevelEquals:
                    sb.Append(".GetLevel() = " + parameter.ParameterName);
                    break;
                case OperatorLite.STEquals:
                    sb.Append(".STEquals(" + parameter.ParameterName + ") = 1");
                    break;
                case OperatorLite.STIntersects:
                    sb.Append(".STIntersects(" + parameter.ParameterName + ") = 1");
                    break;
                case OperatorLite.STDistanceLess:
                case OperatorLite.STDistanceLessOrEquals:
                    string parameterName = QueryLite.DataService.EntityLiteProvider.ParameterPrefix + "P" + paramIndex.ToString();
                    cmd.Parameters.AddWithValue(parameterName, condition.Parameter);
                    paramIndex++;
                    sb.Append(".STDistance(" + parameter.ParameterName + ") " + (condition.Operator == OperatorLite.STDistanceLess ? "< " : "<= ") + parameterName);
                    break;
                default:
                    throw new NotImplementedException("operator " + condition.Operator.ToString() + " not implemented yet");
            }
        }