public void AddWhereConditionsToQueryWithWhereTest()
{
// Arrange
var query = "SELECT * FROM Alpha a wHeRe a.Beta > 7";
var data = new WhereQuerySettings
{
PropertyMappings = new Dictionary <string, string>
{
{ ".", "a" }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => alpha.Gamma == "One"
};
// Act
var result = queryConditions.AddWhereConditionsToQuery(query, data);
// Assert
result.QueryParameter.Should().HaveCount(1);
result.QueryParameter.Keys.First().Should().Be("p0");
result.QueryParameter.Values.First().Should().Be("One");
result.Sql.Should().Be($"{query}{Environment.NewLine}AND{Environment.NewLine}(a.Gamma = @p0){Environment.NewLine}");
}
public void CalculateWhereConditionsPropertyTypeMappingSubClassTest()
{
// Arrange
var data = new WhereQuerySettings
{
PropertyTypeMappings = new Dictionary <Type, string>
{
{ typeof(Alpha), "a" },
{ typeof(Omega), "o" }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => alpha.Omega.Psi == "One"
};
// Act
var result = queryConditions.CalculateWhereConditions(data);
// Assert
result.QueryParameter.Should().HaveCount(1);
result.QueryParameter.Keys.First().Should().Be("p0");
result.QueryParameter.Values.First().Should().Be("One");
result.Sql.Should().Be("(o.Psi = @p0)" + Environment.NewLine);
}
public void CalculateWhereConditionsQueryParameterTest()
{
// Arrange
var data = new WhereQuerySettings
{
QueryParameter = new Dictionary <string, object>
{
{ "Beta", 6 }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => alpha.Gamma == "One"
};
// Act
var result = queryConditions.CalculateWhereConditions(data);
// Assert
result.QueryParameter.Should().HaveCount(2);
result.QueryParameter.Keys.First().Should().Be("Beta");
result.QueryParameter.Values.First().Should().Be(6);
result.QueryParameter.Keys.Last().Should().Be("p0");
result.QueryParameter.Values.Last().Should().Be("One");
result.Sql.Should().Be("(Gamma = @p0)" + Environment.NewLine);
}
public void CalculateWhereConditionsAnyAndWithPropertyTypeMappingTest()
{
// Arrange
var values = new List <int> {
10, 20
};
var data = new WhereQuerySettings
{
PropertyTypeMappings = new Dictionary <Type, string>
{
{ typeof(Alpha), "a" }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => values.Any(v => alpha.Beta > v && v > alpha.Epsilon)
};
// Act
var result = queryConditions.CalculateWhereConditions(data);
// Assert
result.QueryParameter.Should().HaveCount(2);
result.QueryParameter.Keys.First().Should().Be("p0");
result.QueryParameter.Keys.Last().Should().Be("p1");
result.QueryParameter.Values.First().Should().Be(10);
result.QueryParameter.Values.Last().Should().Be(20);
result.Sql.Should().Be("(((a.Beta > @p0) AND (@p0 > a.Epsilon)) OR ((a.Beta > @p1) AND (@p1 > a.Epsilon)))" + Environment.NewLine);
}
public void CalculateWhereConditionsPropertyMappingTest()
{
// Arrange
var data = new WhereQuerySettings
{
PropertyMappings = new Dictionary <string, string>
{
{ ".", "a" }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => alpha.Gamma == "One"
};
// Act
var result = queryConditions.CalculateWhereConditions(data);
// Assert
result.QueryParameter.Should().HaveCount(1);
result.QueryParameter.Keys.First().Should().Be("p0");
result.QueryParameter.Values.First().Should().Be("One");
result.Sql.Should().Be("(a.Gamma = @p0)" + Environment.NewLine);
}
public void AddWhereConditionsToQueryInnerWhereTest()
{
// Arrange
var query = "SELECT * FROM Alpha a LEFT JOIN (SELECT o.Psi FROM Omega WHERE o.Psi IS NOT NULL) io ON io.Psi = a.Gamma";
var data = new WhereQuerySettings
{
PropertyMappings = new Dictionary <string, string>
{
{ ".", "a" }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => alpha.Gamma == "One"
};
// Act
var result = queryConditions.AddWhereConditionsToQuery(query, data);
// Assert
result.QueryParameter.Should().HaveCount(1);
result.QueryParameter.Keys.First().Should().Be("p0");
result.QueryParameter.Values.First().Should().Be("One");
result.Sql.Should().Be($"{query}{Environment.NewLine}WHERE{Environment.NewLine}(a.Gamma = @p0){Environment.NewLine}");
}
public void CalculateWhereConditionsSubObjectBooleanPropertyTest()
{
// Arrange
var data = new WhereQuerySettings
{
PropertyTypeMappings = new Dictionary <Type, string>
{
{ typeof(Alpha), "a" },
{ typeof(Omega), "o" }
}
};
var queryConditions = new List <Expression <Func <Alpha, bool> > >
{
alpha => !alpha.IsActive && alpha.Omega.IsActive
};
// Act
var result = queryConditions.CalculateWhereConditions(data);
// Assert
result.QueryParameter.Should().HaveCount(2);
result.QueryParameter.Keys.First().Should().Be("p0");
result.QueryParameter.Values.First().Should().Be(true);
result.QueryParameter.Keys.Last().Should().Be("p1");
result.QueryParameter.Values.Last().Should().Be(true);
result.Sql.Should().Be($"(NOT(a.IsActive = @p0) AND (o.IsActive = @p1)){Environment.NewLine}");
}
public void AddSortConditionsToQueryInnerOrderByTest()
{
// Arrange
var query = "SELECT * FROM Alpha a JOIN (SELECT * FROM Omega ORDER BY Psi) o ON o.Psi = a.Gamma";
var sortingEngine = new SortingQueryEngine();
var mappings = new Dictionary <string, List <Expression <Func <Alpha, object> > > >
{
{ nameof(Omega.Psi), new List <Expression <Func <Alpha, object> > > {
a => a.Omega.Psi
} }
};
var queryParameters = new QueryParameters
{
SortingQueryProperties = new List <SortingQueryProperty>
{
new SortingQueryProperty
{
PropertyName = nameof(Alpha.Beta),
SortOrder = SortOrder.Descending
},
new SortingQueryProperty
{
PropertyName = nameof(Omega.Psi),
SortOrder = SortOrder.Ascending
}
}
};
var data = new WhereQuerySettings
{
PropertyMappings = new Dictionary <string, string>
{
{ ".", "a" },
{ ".Omega.Psi", "o.Psi" }
}
};
var orderByConditions = sortingEngine.BuildSortConditions(queryParameters, mappings);
// Act
var result = orderByConditions.Data.AddSortConditionsToQuery(query, data);
// Assert
result.Should().Be($"{query}{Environment.NewLine}ORDER BY{Environment.NewLine}a.Beta DESC, o.Psi ASC");
}
public void CalculateSortConditionsTest()
{
// Arrange
var sortingEngine = new SortingQueryEngine();
var mappings = new Dictionary <string, List <Expression <Func <Alpha, object> > > >
{
{ nameof(Omega.Psi), new List <Expression <Func <Alpha, object> > > {
a => a.Omega.Psi
} }
};
var queryParameters = new QueryParameters
{
SortingQueryProperties = new List <SortingQueryProperty>
{
new SortingQueryProperty
{
PropertyName = nameof(Alpha.Beta),
SortOrder = SortOrder.Descending
},
new SortingQueryProperty
{
PropertyName = nameof(Omega.Psi),
SortOrder = SortOrder.Ascending
}
}
};
var data = new WhereQuerySettings
{
PropertyMappings = new Dictionary <string, string>
{
{ ".", "a" },
{ ".Omega.Psi", "o.Psi" }
}
};
var orderByConditions = sortingEngine.BuildSortConditions(queryParameters, mappings);
// Act
var result = orderByConditions.Data.CalculateSortConditions(data);
// Assert
result.Should().Be("a.Beta DESC, o.Psi ASC");
}
public void AddSortConditionsToQueryNoConditionsTest()
{
// Arrange
var query = "SELECT * FROM Alpha a ORDER BY a.Gamma ASC";
var sortingEngine = new SortingQueryEngine();
var queryParameters = new QueryParameters
{
SortingQueryProperties = new List <SortingQueryProperty>()
};
var data = new WhereQuerySettings();
var orderByConditions = sortingEngine.BuildSortConditions <Alpha>(queryParameters);
// Act
var result = orderByConditions.Data.AddSortConditionsToQuery(query, data);
// Assert
result.Should().Be(query);
}
public static WhereQueryResult CalculateWhereConditions <T>(this IEnumerable <Expression <Func <T, bool> > > queryConditions, WhereQuerySettings settings = null) where T : class
{
var whereQueryEngine = new WhereQueryEngine();
return(whereQueryEngine.CalculateWhereConditions(queryConditions, settings));
}
public static WhereQueryResult AddWhereConditionsToQuery <T>(this IEnumerable <Expression <Func <T, bool> > > queryConditions, string sqlQuery, WhereQuerySettings settings = null) where T : class
{
var whereQueryEngine = new WhereQueryEngine();
return(whereQueryEngine.AddWhereConditionsToQuery(queryConditions, sqlQuery, settings));
}
public WhereQueryResult CalculateWhereConditions <T>(IEnumerable <Expression <Func <T, bool> > > queryConditions, WhereQuerySettings settings)
{
var result = new WhereQueryResult(settings?.QueryParameter);
if (!(queryConditions?.Any() ?? false))
{
return(result);
}
var data = new WhereQueryData
{
PropertyMappings = settings?.PropertyMappings ?? new Dictionary <string, string>(),
PropertyTypeMappings = settings?.PropertyTypeMappings ?? new Dictionary <Type, string>(),
QueryParameter = result.QueryParameter
};
var sql = new StringBuilder();
foreach (var queryCondition in queryConditions)
{
if (sql.Length > 0)
{
sql.AppendLine("AND");
}
sql.AppendLine(ProcessExpression(queryCondition.Body, data, null));
}
result.Sql = sql.ToString();
return(result);
}
public WhereQueryResult AddWhereConditionsToQuery <T>(IEnumerable <Expression <Func <T, bool> > > queryConditions, string sqlQuery, WhereQuerySettings settings)
{
var whereQueryResult = CalculateWhereConditions(queryConditions, settings);
if (whereQueryResult.Sql.IsNullOrEmpty())
{
whereQueryResult.Sql = sqlQuery;
return(whereQueryResult);
}
var existence = sqlQuery.CheckExistence(SQL_WHERE);
if (existence == Existence.Available)
{
whereQueryResult.Sql = String.Join(Environment.NewLine, sqlQuery, SQL_AND, whereQueryResult.Sql);
}
else
{
whereQueryResult.Sql = String.Join(Environment.NewLine, sqlQuery, SQL_WHERE, whereQueryResult.Sql);
}
return(whereQueryResult);
}
