private SqlProvider <T> FormatGetDo(IWhereExpression whereParams, IFieldAnyExpression fieldAnyExpression)
{
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression, 1, Context.QuerySet.IsDistinct);
var fromTableSql = FormatTableName();
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(1);
if (fieldAnyExpression != null)
{
string selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression, 1, true);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
SqlString = $"{selectDistinctSql} from ({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName} where {fieldAnyExpression.WhereClause} {limitSql}";
}
else
{
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
}
return(this);
}
//public SqlProvider<T> FormatToList(LambdaExpression[] selector)
//{
// var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T), Context.QuerySet.TopNum, false, selector);
// var fromTableSql = FormatTableName();
// var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
// var whereSql = whereParams.SqlCmd;
// Params = whereParams.Param;
// var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
// var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(Context.QuerySet.TopNum);
// SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
// return this;
//}
public SqlProvider <T> FormatToListZhanglei(Type type, IFieldAnyExpression fieldAnyExpression = null)
{
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, Context.QuerySet.TopNum, Context.QuerySet.IsDistinct, Context.QuerySet.SelectExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(Context.QuerySet.TopNum);
if (fieldAnyExpression != null)
{
var selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, Context.QuerySet.TopNum, true, Context.QuerySet.SelectExpression);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
SqlString = $"{selectDistinctSql} from ({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName} where {fieldAnyExpression.WhereClause} {limitSql}";
}
else
{
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
}
return(this);
}
/// <summary>
/// 此方式支持除分页投影之外的所有查询方法。
/// </summary>
/// <typeparam name="TField"></typeparam>
/// <param name="Field"></param>
/// <param name="Any"></param>
/// <returns></returns>
public IRepositoryPg <T> FieldAny <TField>(Expression <Func <T, IList <TField> > > Field, Expression <Func <TField, bool> > Any)
{
this.FieldAnyExpression = new FieldAnyExpression <T, TField>(Field, Any, Dialect);
return(this);
}
public SqlProvider <T> FormatGet <TKey>(TKey id, IFieldAnyExpression fieldAnyExpression)
{
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere <T, TKey>(id);
return(FormatGetDo(whereParams, fieldAnyExpression));
}
public SqlProvider <T> FormatGet(IFieldAnyExpression fieldAnyExpression)
{
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
return(FormatGetDo(whereParams, fieldAnyExpression));
}
public SqlProvider <T> FormatToPageList(Type type, int pageIndex, int pageSize, IFieldAnyExpression fieldAnyExpression)
{
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
if (string.IsNullOrEmpty(orderbySql))
{
throw new Exception("分页查询需要排序条件");
}
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, pageSize, Context.QuerySet.IsDistinct, Context.QuerySet.SelectExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(pageSize);
if (fieldAnyExpression != null)
{
var selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, pageSize, true, Context.QuerySet.SelectExpression);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
string newTable = $"({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName}";
string newNewTable = $"{selectDistinctSql} from {newTable} where {fieldAnyExpression.WhereClause} {limitSql}";
SqlString = $"SELECT COUNT(1) from ({newNewTable}) as {ResolveExpression.FieldAnyTableName};";
SqlString += $@"{selectDistinctSql}
FROM ( SELECT *
,ROW_NUMBER() OVER ( {orderbySql} ) AS ROWNUMBER
from {newTable} where {fieldAnyExpression.WhereClause}
) T
WHERE ROWNUMBER > {(pageIndex - 1) * pageSize}
AND ROWNUMBER <= {pageIndex * pageSize} {orderbySql} {limitSql};";
}
else
{
SqlString = $"SELECT COUNT(1) {fromTableSql} {whereSql};";
if (Dialect.pageListDialectEnum == PageListDialectEnum.Mysql)
{
SqlString += $@"{selectSql} {fromTableSql} {whereSql} {orderbySql} limit {(pageIndex - 1) * pageSize},{pageSize}";
}
else
{
SqlString += $@"{selectSql}
FROM ( SELECT *
,ROW_NUMBER() OVER ( {orderbySql} ) AS ROWNUMBER
{fromTableSql}
{whereSql}
) T
WHERE ROWNUMBER > {(pageIndex - 1) * pageSize}
AND ROWNUMBER <= {pageIndex * pageSize} {orderbySql} {limitSql};";
}
}
return(this);
}