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