private (string Query, DynamicParameters Parameters) BuildGetWhereQuery(Expression <Func <TModel, bool> > where, QueryExecutionOptions options = null)
{
var limit = options?.ResultLimit;
var columnName = options?.ColumnName;
var language = GetCurrentLanguage();
var(whereClauseSql, whereClauseParameters) = WhereClauseCompiler.ToSql(where, language: language);
var parameters = new DynamicParameters(whereClauseParameters);
var limitSql = limit.HasValue
? (language == WhereClauseCompiler.Language.MYSQL) ? $" LIMIT {limit}" : $" TOP {limit}"
: "";
var orderByColumnSql = String.IsNullOrEmpty(columnName) ? "" : $"ORDER BY {columnName}";
string query;
if (language.Equals(WhereClauseCompiler.Language.MYSQL))
{
query = $"SELECT * FROM `{TableName}` WHERE {whereClauseSql}{orderByColumnSql}{limitSql}";
}
else
{
query = $"SELECT {limitSql} * FROM {TableName} WHERE {whereClauseSql}{orderByColumnSql}";
}
return(query, parameters);
}
public void MultipleClauses_PreserveOperationHierarchy_NoParentheses()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Id_Long_Nullable == null || x.Flag_Nullable == null || x.Date_Nullable == null && x.StringData == null || x.Guid_Nullable == null);
//ASSERT
Assert.AreEqual("(([Id_Long_Nullable] IS NULL) OR ([Flag_Nullable] IS NULL) OR ([Date_Nullable] IS NULL) AND ([StringData] IS NULL) OR ([Guid_Nullable] IS NULL))", clause.Sql);
Assert.AreEqual(0, clause.Parameters.Count);
}
public void ParameterLimit_CustomLimit_Exceeded()
{
//ARRANGE
var limit = 5;
var array = Enumerable.Range(0, limit + 1);
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => array.Contains(x.Id_Int), parameterLimit: limit);
}
public void BoolColumn_CheckFalse()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => !x.Flag);
//ASSERT
Assert.AreEqual("(NOT ([Flag] = 1))", clause.Sql);
Assert.AreEqual(0, clause.Parameters.Count);
}
public void IntPropertyFromOtherType_In_IntArray_NotSupported()
{
//ARRANGE
var data = CreateTestDataDto();
var intArray = new[] { 1, 2, 3, 4 };
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => intArray.Contains(data.ChildData.ChildData.Id));
}
public void IntColumn_LessThan_IntConstant_NormalOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Id_Int < 1);
//ASSERT
Assert.AreEqual("([Id_Int] < @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(1, clause.Parameters["__P0"]);
Assert.AreEqual(typeof(int), clause.Parameters["__P0"].GetType());
}
public void PropertySelectionValidation_SelectedMapperProperty()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.AMappedPropertyWithMultipleAttributes == (object)1);
//ASSERT
Assert.AreEqual($"([AMappedPropertyWithMultipleAttributes] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(int), clause.Parameters[$"__P0"].GetType());
Assert.AreEqual(1, clause.Parameters[$"__P0"]);
}
public void StringConstant_EndsWith_StringColumn()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => "abc".EndsWith(x.StringData));
//ASSERT
Assert.AreEqual("(@__P0 LIKE CONCAT('%',[StringData]))", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(string), clause.Parameters["__P0"].GetType());
Assert.AreEqual("abc", clause.Parameters["__P0"]);
}
public void StringColumn_StartsWith_StringConstant()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.StringData.StartsWith("abc"));
//ASSERT
Assert.AreEqual("([StringData] LIKE CONCAT(@__P0,'%'))", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(string), clause.Parameters["__P0"].GetType());
Assert.AreEqual("abc", clause.Parameters["__P0"]);
}
public void NullableBoolColumn_Equals_BoolConstant_ReversedOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => true == x.Flag_Nullable);
//ASSERT
Assert.AreEqual("(@__P0 = [Flag_Nullable])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(bool), clause.Parameters["__P0"].GetType());
Assert.AreEqual(true, clause.Parameters["__P0"]);
}
public void BoolColumn_Equals_BoolConstant_NormalOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Flag == true);
//ASSERT
Assert.AreEqual("([Flag] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(true, clause.Parameters["__P0"]);
Assert.AreEqual(typeof(bool), clause.Parameters["__P0"].GetType());
}
public void NullableLongColumn_Equals_IntConstant_ReversedOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => 1 == x.Id_Long_Nullable);
//ASSERT
Assert.AreEqual("(@__P0 = [Id_Long_Nullable])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(long), clause.Parameters["__P0"].GetType());
Assert.AreEqual((long)1, clause.Parameters["__P0"]);
}
public void LongColumn_Equals_IntConstant_NormalOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Id_Long == 1);
//ASSERT
Assert.AreEqual("([Id_Long] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(long), clause.Parameters["__P0"].GetType());
Assert.AreEqual((long)1, clause.Parameters["__P0"]);
}
public void IntColumn_LessOrEqualThan_IntConstant_ReversedOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => 1 <= x.Id_Int);
//ASSERT
Assert.AreEqual("(@__P0 <= [Id_Int])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(1, clause.Parameters["__P0"]);
Assert.AreEqual(typeof(int), clause.Parameters["__P0"].GetType());
}
public void ModelWithBase_IdFromBaseComparison()
{
//ACT
var clause = WhereClauseCompiler.ToSql <ModelWithBase>(x => x.Base_Id == 1);
//ASSERT
Assert.AreEqual("([Base_Id] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(int), clause.Parameters["__P0"].GetType());
Assert.AreEqual(1, clause.Parameters["__P0"]);
}
public void StringColumn_Equals_StringConstant_ReversedOrder()
{
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => "abc" == x.StringData);
//ASSERT
Assert.AreEqual("(@__P0 = [StringData])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(string), clause.Parameters["__P0"].GetType());
Assert.AreEqual("abc", clause.Parameters["__P0"]);
}
public async Task <bool> DeleteWhereAsync(Expression <Func <TModel, bool> > where, QueryExecutionOptions options = null)
{
var(whereClauseSql, whereClauseParameters) = WhereClauseCompiler.ToSql(where, language: WhereClauseCompiler.Language.MYSQL);
var parameters = new DynamicParameters(whereClauseParameters);
using (var unitOfWork = ConnectionManager.Connect())
{
var rowsDeleted = await unitOfWork.Connection.ExecuteAsync($"DELETE FROM {TableName} WHERE {whereClauseSql}", parameters, ConnectionManager.GetCurrentTransaction(), options?.TimeoutInSeconds);
return(rowsDeleted >= 0);
}
}
public void IntColumn_In_EmptyIntArray()
{
//ARRANGE
var intArray = new int[] { };
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => intArray.Contains(x.Id_Int));
//ASSERT
Assert.AreEqual("(1=0)", clause.Sql);
Assert.AreEqual(0, clause.Parameters.Count);
}
public override sealed bool Delete(IEnumerable <long> ids, QueryExecutionOptions options = null)
{
var(whereClauseSql, whereClauseParameters) = WhereClauseCompiler.ToSql <TModel>(x => ids.Contains(x.Id), language: WhereClauseCompiler.Language.MYSQL);
var parameters = new DynamicParameters(whereClauseParameters);
using (var unitOfWork = ConnectionManager.Connect())
{
var deletedRows = unitOfWork.Connection.Execute($"DELETE FROM {TableName} WHERE {whereClauseSql}", parameters, ConnectionManager.GetCurrentTransaction(), options?.TimeoutInSeconds);
return(deletedRows >= 0);
}
}
public void NullableDateTimeColumn_Equals_NullNullableDateTimeMember_ReversedOrder()
{
//ARRANGE
var clasWithDate = CreateTestDataDto();
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => clasWithDate.ChildData.ChildData.Date == x.Date_Nullable);
//ASSERT
Assert.AreEqual("([Date_Nullable] IS NULL)", clause.Sql);
Assert.AreEqual(0, clause.Parameters.Count);
}
public void DateTimeColumn_Equals_DateTimeVariable_ReversedOrder()
{
//ARRANGE
var date = DateTime.Now;
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => date == x.Date);
//ASSERT
Assert.AreEqual("(@__P0 = [Date])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(DateTime), clause.Parameters["__P0"].GetType());
Assert.AreEqual(date, clause.Parameters["__P0"]);
}
public void NullableDateTimeColumn_Equals_NullableDateTimeMember_NormalOrder()
{
//ARRANGE
var clasWithDate = CreateTestDataDto(DateTime.Now);
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Date_Nullable == clasWithDate.ChildData.ChildData.Date);
//ASSERT
Assert.AreEqual("([Date_Nullable] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(DateTime), clause.Parameters["__P0"].GetType());
Assert.AreEqual(clasWithDate.ChildData.ChildData.Date, clause.Parameters["__P0"]);
}
public void ParameterLimit_CustomLimit_NotExceeded()
{
//ARRANGE
var limit = 5;
var array = Enumerable.Range(0, limit);
var parameterListToExpect = string.Join(", ", Enumerable.Range(0, limit).Select(x => $"@{WhereClauseCompiler.ParameterName}{x}"));
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => array.Contains(x.Id_Int), parameterLimit: limit);
//ASSERT
Assert.AreEqual($"([Id_Int] IN ({parameterListToExpect}))", clause.Sql);
Assert.AreEqual(limit, clause.Parameters.Count);
}
public void NullableGuidColumn_Equals_Guid_ReversedOrder()
{
//ARRANGE
var guid = Guid.NewGuid();
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => guid == x.Guid_Nullable);
//ASSERT
Assert.AreEqual("(@__P0 = [Guid_Nullable])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(Guid), clause.Parameters["__P0"].GetType());
Assert.AreEqual(guid, clause.Parameters["__P0"]);
}
public void NullableDateTimeMember_LessOrEqualThan_NullableDateTimeColumn()
{
//ARRANGE
var clasWithDate = CreateTestDataDto(DateTime.Now);
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => clasWithDate.ChildData.ChildData.Date <= x.Date_Nullable);
//ASSERT
Assert.AreEqual("(@__P0 <= [Date_Nullable])", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(DateTime), clause.Parameters["__P0"].GetType());
Assert.AreEqual(clasWithDate.ChildData.ChildData.Date, clause.Parameters["__P0"]);
}
public void GuidColumn_Equals_Guid_NormalOrder()
{
//ARRANGE
var guid = Guid.NewGuid();
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Guid == guid);
//ASSERT
Assert.AreEqual("([Guid] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(Guid), clause.Parameters["__P0"].GetType());
Assert.AreEqual(guid, clause.Parameters["__P0"]);
}
public void ParameterNames_CustomParameterName()
{
//ARRANGE
string customParamName = "customParam";
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Id_Int == 1, customParamName);
//ASSERT
Assert.AreEqual($"([Id_Int] = @{customParamName}0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(int), clause.Parameters[$"{customParamName}0"].GetType());
Assert.AreEqual(1, clause.Parameters[$"{customParamName}0"]);
}
public void LanguageOptions_MySql()
{
//ARRANGE
var language = WhereClauseCompiler.Language.MYSQL;
//ACT
var clause = WhereClauseCompiler.ToSql <Model>(x => x.Id_Int == 1, language: language);
//ASSERT
Assert.AreEqual($"(`Id_Int` = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(int), clause.Parameters[$"__P0"].GetType());
Assert.AreEqual(1, clause.Parameters[$"__P0"]);
}
public void ModelWithBase_IdFromBaseComparison_OtherClassWithSameBase()
{
//ARRANGE
var otherClass = new OtherClassWithSameBase {
Base_Id = 1
};
//ACT
var clause = WhereClauseCompiler.ToSql <ModelWithBase>(x => x.Base_Id == otherClass.Base_Id);
//ASSERT
Assert.AreEqual("([Base_Id] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(int), clause.Parameters["__P0"].GetType());
Assert.AreEqual(1, clause.Parameters["__P0"]);
}
public void Model_IdComparison_SameModelButInstanced()
{
//ARRANGE
var instanced = new ModelWithBase {
Base_Id = 1
};
//ACT
var clause = WhereClauseCompiler.ToSql <ModelWithBase>(x => x.Base_Id == instanced.Base_Id);
//ASSERT
Assert.AreEqual("([Base_Id] = @__P0)", clause.Sql);
Assert.AreEqual(1, clause.Parameters.Count);
Assert.AreEqual(typeof(int), clause.Parameters["__P0"].GetType());
Assert.AreEqual(1, clause.Parameters["__P0"]);
}
