public static IDbCommand BuildSelect <T, TProperty>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, bool> > predicate, PageFilter <T, TProperty> pageFilter)
{
if (classMap == null)
{
throw new ArgumentNullException("classMap");
}
if (pageFilter == null)
{
throw new ArgumentNullException("pageFilter");
}
var ctx = new QueryContext(classMap);
var filterColumn = ctx.AliasForColumn();
var columnList = String.Format("row_number() over (order by [{0}] {1}) {2}, {3}",
MemberHelper.GetName(pageFilter.OrderProperty), pageFilter.Desc ? "desc" : "asc", filterColumn,
classMap.Properties.Aggregate(string.Empty, GetColumnListAggregator(ctx.AliasForTable(classMap))));
var command = DbCommandDecorator.Create(connection);
if (predicate != null)
{
var visitor = new PredicateVisitor(ctx);
visitor.Visit(predicate.Body);
foreach (var pair in visitor.Parameters)
{
command.AddParam(pair.Key, pair.Value);
}
command.CommandText = String.Format(SELECT, columnList, classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString());
}
else
{
command.CommandText = String.Format(SELECTALL, columnList, classMap.TableName, ctx.AliasForTable(classMap));
}
var fromParam = command.AddParam(null, pageFilter.From);
var toParam = command.AddParam(null, pageFilter.To);
command.CommandText = String.Format("select {4} from ({0}) d where d.[{1}] between {2} and {3} order by d.[{1}] asc",
command.CommandText, filterColumn, fromParam.ParameterName, toParam.ParameterName,
classMap.Properties.Aggregate(string.Empty, GetPropListAggregator("d")));
return(command);
}