public static IQueryable <T> ApplySorting <T>(this IQueryable <T> query, IQueryObject userQuery, Dictionary <string, Expression <Func <T, object> > > sortColumnsMap)
{
if (String.IsNullOrWhiteSpace(userQuery.SortBy) || !sortColumnsMap.ContainsKey(userQuery.SortBy))
{
return(query);
}
if (userQuery.IsSortAscending)
{
return(query.OrderBy(sortColumnsMap[userQuery.SortBy]));
}
else
{
return(query.OrderByDescending(sortColumnsMap[userQuery.SortBy]));
}
}
public static IQueryable <T> ApplyPaging <T>(this IQueryable <T> query, IQueryObject queryObj)
{
if (queryObj.Page <= 0)
{
queryObj.Page = 1;
}
if (queryObj.PageSize <= 0)
{
queryObj.PageSize = 10;
}
return(query
.Skip((queryObj.Page - 1) * queryObj.PageSize)
.Take(queryObj.PageSize));
}
private static IQueryObject FixPagingData(this IQueryObject queryObj)
{
// set default for page number
if (queryObj.Page <= 0)
{
queryObj.Page = DefaultPage;
}
// set default for PageSize
if (queryObj.PageSize <= 0)
{
queryObj.PageSize = DefaultPageSize;
}
return(queryObj);
}
internal GroupPlanNode(
PlanNode previous,
IQueryObject pipe,
Type inputKey, Type outputKey, Type payloadType,
Expression keySelector,
int shuffleId, int totalBranches,
bool isShuffle, bool isGenerated, string errorMessages)
: base(pipe, outputKey, payloadType, isGenerated, errorMessages)
{
this.Previous = previous;
this.GroupingExpression = keySelector;
this.InputKeyType = inputKey;
this.ShuffleId = shuffleId;
this.TotalBranches = totalBranches;
this.IsShuffle = isShuffle;
}
public static IQueryable <T> ApplyOrdering <T>(this IQueryable <T> query, IQueryObject queryObj, Dictionary <string, Expression <Func <T, object> > > columnsMap)
{
if (string.IsNullOrWhiteSpace(queryObj.SortBy) || !columnsMap.ContainsKey(queryObj.SortBy))
{
return(query);
}
if (queryObj.IsSortAscending)
{
return(query = query.OrderBy(columnsMap[queryObj.SortBy]));
}
else
{
return(query = query.OrderByDescending(columnsMap[queryObj.SortBy]));
}
}
public async override Task <Tuple <int, IList <TEntity> > > GetListAsync(IQueryObject <TEntity> query)
{
var parameters = new DynamicParameters();
var select = ExpressionHelper.Select <TEntity>();
var count = ExpressionHelper.Count <TEntity>();
foreach (var where in query.QueryExpression)
{
select.Where(where);
}
foreach (var where in query.QueryExpression)
{
count.Where(where);
}
select.Where(x => x.IsDeleted == false);
count.Where(x => x.IsDeleted == false);
if (query.OrderSort == SortOrder.ASC)
{
select.OrderBy(query.OrderField);
}
else if (query.OrderSort == SortOrder.DESC)
{
select.OrderByDesc(query.OrderField);
}
else
{
select.OrderByDesc(x => x.CreationTime);
}
if (query.Count >= 0)
{
select.Limit(query.Skip, query.Count);
}
foreach (KeyValuePair <string, object> item in select.DbParams)
{
parameters.Add(item.Key, item.Value);
}
using (Connection)
{
var customerRepresentativeList = await ReadConnection.QueryAsync <TEntity>(select.Sql, parameters);
int totalCount = await ReadConnection.QuerySingleAsync <int>(count.Sql, parameters);
return(new Tuple <int, IList <TEntity> >(totalCount, customerRepresentativeList.ToList()));
}
}
public static IQueryable <T> ApplyOrdering <T>(this IQueryable <T> query, IQueryObject queryObject, Dictionary <string, Expression <Func <T, object> > > columnsMap)
{
//be sure to include only existing column names
if (String.IsNullOrWhiteSpace(queryObject.SortBy) || !columnsMap.ContainsKey(queryObject.SortBy))
{
return(query);
}
if (queryObject.isSortAscending)
{
return(query.OrderBy(columnsMap[queryObject.SortBy]));
}
else
{
return(query.OrderByDescending(columnsMap[queryObject.SortBy]));
}
}
public static IQueryable <T> ApplyOrdering <T> (this IQueryable <T> query, IQueryObject queryObj, Dictionary <string, Expression <Func <T, object> > > columnsMap)
{
// edge case: id not exist, or parameters are not assigned.
if (String.IsNullOrWhiteSpace(queryObj.SortBy) || !columnsMap.ContainsKey(queryObj.SortBy))
{
return(query);
}
if (queryObj.IsSortAsceding)
{
return(query.OrderBy(columnsMap[queryObj.SortBy]));
}
else
{
return(query.OrderByDescending(columnsMap[queryObj.SortBy]));
}
}
public static List <T> ApplyOrdering <T>(this List <T> list, IQueryObject <T> filter)
{
var query = list.AsQueryable();
if (filter.Sort != null && filter.Sort.Any())
{
var isSortAscending = !filter.Sort.Any(c => c.Contains("-"));
filter.Sort = filter.Sort.Select(c => { c = c.ToLower().Replace("-", ""); return(c); }).ToList();
var map = filter.Map();
if (filter.Sort.Any())
{
return((isSortAscending) ? query.OrderBy(map[filter.Sort[0]]).ToList() : query.OrderByDescending(map[filter.Sort[0]]).ToList());
}
}
return(query.ToList());
}
internal GroupedWindowPlanNode(
PlanNode previous,
IQueryObject pipe,
Type keyType,
Type inputType,
Type outputType,
IAggregate <TInput, TState, TResult> aggregate,
Expression keySelector,
Expression finalResultSelector,
bool isGenerated,
string errorMessages)
: base(previous, pipe, keyType, outputType, inputType, isGenerated, errorMessages)
{
this.Aggregate = aggregate;
this.KeySelector = keySelector;
this.FinalResultSelector = finalResultSelector;
}
internal UngroupPlanNode(
PlanNode previous,
IQueryObject pipe,
Type inputKey,
Type outputKey,
Type groupPayloadType,
Type payloadType,
Expression resultSelector,
bool isGenerated,
string errorMessages)
: base(pipe, outputKey, payloadType, isGenerated, errorMessages)
{
this.Previous = previous;
this.ResultExpression = resultSelector;
this.InputKeyType = inputKey;
this.GroupPayloadType = groupPayloadType;
}
public static IQueryable <T> ApplyFiltering <T>(this IQueryable <T> query, IQueryObject queryObj, Dictionary <string, Expression <Func <T, bool> > > columnsMap)
{
if (queryObj != null)
{
foreach (var prop in queryObj.GetType().GetProperties())
{
if (prop.GetValue(queryObj) != null)
{
if (columnsMap.ContainsKey(prop.Name))
{
query = query.Where(columnsMap[prop.Name]);
}
}
}
}
return(query);
}
internal QueryResult GetFilledDataTable(IQueryObject obj)
{
var adapter = CreateDbAdapter();
var cmd = obj.GetDbCommand();
adapter.SelectCommand = cmd;
var cnn = GetActualConnection(obj.DbConnector.InstanceConnectionString);
try
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
adapter.SelectCommand.Connection = cnn;
adapter.SelectCommand.Transaction = GetActualTransaction();
var result = GetFilledDataTable(adapter);
long totalRecords;
if (obj.GetCount)
{
var countCmd = CreateDbCommand(obj.CountSql);
var dbParameters = new DbParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(dbParameters, 0);
cmd.Parameters.Clear();
countCmd.Parameters.AddRange(dbParameters);
totalRecords = (long)GetRealScalarData(countCmd);
}
else
{
totalRecords = result.Rows.Count;
}
return(new QueryResult {
DataTable = result, TotalRecords = totalRecords
});
}
finally
{
if (NeedToDisposeActualConnection)
{
cnn.Dispose();
}
}
}
public static IQueryable <T> ApplyPaging <T>(this IQueryable <T> queryable, IQueryObject queryObj)
{
if (queryObj == null)
{
return(queryable);
}
if (queryObj.PageNumber <= 0)
{
queryObj.PageNumber = 1;
}
if (queryObj.PageSize <= 0)
{
queryObj.PageSize = 10;
}
return(queryable.Skip((queryObj.PageNumber - 1) * queryObj.PageSize).Take(queryObj.PageSize));
}
public static IQueryable <Car> ApplyOrdering(this IQueryable <Car> query, IQueryObject queryObj, Dictionary <string, Expression <Func <Car, object> > > columnsMap)
{
// check for the existence of the key in the columnsMap
if (String.IsNullOrWhiteSpace(queryObj.SortBy) || !columnsMap.ContainsKey(queryObj.SortBy))
{
// if we do have a column with that key
return(query);
}
if (queryObj.IsSortAscending)
{
// sort by ascending using the name of the key(column name)
return(query.OrderBy(columnsMap[queryObj.SortBy]));
}
else
{
// sort by descending using the name of the key(column name)
return(query.OrderByDescending(columnsMap[queryObj.SortBy]));
}
}
/// <summary>
/// 根据数据模型创建执行对象。
/// </summary>
/// <param name="model">数据模型</param>
/// <returns></returns>
public static IQueryObject CreateQueryObject(object model = null)
{
if (model == null)
{
return(CreateEmptyQueryObject());
}
PropertyInfo[] ps = model.GetType().GetProperties();
IQueryObject q = CreateEmptyQueryObject();
if (q == null)
{
return(null);
}
q.TableName = model.ToString();
int i = q.TableName.LastIndexOf('.');
if (i > -1)
{
q.TableName = q.TableName.Substring(i + 1, q.TableName.Length - i - 1);
}
//解析LinQ对象
foreach (var info in ps)
{
foreach (ColumnAttribute attr in info.GetCustomAttributes(typeof(ColumnAttribute), false))
{
if (attr.IsPrimaryKey)
{
q.PrimaryKey = info.Name;
}
if (info.CanRead)
{
q.Add(info.Name);
}
}
}
return(q);
}
public static IQueryable <T> ApplyPaging <T>(this IQueryable <T> query, IQueryObject queryObj)
{
if (queryObj.ApplyPagingFromClient)
{
return(query);
}
if (queryObj.Page <= 0)
{
queryObj.Page = 1;
}
if (queryObj.PageSize <= 0)
{
queryObj.PageSize = 10;
}
query = query.Skip((queryObj.Page - 1) * queryObj.PageSize).Take(queryObj.PageSize);
return(query);
}
public PublicacoesController(
IQueryObject <PublicacaoQuery> publicacaoQuery,
ICommandHandler <CadastrarPublicacaoCommand> cadastrarPublicacao,
ICommandHandler <InativarPublicacaoCommand> inativarPublicacao,
ICommandHandler <ComentarPublicacaoCommand> comentarPublicacao,
ICommandHandler <RecompartilharPublicacaoCommand> recompartilharPublicacao,
ICommandHandler <UpvotePublicacaoCommand> upvotePublicacao,
ICommandHandler <DownvotePublicacaoCommand> downvotePublicacao,
IServiceContext serviceContext)
{
_publicacaoQuery = publicacaoQuery;
_cadastrarPublicacao = cadastrarPublicacao;
_inativarPublicacao = inativarPublicacao;
_comentarPublicacao = comentarPublicacao;
_recompartilharPublicacao = recompartilharPublicacao;
_upvotePublicacao = upvotePublicacao;
_downvotePublicacao = downvotePublicacao;
_serviceContext = serviceContext;
}
public static IQueryable <T> ApplySorting <T>(this IQueryable <T> query, IQueryObject filter, Dictionary <string, Expression <Func <T, object> > > columnMap)
{
// foreach(var property in filter.GetType().GetProperties())
// {
// if(property.Name=="")
// }
if (string.IsNullOrWhiteSpace(filter.SortBy) || !columnMap.ContainsKey(filter.SortBy))
{
return(query);
}
if (filter.IsSortAscending)
{
return(query.OrderBy(columnMap[filter.SortBy]));
}
else
{
return(query.OrderByDescending(columnMap[filter.SortBy]));
}
}
public async Task <QueryResult <BnkseekEntity> > GetByQueryAsync(IQueryObject queryObj = null, bool includeReleted = false)
{
IQueryable <BnkseekEntity> query = null;
if (!includeReleted)
{
query = _context.BnkseekRecords;
}
else
{
// ? If it's true then returning and the related data, also.
query = _context.BnkseekRecords
.Include(b => b.PznEntity)
.Include(b => b.RegEntity)
.Include(b => b.TnpEntity)
.Include(b => b.UerEntity);
}
// ? Filering out by specified filters in the query object
if (queryObj != null && !string.IsNullOrWhiteSpace(queryObj.NEWNUM))
{
query = query.Where(b => b.NEWNUM.ToUpper().Contains(queryObj.NEWNUM.ToUpper()));
}
if (queryObj != null && !string.IsNullOrWhiteSpace(queryObj.REGN))
{
query = query.Where(b => b.REGN.ToUpper().Contains(queryObj.REGN.ToUpper()));
}
if (queryObj != null && !string.IsNullOrWhiteSpace(queryObj.PZN))
{
query = query.Where(b => b.PZN.ToUpper() == queryObj.PZN.ToUpper());
}
return(new QueryResult <BnkseekEntity>
{
TotalItems = await query.CountAsync(),
Items = query.ApplyPaging(queryObj)
});
}
public static string GetSqlLog(this ILog logger, IQueryObject queryObject)
{
if (queryObject == null)
{
return(null);
}
var query = queryObject.Query.ToString();
var sql = new StringBuilder();
sql.AppendLine(query);
if (queryObject.Parameters != null && queryObject.Parameters.Count > 0)
{
sql.AppendLine("Parameters:");
foreach (var item in queryObject.Parameters)
{
sql.AppendInvariantLine("{0}: {1}", item.Key, item.Value);
}
}
return(sql.ToString());
}
/// <summary>
/// 根据查询条件查询总数
/// </summary>
/// <param name="query">查询对象</param>
/// <returns></returns>
public override async Task <int> CountAsync(IQueryObject <TEntity> query)
{
var parameters = new DynamicParameters();
var count = ExpressionHelper.Count <TEntity>();
foreach (var where in query.QueryExpression)
{
count.Where(where);
}
foreach (KeyValuePair <string, object> item in count.DbParams)
{
parameters.Add(item.Key, item.Value);
}
using (ReadConnection)
{
var Count = await ReadConnection.QuerySingleAsync <int>(count.Sql, parameters);
return(Count);
}
}
public override Task <Tuple <int, IList <TEntity> > > GetListAsync(IQueryObject <TEntity> query)
{
IQueryable <TEntity> selfQuery = Collection.AsQueryable();
var b = query.QueryExpression.WhereAnd();
selfQuery = selfQuery.Where(b);
if (query.OrderField != null)
{
if (query.OrderSort == SortOrder.ASC)
{
selfQuery = selfQuery.OrderBy(query.OrderField);
}
else
{
selfQuery = selfQuery.OrderByDescending(query.OrderField);
}
}
List <TEntity> items = new List <TEntity>();
if (query.Count == -1)
{
items = selfQuery.ToList();
return(Task.FromResult(new Tuple <int, IList <TEntity> >(items.Count, items)));
}
else
{
items = selfQuery.Skip(query.Skip).Take(query.Count + 1).ToList();
int total = query.Count;
if (items.Count > query.Count)
{
total = query.Count * 2 + query.Skip;
}
return(Task.FromResult(new Tuple <int, IList <TEntity> >(total, items.Take(query.Count).ToList())));
}
}
public void Limit(IQueryObject queryObject, int?offset, int?rowcount)
{
if (queryObject.Builder.Length == 0)
{
return;
}
//if(offset != null)
//{
// if(queryObject.Builder[queryObject.Builder.Length - 1] != '\n')
// {
// queryObject.AppendLine();
// }
// queryObject.AppendLine("OFFSET " + offset);
//}
if (rowcount != null)
{
var query = queryObject.Builder.ToString();
queryObject.Builder.Clear();
queryObject.AppendLine(string.Format("SELECT TOP {0} * FROM ({1}) mssqlprovider", rowcount, query));
}
}
public static IQueryable <T> ApplyOrdering <T>(this IQueryable <T> query, IQueryObject <T> filter)
{
if (filter.Sort != null && filter.Sort.Any())
{
var isSortAscending = !filter.Sort.Any(c => c.Contains("-"));
filter.Sort = filter.Sort.Select(c => { c = c.ToLower().Replace("-", ""); return(c); }).ToList();
var map = filter.Map();
if (filter.Sort.Any())
{
var orderedQuery = (isSortAscending) ? query.OrderBy(map[filter.Sort[0]]) : query.OrderByDescending(map[filter.Sort[0]]);
foreach (var field in filter.Sort.Skip(1))
{
if (!String.IsNullOrWhiteSpace(field) && map.ContainsKey(field))
{
orderedQuery = isSortAscending ? orderedQuery.ThenBy(map[field]) : orderedQuery.ThenBy(map[field]);
}
}
query = orderedQuery;
}
}
return(query);
}
public static IQueryable <T> ApplyOrdering <T>(this IQueryable <T> query, IQueryObject queryObj, Dictionary <string, Expression <Func <T, Object> > > columnsMap)
{
//List<string>columns = new List<string> { "id", "make", "model", "contactName" };
//queryObj.SortBy = ;
//if (!queryObj.SortBy.DefaultIfEmpty(columns))
//{
// queryObj.SortBy = "id";
//}
if (String.IsNullOrWhiteSpace(queryObj.SortBy) || !columnsMap.ContainsKey(queryObj.SortBy))
{
return(query);
}
if (queryObj.IsSortAscending)
{
return(query = query.OrderBy(columnsMap[queryObj.SortBy]));
}
else
{
return(query = query.OrderByDescending(columnsMap[queryObj.SortBy]));
}
}
public static QueryResult <T> ApplyPaging <T>(this IQueryable <T> query, IQueryObject queryObj)
{
if (queryObj == null)
{
throw new ArgumentNullException(nameof(queryObj));
}
if (queryObj.PageSize <= 0)
{
queryObj.PageSize = DefaultPageSize;
}
if (queryObj.CurrentPage <= 0)
{
queryObj.CurrentPage = DefaultCurrentPage;
}
return(new QueryResult <T>
{
ResultSet = query.PageData(queryObj).ToList(),
CurrentPage = queryObj.CurrentPage,
TotalNumberOfPages = GetPageCount(query.Count(), queryObj.PageSize)
});
}
public override async Task <Tuple <int, IList <GoodsInfo> > > GetListAsync(IQueryObject <GoodsInfo> query)
{
StringBuilder sb = new StringBuilder();
sb.Append("select g.*, u.* from goodsinfo g LEFT JOIN userinfo u ON g.UserId=u.Id WHERE 1=1 and g.IsDelete=0 ");
StringBuilder sbCount = new StringBuilder();
sbCount.Append("select COUNT(g.Id) from goodsinfo g LEFT JOIN userinfo u ON g.UserId=u.Id WHERE 1=1 and g.IsDelete=0 ");
var queryandsort = query.QuerySql.FirstOrDefault();
if (query.QuerySql.Count > 0)
{
sb.Append(queryandsort.Key);
sbCount.Append(queryandsort.Key);
}
if (query.Count <= 0)
{
query.Count = int.MaxValue;
}
sb.Append(" LIMIT " + query.Skip + "," + query.Count);
using (ReadConnection)
{
var GoodsList = await ReadConnection.QueryAsync <GoodsInfo, UserInfo, GoodsInfo>(sb.ToString(), (goodsinfo, usersinfo) =>
{
if (goodsinfo != null)
{
goodsinfo.UserInfo = usersinfo;
}
return(goodsinfo);
}, queryandsort.Value);
int totalCount = await ReadConnection.QuerySingleAsync <int>(sbCount.ToString(), queryandsort.Value);
return(new Tuple <int, IList <GoodsInfo> >(totalCount, GoodsList.ToList()));
}
}
public QueryFluent(Repository <TEntity> repository, IQueryObject <TEntity> queryObject)
: this(repository)
{
_expression = queryObject.Query();
}
public virtual IQueryFluent <TEntity> Query(IQueryObject <TEntity> queryObject)
{
return(new QueryFluent <TEntity>(this, queryObject));
}
