public LambdaDelete <T> Where(Expression <Func <T, bool> > expression) { if (expression == null) { return(this); } string condition = FormatExpression(expression); WhereCondition.Add(condition); return(this); }
private string BuildDistinct() { // 1. Get a list of all conditions as list of field/value pairs List <WhereCondition> whereConditions = new List <WhereCondition>(); foreach (Row <T> condition in _conditions) { var whereCondition = new WhereCondition(); foreach (var field in _databaseFields) { object value = field.Item2.GetValue(condition.Data); if (value == null || (_onlyPrimaryKeys && field.Item3.Any(a => !a.IsPrimaryKey))) { continue; } whereCondition.Add(field.Item1, SQLUtil.ToSQLValue(value)); } if (!whereCondition.IsEmpty) { whereConditions.Add(whereCondition); } } // 2. Check if all conditions share the same fields bool allShareSameFields = true; var baselineConditionFields = whereConditions.First().FieldValuePairs.Keys; foreach (var condition in whereConditions) { if (!condition.FieldValuePairs.Keys.SequenceEqual(baselineConditionFields)) { allShareSameFields = false; } } if (!allShareSameFields) { return(null); } // ToDo: support the case with more than 2 fields if (baselineConditionFields.Count != 2) { return(null); } // 3. Get a distinct of values for each field var fieldsWithDistinctValues = (from condition in whereConditions from fieldValuePair in condition.FieldValuePairs group fieldValuePair by fieldValuePair.Key into byField select new { Field = byField.Key, Values = byField.ToList().Select(pair => pair.Value).Distinct().ToList() } ).OrderBy(field => field.Values.Count()).ToList(); // 4. Get the field with lowest different values var fieldWithLowestDifferentValues = fieldsWithDistinctValues.First().Field; // ToDo: support the case with more than 2 fields var secondField = fieldsWithDistinctValues.Skip(1).First().Field; // 5. Group conditions by the field with lowest different values var conditionsByFieldWithLowestDifferentValues = whereConditions.GroupBy(cond => cond.FieldValuePairs[fieldWithLowestDifferentValues]) .ToDictionary(g => g.Key, g => g.ToList()); // 6. Build the where clause using the field with lowest different values for the first condition StringBuilder whereClause = new StringBuilder(); foreach (var pair in conditionsByFieldWithLowestDifferentValues) { whereClause.Append("("); whereClause.Append(fieldWithLowestDifferentValues); whereClause.Append("="); whereClause.Append(pair.Key); whereClause.Append(" AND "); whereClause.Append(secondField); if (pair.Value.Select(cond => cond.FieldValuePairs[secondField]).Count() == 1) { whereClause.Append("="); whereClause.Append(pair.Value.Select(cond => cond.FieldValuePairs[secondField]).First()); } else { whereClause.Append(" IN ("); whereClause.Append(string.Join(',', pair.Value.Select(cond => cond.FieldValuePairs[secondField]))); whereClause.Append(")"); } whereClause.Append(")"); whereClause.Append(" OR "); } whereClause.Length -= 4; // remove last " OR "; return(whereClause.ToString()); }