public void QueryTest()
{
var sqlProvider = new MySqlSqlProvider();
using (var session = this.Factory.CreateRawSession())
{
#region DateTime
QueryTestInternal(session,
f => f.Birthday < new DateTime(2019, 02, 07, 23, 28, 00, 00),
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE Birthday < @Birthday",
1);
var dateTime = new DateTime(2019, 02, 07, 23, 28, 00, 00);
QueryTestInternal(session,
f => f.Birthday < dateTime,
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE Birthday < @Birthday",
1);
//QueryTestInternal(session,
// f => f.Birthday < DateTime.Now,
// "SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE Birthday < NOW()",
// 0);
//QueryTestInternal(session,
// f => f.Birthday == DateTime.Today,
// "SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE DATE(Birthday) = DATE(NOW())",
// 0);
#endregion
#region Lists
var namesList = new List <string>
{
"Jaina",
"Arthas",
"Garrosh"
};
QueryTestInternal(session,
f => namesList.Contains(f.FirstName),
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE FirstName IN (@FirstName, @FirstName1, @FirstName2)",
3);
QueryTestInternal(session,
f => new List <string>
{
"Jaina",
"Arthas",
"Garrosh"
}.Contains(f.FirstName),
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE FirstName IN (@FirstName, @FirstName1, @FirstName2)",
3);
#endregion
QueryTestInternal(session,
f => f.FirstName == "Jaina",
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE FirstName = @FirstName",
1);
QueryTestInternal(session,
f => f.FirstName == "Temp" && f.ID == 2 || f.LastName == "Proudmoore" && f.Salary != 0,
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE ((FirstName = @FirstName) AND (ID = @ID)) OR ((LastName = @LastName) AND (Salary <> @Salary))",
4);
QueryTestInternal(session,
f => f.ID == 2,
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE ID = @ID",
1);
var id = 3;
QueryTestInternal(session,
f => f.ID == id,
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE ID = @ID",
1);
var ids = new List <int>
{
1,
2,
3
};
foreach (var currentId in ids)
{
QueryTestInternal(session,
f => f.ID == currentId,
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE ID = @ID",
1);
}
QueryTestInternal(session,
f => f.ID == this.GetId(),
"SELECT ID, FirstName, LastName, Gender, Birthday, Status, Salary FROM Employee WHERE ID = @ID",
1);
}
void QueryTestInternal(IRawDatabaseSession session, Expression <Func <Employee, bool> > expression, string expectedResultSql, int expectedResultParamaterCount)
{
var query = sqlProvider.GetSqlForPredicate(expression, this.MappingProvider.GetMapping <Employee>());
var result1 = session.ExecuteReader <Employee>(query);
var replaced = query.GetWithReplacedParameters();
Assert.True(query.Sql == expectedResultSql);
Assert.True(query.Parameters.Count == expectedResultParamaterCount);
}
}
public MySqlDataBaseSessionAsync(SdOrmConfig config)
: base(new MySqlConnection(config.ConnectionString), config.Mappings)
{
this._sqlProvider = new MySqlSqlProvider();
}
