public T Single <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao, new()
{
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append($"SELECT {BuildSelectSqlColumn(attr)} FROM {attr.Name}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
if (!string.IsNullOrEmpty(attr.Sort))
{
sql.Append(" ORDER BY " + attr.Sort);
}
var instance = _dapper.QueryFirstOrDefault <T>(sql.ToString(), condition.DapperArguments);
instance?.Inital();
return(instance);
}
/// <summary>
/// Create condition with where and args
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="where"></param>
/// <param name="_whereExp"></param>
/// <returns></returns>
public static Tuple <string, DynamicParameters> BuildWhere <TResult>(Expression <Func <TResult, bool> > _whereExp, string where = null)
{
DynamicParameters args = new DynamicParameters();
if (!string.IsNullOrEmpty(where))
{
return(new Tuple <string, DynamicParameters>(where, args));
}
if (_whereExp == null)
{
return(new Tuple <string, DynamicParameters>("", args));
}
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(_whereExp.Body);
//arg
for (int i = 0; i < conditionBuilder.Arguments.Count(); i++)
{
args.Add("@q__" + i.ToString(), conditionBuilder.Arguments[i]);
}
//sql
return(new Tuple <string, DynamicParameters>(" where " + conditionBuilder.Condition, args));
}
private void BuildWhere(string where = null)
{
if (where != null)
{
trackSql = trackSql.Replace("{where}", where);
return;
}
if (whereExp == null)
{
trackSql = trackSql.Replace("{where}", "");
return;
}
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(whereExp.Body);
//arg
for (int i = 0; i < conditionBuilder.Arguments.Count(); i++)
{
args.Add("@q__" + i.ToString(), conditionBuilder.Arguments[i]);
}
//sql
trackSql = trackSql.Replace("{where}", " where " + conditionBuilder.Condition);
}
public List <T> Query <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao, new()
{
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append($"SELECT {BuildSelectSqlColumn(attr)} FROM {attr.Name}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
if (!string.IsNullOrEmpty(attr.Sort))
{
sql.Append(" ORDER BY " + attr.Sort);
}
var list = _dapper.Query <T>(sql.ToString(), condition.DapperArguments).ToList();
if (list.Count > 0)
{
list.ForEach(x => x.Inital());
}
return(list);
}
private KeyValuePair <string, KeyValuePair <string[], object[]> > GetPropertiesDelete(Expression <Func <V, bool> > expression)
{
ConditionBuilder whereBuilder = new ConditionBuilder(TypeName, this.providerEx);
whereBuilder.Build(expression.Body);
string sql = string.Format("DELETE FROM {0} WHERE {1}", DMClassMap.GetPcDictionary(TypeName).TableName, whereBuilder.Condition);
KeyValuePair <string, KeyValuePair <string[], object[]> > kvp = new KeyValuePair <string, KeyValuePair <string[], object[]> >(sql, new KeyValuePair <string[], object[]>(whereBuilder.WhereParam.ToArray(), whereBuilder.Arguments));
return(kvp);
}
/// <summary>
/// group by
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="selectAction"></param>
/// <param name="iExpression"></param>
/// <returns></returns>
public static LinqQueryAction <T> GroupBy <T>(
this LinqQueryAction <T> selectAction,
Expression <Func <T, object[]> > iExpression)
{
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(iExpression.Body);
selectAction.CAction.SqlGroupBy(conditionBuilder.Condition);
return(selectAction);
}
/// <summary>
/// order by
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="selectAction"></param>
/// <param name="iExpression"></param>
/// <param name="eByEnum"></param>
/// <returns></returns>
public static LinqQueryAction <T> OrderBy <T>(
this LinqQueryAction <T> selectAction,
Expression <Func <T, object> > iExpression, OrderByEnum eByEnum)
{
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(iExpression.Body);
selectAction.CAction.SqlOrderBy(conditionBuilder.Condition, eByEnum);
return(selectAction);
}
/// <summary>
/// 新增，修改的条件的添加
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="action"></param>
/// <param name="iExpression">直接添加实体类</param>
/// <returns></returns>
public static LinqQueryAction <T> SqlValue <T>(this LinqQueryAction <T> action,
Expression <Func <T, T> > iExpression)
{
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(iExpression.Body);
if (conditionBuilder.ConObjects.Count > 0)
{
action.CAction.SetDbEntity((BaseEntity)conditionBuilder.ConObjects[0]);
}
return(action);
}
/// <summary>
/// 新增，修改的条件的添加
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="action"></param>
/// <param name="iExpression"></param>
/// <returns></returns>
public static LinqQueryAction <T> SqlValue <T>(this LinqQueryAction <T> action,
Expression <Func <T, bool> > iExpression)
{
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(iExpression.Body);
for (int i = 0; i < conditionBuilder.ConObjects.Count; i++)
{
action.CAction.SqlKeyValue(conditionBuilder.ConObjects[i].ToString(), conditionBuilder.Arguments[i]);
}
return(action);
}
/// <summary>
/// 多个新增同时操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="action"></param>
/// <param name="iExpression">直接添加实体类</param>
/// <returns></returns>
public static LinqQueryAction InsertList(this LinqQueryAction action,
Expression <Func <List <BaseEntity> > > iExpression)
{
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(iExpression.Body);
if (conditionBuilder.Arguments.Length > 0)
{
List <BaseEntity> entities = ((List <BaseEntity>)conditionBuilder.Arguments[0]).Cast <BaseEntity>().ToList();
((InserAction)action.CAction).InsertList(entities);
}
return(action);
}
private KeyValuePair <string, KeyValuePair <string[], object[]> > GetPropertiesCount(Expression <Func <V, bool> > expression)
{
ConditionBuilder whereBuilder = new ConditionBuilder(TypeName, this.providerEx);
string whereStr = "";
if (expression != null)
{
whereBuilder.Build(expression.Body);
whereStr = "WHERE " + whereBuilder.Condition;
}
string sql = string.Format("SELECT COUNT(0) FROM {0} {1}", DMClassMap.GetPcDictionary(TypeName).TableName, whereStr);
KeyValuePair <string, KeyValuePair <string[], object[]> > kvp = new KeyValuePair <string, KeyValuePair <string[], object[]> >(sql, new KeyValuePair <string[], object[]>(whereBuilder.WhereParam.ToArray(), whereBuilder.Arguments));
return(kvp);
}
/// <summary>
/// 查找，删除，更新的where条件的添加
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataBaseAction"></param>
/// <param name="wherePredicate"></param>
/// <returns></returns>
public static LinqQueryAction <T> Where <T>(this LinqQueryAction <T> dataBaseAction, Expression <Func <T, bool> > wherePredicate)
where T : BaseEntity
{
string command = string.Empty;
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(wherePredicate.Body);
if (!String.IsNullOrEmpty(conditionBuilder.Condition))
{
command = conditionBuilder.Condition;
}
dataBaseAction.CAction.SqlWhere(command);
return(dataBaseAction);
}
private KeyValuePair <string, KeyValuePair <string[], object[]> > GetPropertiesSelect(Expression <Func <V, object[]> > expression, Expression <Func <V, bool> > where, Expression <Func <OrderExpression <V>, object> > order = null)
{
ConditionBuilder whereBuilder = new ConditionBuilder(TypeName, this.providerEx);
string whereStr = "";
if (where != null)
{
whereBuilder.Build(where.Body);
whereStr += "WHERE " + whereBuilder.Condition;
}
string columns = GetProperties(expression);
string orderSql = GetOrderSql(order);
string sql = string.Format("SELECT {0} FROM {1} {2} {3}", columns, DMClassMap.GetPcDictionary(TypeName).TableName, whereStr, orderSql);
KeyValuePair <string, KeyValuePair <string[], object[]> > kvp = new KeyValuePair <string, KeyValuePair <string[], object[]> >(sql, new KeyValuePair <string[], object[]>(whereBuilder.WhereParam.ToArray(), whereBuilder.Arguments));
return(kvp);
}
public int Update <T>(T instance, Expression <Func <T, bool> > whereBy) where T : class, IDao
{
var listCol = new List <string>();
var sqlPara = new DynamicParameters(new { });
foreach (var pi in instance.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
var attrCol = GetColumnAttr(pi);
if (attrCol != null)
{
var value = pi.GetValue(instance, null);
listCol.Add($"{attrCol.Name} = @{attrCol.Name}");
sqlPara.Add(attrCol.Name, value == null && pi.PropertyType == typeof(string) ? string.Empty : value);
}
}
var attr = GetTableAttr <T>();
if (listCol.Count > 0 && sqlPara.ParameterNames.Any())
{
var sql = new StringBuilder();
sql.Append($"UPDATE {attr.Name} SET {string.Join(", ", listCol.ToArray())}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
foreach (var p in condition.DapperArguments)
{
sqlPara.Add(p.Key, p.Value);
}
return(_dapper.Execute(sql.ToString(), sqlPara));
}
return(-1);
}
private void BuildOrder(string order = null)
{
if (order != null)
{
trackSql = trackSql.Replace("{order}", order);
return;
}
if (orderExp == null)
{
trackSql = trackSql.Replace("{order}", " order by 1 ");
return;
}
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(orderExp.Body);
trackSql = trackSql.Replace("{order}", string.Format(" order by [{0}] {1} ", conditionBuilder.Condition, order));
}
public int Count <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao
{
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append($"SELECT COUNT(*) FROM {attr.Name}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
return(_dapper.ExecuteScalar <int>(sql.ToString(), condition.DapperArguments));
}
public static string BuildOrderBy <TResult>(Expression <Func <TResult, object> > orderExp, string aseOrDesc, string orderby = null)
{
if (!string.IsNullOrEmpty(orderby))
{
return(orderby);
}
var currenttype = typeof(TResult);
var idProps = GetIdProperties(currenttype).ToList();
if (string.IsNullOrEmpty(orderby) && orderExp == null)
{
return(GetColumnName(idProps.First()) + aseOrDesc);
}
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(orderExp.Body);
return(Common.Encapsulate(_encapsulation, conditionBuilder.Condition) + aseOrDesc);
}
public List <R> ToListAs <R>(Expression <Func <T, R> > singleSelector)
{
if (CanCache())
{
return(this.ToQueryable().Select(singleSelector.Compile()).ToList());
}
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(singleSelector.Body);
string column = conditionBuilder.Condition;
BuildColumns(column);
BuildWhere();
BuildOrder();
BuildLimit();
var list = Dapper.SqlMapper.Query <R>(Conn, trackSql, args, BaseModule.GetTran()).ToList();
return(list);
}
public List <T> Query <T>(IPager pager, Expression <Func <T, bool> > whereBy, string orderBy = null)
where T : class, IDao, new()
{
var attr = GetTableAttr <T>();
// Generate WhereBy Clause
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
// Generate OrderBy Clause
var strOrderBy = !string.IsNullOrEmpty(attr.Sort) ? attr.Sort : attr.Key;
if (!string.IsNullOrEmpty(orderBy))
{
strOrderBy = orderBy;
}
// Get TotalCount First
var countSql = $"SELECT COUNT({attr.Key}) AS TotalCount FROM {attr.Name} WHERE {condition.Condition}";
pager.SetTotalCount(_dapper.ExecuteScalar <int>(countSql, condition.DapperArguments));
// Build Sql and Execute
var innerSql = $"SELECT ROW_NUMBER() OVER(ORDER BY {strOrderBy}) AS RowNo, * FROM {attr.Name} WHERE {condition.Condition}";
string sql =
$"SELECT {BuildSelectSqlColumn(attr)} FROM ({innerSql}) AS t WHERE t.RowNo BETWEEN {pager.CurrentPage * pager.PagingSize + 1} AND {(pager.CurrentPage + 1) * pager.PagingSize}";
var list = _dapper.Query <T>(sql, condition.DapperArguments).ToList();
if (list.Count > 0)
{
list.ForEach(x => x.Inital());
}
return(list);
}
public R FirstAs <R>(Expression <Func <T, R> > singleSelector)
{
if (CanCache())
{
return(this.ToQueryable().Select(singleSelector.Compile()).FirstOrDefault());
}
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.Build(singleSelector.Body);
string column = conditionBuilder.Condition;
limitForm = 0;
limitLength = 1;
BuildColumns(column);
BuildWhere();
BuildOrder();
BuildLimit();
var obj = Dapper.SqlMapper.QueryFirstOrDefault <R>(Conn, trackSql, args, BaseModule.GetTran());
return(obj);
}
private KeyValuePair <string, KeyValuePair <string[], object[]> > GetPropertiesUpdate(Expression <Func <V> > expression, Expression <Func <V, bool> > where)
{
ConditionBuilder whereBuilder = new ConditionBuilder(TypeName, this.providerEx);;
whereBuilder.Build(where.Body);
var updateExp = expression.Body as MemberInitExpression;
var updateList = updateExp.Bindings.Cast <MemberAssignment>().Select(a => new
{
Name = a.Member.Name,
Value = GetMemeberValue(a)
});
StringBuilder sqlUpdate = new StringBuilder();
int i = 0;
List <string> param = new List <string>();
List <object> pvalue = new List <object>();
var pc = DMClassMap.GetPcDictionary(TypeName);
foreach (var item in updateList)
{
string column = pc.GetColumn(item.Name);
string pname = string.Format("{2}p{0}__{1}", i.ToString(), column, providerEx.ParameterType);
if (!string.IsNullOrEmpty(column))
{
sqlUpdate.AppendFormat(" {0} = {1},", column, pname);
param.Add(pname);
pvalue.Add(item.Value);
i++;
}
}
param.AddRange(whereBuilder.WhereParam);
pvalue.AddRange(whereBuilder.Arguments);
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", pc.TableName, sqlUpdate.ToString().TrimEnd(','), whereBuilder.Condition);
KeyValuePair <string, KeyValuePair <string[], object[]> > kvp = new KeyValuePair <string, KeyValuePair <string[], object[]> >(sql, new KeyValuePair <string[], object[]>(param.ToArray(), pvalue.ToArray()));
return(kvp);
}