public void Convert_QueryBuilder_Returns_Built_QueryExpression()
{
var expressionConverter = CreateConverter <int, int>();
var testBuilder = new TestQueryBuilder();
var condition = expressionConverter.Convert(q => testBuilder);
Assert.ReferenceEquals(testBuilder.QueryExpression, condition.QueryExpression);
}
public void Test_CustomMaxValue_To_Large([Values(201, 205)] int customMaxLimit)
{
var builder = new TestQueryBuilder(customMaxLimit);
builder.Limit = 300;
Assert.That(builder.Limit, Is.EqualTo(200));
Assert.That(builder.CustomLimit, Is.EqualTo(200));
}
public void Test_CustomMaxValue_To_Small([Values(0, -5)] int customMaxLimit)
{
var builder = new TestQueryBuilder(customMaxLimit);
builder.Limit = 10;
Assert.That(builder.Limit, Is.EqualTo(1));
Assert.That(builder.CustomLimit, Is.EqualTo(1));
}
public void Test_CustomMaxValue()
{
var builder = new TestQueryBuilder(78);
builder.Limit = 100;
Assert.That(builder.Limit, Is.EqualTo(78));
Assert.That(builder.CustomLimit, Is.EqualTo(78));
}
public void TableNameWithSquareBracketsTest()
{
var testCriteria = new SquareBracketsTableNameTestCriteria {
TestPropertyId = 1
};
var builder = new TestQueryBuilder <SquareBracketsTableNameTestCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select [TestTable].* from [TestTable] WHERE [TestTable].TestProperty = @TestTableTestPropertyId",
SimplifyString(query.Sql));
}
public void SelectNullableNullTest()
{
var testCriteria = new SelectNullableCriteria
{
};
var builder = new TestQueryBuilder <SelectNullableCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* from Houses",
SimplifyString(query.Sql));
}
public void TestSimple()
{
var builder = new TestQueryBuilder <TestCriteria>(new TestCriteria());
var query = builder.Build();
Assert.AreEqual("Select TableName.* from TableName", SimplifyString(query.Sql));
Assert.AreEqual("Id", query.SplitOn);
DynamicParameters tmp;
Assert.IsNotNull((tmp = query.Parameters as DynamicParameters));
Assert.AreEqual(0, tmp.ParameterNames.Count());
}
public void JoinWithoutJoinedField()
{
var testCriteria = new JoinWithoutJoinedFieldCriteria
{
WithPersons = true
};
var builder = new TestQueryBuilder <JoinWithoutJoinedFieldCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* , 0 as SplitOnPersonsHouseId , Persons.* from Houses LEFT JOIN Persons on Persons.HouseId = Houses.HouseId",
SimplifyString(query.Sql));
}
public void JoinAddOnTypeTest()
{
var testCriteria = new JoinAddOnTypeCriteria
{
WithPeople = true
};
var builder = new TestQueryBuilder <JoinAddOnTypeCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* , 0 as SplitOnPeoplePeopleId , People.* from Houses LEFT JOIN HousePeople on HousePeople.HouseId = Houses.HouseId AND HousesPeople.Required = 1 LEFT JOIN People on People.PeopleId = HousePeople.PeopleId",
SimplifyString(query.Sql));
}
public void JoinSelectTest()
{
var testCriteria = new JoinSelectCriteria
{
WithOwners = true
};
var builder = new TestQueryBuilder <JoinSelectCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* , 0 as SplitOnOwnersHouseId , Owners.Name , Owners.Id , Type as OwnerType from Houses LEFT JOIN Owners on Owners.HouseId = Houses.HouseId",
SimplifyString(query.Sql));
}
public void SelectNullableTest()
{
var testCriteria = new SelectNullableCriteria
{
Id = 1
};
var builder = new TestQueryBuilder <SelectNullableCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* , Houses.Name from Houses WHERE Houses.Id = @HousesId",
SimplifyString(query.Sql));
}
public void TestWhereMultiple()
{
var testCriteria = new TestWhereMultipleCriteria
{
HasOwnerNotThis = Guid.NewGuid()
};
var builder = new TestQueryBuilder <TestWhereMultipleCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* from Houses WHERE Houses.OwnerId = @HousesHasOwnerNotThis AND Houses.OwnerId is not null",
SimplifyString(query.Sql));
}
public void JoinAddOnTest()
{
var testCriteria = new JoinAddOnCriteria
{
WithOwners = true
};
var builder = new TestQueryBuilder <JoinAddOnCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* , 0 as SplitOnOwnersHouseId from Houses LEFT JOIN Owners on Owners.HouseId = Houses.HouseId AND Owners.OwnerId in (1,2,3)",
SimplifyString(query.Sql));
}
public void TestSimpleJoin()
{
var testCriteria = new TestJoinCriteria
{
WithAnotherTable = true,
};
var builder = new TestQueryBuilder <TestJoinCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* , 0 as SplitOnAnotherTableCurrentTableId , AnotherTable.* from TableName LEFT JOIN AnotherTable on AnotherTable.CurrentTableId = TableName.CurrentTableId"
, SimplifyString(query.Sql)
);
}
public void JoinNoSplitTest()
{
var testCriteria = new JoinNoSplitCriteria
{
OwnerId = 1
};
var builder = new TestQueryBuilder <JoinNoSplitCriteria>(testCriteria);
var query = builder.Build();
Assert.Equal(
"SELECT Houses.* , 0 as SplitOnOwnersId FROM Houses LEFT JOIN HouseOwners on HouseOwners.HouseId = Houses.Id WHERE HouseOwners.OwnerId = @HouseOwnersOwnerId",
SimplifyString(query.Sql));
Assert.Equal("SplitOnOwnersId", query.SplitOn);
}
public void TestManyToManyJoinEmpty()
{
var testCriteria = new TestManyToManyJoinCriteria
{
WithAnotherTable = false,
};
var builder = new TestQueryBuilder <TestManyToManyJoinCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* , 0 as SplitOnAnotherTableAnotherId from TableName"
, SimplifyString(query.Sql)
);
}
public void TestSimpleJoinEmpty()
{
var testCriteria = new TestJoinCriteria
{
WithAnotherTable = false,
};
var builder = new TestQueryBuilder <TestJoinCriteria>(testCriteria);
var query = builder.Build();
Assert.Equal(
"SELECT TableName.* , 0 as SplitOnAnotherTableCurrentTableId FROM TableName"
, SimplifyString(query.Sql)
);
}
public void JoinNoSplitTest2()
{
var testCriteria = new JoinNoSplitCriteria2
{
OwnerId = 1
};
var builder = new TestQueryBuilder <JoinNoSplitCriteria2>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* from Houses LEFT JOIN HouseOwners on HouseOwners.HouseId = Houses.Id WHERE HouseOwners.OwnerId = @HouseOwnersOwnerId",
SimplifyString(query.Sql));
Assert.AreEqual("", query.SplitOn);
}
public void TestIncludingIncludingFieldIsNotExists()
{
var testCriteria = new TestIncludingCriteria
{
OwnerName = "Vasya",
WithOwners = false,
};
var builder = new TestQueryBuilder <TestIncludingCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* , 0 as SplitOnOwnersId from Houses LEFT JOIN Owners on Owners.Id = Houses.OwnerId WHERE Owners.OwnerName Like @OwnersOwnerName",
SimplifyString(query.Sql));
}
public void BaseTest()
{
var testCriteria = new RealCriteria
{
Id = Guid.NewGuid(),
CustomerId = 1,
WithCustomers = true
};
var builder = new TestQueryBuilder <BaseCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select RealHouses.* , 0 as SplitOnCustomersCustomerId , Customers.* from RealHouses INNER JOIN Customers on Customers.CustomerId = RealHouses.CustomerId WHERE RealHouses.HouseId = @RealHousesId AND RealHouses.CustomerId = @RealHousesCustomerId",
SimplifyString(query.Sql));
}
public void TestSelect()
{
var testCriteria = new TestSelectCriteria
{
WithSum = true,
SelectClause = null,
AddSelect = "Shipments:Name,Mass"
};
var builder = new TestQueryBuilder <TestSelectCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Shipments.Name , Shipments.Mass , Sum(Shipments.Price) from Shipments",
SimplifyString(query.Sql));
}
public void TestManyToManyJoin()
{
var testCriteria = new TestManyToManyJoinCriteria
{
WithAnotherTable = true,
};
var builder = new TestQueryBuilder <TestManyToManyJoinCriteria>(testCriteria);
var query = builder.Build();
Assert.Equal(
"SELECT TableName.* , 0 as SplitOnAnotherTableAnotherId , AnotherTable.* FROM TableName " +
"LEFT JOIN AnotherTableCurrentTable on AnotherTableCurrentTable.CurrentId = TableName.CurrentId " +
"LEFT JOIN AnotherTable on AnotherTable.AnotherId = AnotherTableCurrentTable.AnotherId"
, SimplifyString(query.Sql)
);
}
public void SumTest()
{
var testCriteria = new SumCriteria
{
Ids = new [] { 1, 2, 3 },
QueryType = QueryType.Sum,
SelectClause = new SelectClause("sum(Houses.Price)"),
WithCustomers = true,
};
var builder = new TestQueryBuilder <SumCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select sum(Houses.Price) from Houses INNER JOIN Customers on Customers.CustomerId = Houses.CustomerId WHERE Houses.Id in @HousesIds",
SimplifyString(query.Sql));
}
public void JoinReferenceTest()
{
var testCriteria = new JoinReferenceCriteria
{
OwnerIds = new List <int>()
{
1, 2, 3, 4
}
};
var builder = new TestQueryBuilder <JoinReferenceCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Houses.* from Houses LEFT JOIN Owners on Owners.HouseId = Houses.HouseId WHERE Owners.Id in @OwnersOwnerIds",
SimplifyString(query.Sql));
}
public void TestLike()
{
var builder = new TestQueryBuilder <TestCriteria>(new TestCriteria
{
Name = "123",
});
var query = builder.Build();
Assert.AreEqual("Select TableName.* from TableName WHERE TableName.Name Like @TableNameName",
SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameName", dynamicParameters.ParameterNames.Single());
Assert.AreEqual("%123%", parameters["TableNameName"]);
}
public void TestWhereEq()
{
var builder = new TestQueryBuilder <TestCriteria>(new TestCriteria
{
Id = 1,
});
var query = builder.Build();
Assert.Equal("SELECT TableName.* FROM TableName WHERE TableName.Id = @TableNameId",
SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.Equal(1, dynamicParameters.ParameterNames.Count());
Assert.Equal("TableNameId", dynamicParameters.ParameterNames.Single());
Assert.Equal(1, parameters["TableNameId"]);
}
public void GroupByTest()
{
var testCriteria = new GroupByCriteria
{
GroupBy = new [] { "Houses.OwnerId", "Houses.Category" },
SelectClause = new SelectClause
{
Select = "Count(1) , Houses.OwnerId , Houses.Category",
IsExpression = true
}
};
var builder = new TestQueryBuilder <GroupByCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Count(1) , Houses.OwnerId , Houses.Category from Houses GROUP BY Houses.OwnerId , Houses.Category",
SimplifyString(query.Sql));
}
public void TestGtEq()
{
var crit = new TestCriteria
{
DateFrom = DateTime.Now,
};
var builder = new TestQueryBuilder <TestCriteria>(crit);
var query = builder.Build();
Assert.AreEqual("Select TableName.* from TableName WHERE TableName.Date >= @TableNameDateFrom",
SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameDateFrom", dynamicParameters.ParameterNames.Single());
Assert.AreEqual(crit.DateFrom, parameters["TableNameDateFrom"]);
}
public void TestFormatter()
{
var testCriteria = new TestCriteria
{
DateTimeWithFormatter = DateTime.Now,
};
var builder = new TestQueryBuilder <TestCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* from TableName WHERE TableName.DateTimeWithFormatter = @TableNameDateTimeWithFormatter"
, SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameDateTimeWithFormatter", dynamicParameters.ParameterNames.Single());
Assert.AreEqual("1", parameters["TableNameDateTimeWithFormatter"]);
}
public void TestJoinOrder()
{
var testCriteria = new TestJoinOrderCriteria
{
WithAirplans = true,
WithCars = true,
WithHouses = true,
};
var builder = new TestQueryBuilder <TestJoinOrderCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select Persons.* , 0 as SplitOnCarsPersonId , Cars.* , 0 as SplitOnAirplansPersonId , Airplans.* , 0 as SplitOnHousesPersonId , Houses.* from Persons " +
"LEFT JOIN Cars on Cars.PersonId = Persons.Id " +
"LEFT JOIN Airplans on Airplans.PersonId = Persons.Id " +
"LEFT JOIN Houses on Houses.PersonId = Persons.Id"
, SimplifyString(query.Sql)
);
}
