public IList <MoneyViewModel> GetMonthData(int?year, int?month)
{
IBetweenPredicate predicates = null;
if (year.HasValue && month.HasValue)
{
//每月一號
DateTime monthStart = new DateTime(year.Value, month.Value, 1);
//每月最後一天
DateTime monthEnd = monthStart.AddMonths(1).AddDays(-1);
BetweenValues betweenValues = new BetweenValues
{
Value1 = monthStart,
Value2 = monthEnd,
};
predicates = Predicates.Between <AccountBook>(f =>
f.Dateee, betweenValues);
}
var sort = new List <ISort>
{
Predicates.Sort <AccountBook>(x => x.Dateee),
Predicates.Sort <AccountBook>(x => x.Categoryyy)
};
var source = this._accountBookRepository.GetList(predicates, sort);
var viewModels = source.Select(x =>
new MoneyViewModel
{
Id = x.Id,
Amount = x.Amounttt,
Category = x.Categoryyy.ParseEnum <CategoryType>(),
CreateTime = x.Dateee
}).ToList();
return(viewModels);
}
private Expression GetExpression <T>(
IPredicate predicate,
MemberExpression member,
PropertyMap pm)
{
if (predicate is IBetweenPredicate)
{
IBetweenPredicate bPredicate = predicate as IBetweenPredicate;
Expression gte = Expression.GreaterThanOrEqual(member, Expression.Convert(Expression.Constant(bPredicate.Value), pm.PropertyInfo.PropertyType));
Expression lte = Expression.LessThanOrEqual(member, Expression.Convert(Expression.Constant(bPredicate.Value2), pm.PropertyInfo.PropertyType));
if (bPredicate.Not)
{
return(Expression.Not(Expression.AndAlso(gte, lte)));
}
return(Expression.AndAlso(gte, lte));
}
else if (predicate is IFieldPredicate)
{
return(GetExpression <T>(predicate as IFieldPredicate, member, pm));
}
return(null);
}
public void MySQLBuildTest()
{
IDapperExtensionsConfiguration conf = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new MySqlDialect());
SqlGeneratorImpl sqlGeneratorImpl = new SqlGeneratorImpl(conf);
IFieldPredicate nameFieldPredicate = Predicates.Field <User>(p => p.Name, Operator.Like, "уе%");
string namesql = nameFieldPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>());
Assert.Equal("(`User`.`Name` LIKE @Name_0)", namesql);
List <string> valueList = new List <string>()
{
"1", "2", "3"
};
IFieldPredicate nameFieldPredicate2 = Predicates.Field <User>(p => p.Name, Operator.Eq, valueList);
string namesql2 = nameFieldPredicate2.GetSql(sqlGeneratorImpl, new Dictionary <string, object>());
Assert.Equal("(`User`.`Name` IN (@Name_0, @Name_1, @Name_2))", namesql2);
IBetweenPredicate idFieldPredicate = Predicates.Between <User>(p => p.Name, new BetweenValues {
Value1 = 1, Value2 = 10
}, true);
string idsql = idFieldPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>());
Assert.Equal("(`User`.`Name` NOT BETWEEN @Name_0 AND @Name_1)", idsql);
IPropertyPredicate propertyPredicate = Predicates.Property <User, Role>(u => u.RoleId, Operator.Eq, r => r.Id, true);
string propertysql = propertyPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>());
Assert.Equal("(`User`.`RoleId` <> `Role`.`Id`)", propertysql);
IExistsPredicate existsPredicate = Predicates.Exists <User>(nameFieldPredicate, true);
string existssql = existsPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>());
Assert.Equal("(NOT EXISTS (SELECT 1 FROM `User` WHERE (`User`.`Name` LIKE @Name_0)))", existssql);
IList <IPredicate> predList = new List <IPredicate> {
nameFieldPredicate, idFieldPredicate
};
IPredicateGroup predGroup1 = Predicates.Group(GroupOperator.And, predList.ToArray());
IList <IPredicate> predList2 = new List <IPredicate> {
predGroup1, existsPredicate
};
IPredicateGroup predGroup2 = Predicates.Group(GroupOperator.Or, predList2.ToArray());
string groupsql = predGroup2.GetSql(sqlGeneratorImpl, new Dictionary <string, object>());
string res = "(((`User`.`Name` LIKE @Name_0) AND (`User`.`Name` NOT BETWEEN @Name_1 AND @Name_2)) OR (NOT EXISTS (SELECT 1 FROM `User` WHERE (`User`.`Name` LIKE @Name_3))))";
Assert.Equal(groupsql, res);
}
private static string GetList()
{
Stopwatch sw = new Stopwatch();
sw.Start();
IPredicateGroup group = new PredicateGroup()
{
Operator = GroupOperator.Or, Predicates = new List <IPredicate>()
};
IBetweenPredicate bet = Predicates.Between <Person>(f => f.Sex, new BetweenValues {
Value1 = 0, Value2 = 2
});
group.Predicates.Add(bet);
IPredicate pre = Predicates.Field <Person>(f => f.FirstName, Operator.Like, "First");
group.Predicates.Add(pre);
IList <Person> result = PersonRepository.Value.GetList(group);
sw.Stop();
return(string.Format("共获取{0}条记录，耗时：{1}毫秒", result.Count, sw.ElapsedMilliseconds));
}
