/// <summary>
/// 根据条件查询
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="filter">查询条件：lambda条件过滤表达式</param>
/// <param name="order">排序表达式</param>
/// <param name="fields">查询字段：lambda字段表达式【可多组】</param>
/// <returns>列表实体</returns>
public static List <T> Select <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, params Expression <Func <T, object> >[] fields)
{
string strWhere = string.Empty;
string strFields = string.Empty;
string orderBy = string.Empty;
if (filter != null)
{
strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer);
}
if (order != null)
{
orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList());
}
foreach (var f in fields)
{
var fieldName = ExpressionField.GetFieldName <T>(f);
strFields = strFields + "," + fieldName;
}
if (string.IsNullOrEmpty(strFields))
{
strFields = "*";
}
else
{
strFields = strFields.Trim(',');
}
List <T> list = new List <T>();
StringBuilder strSql = new StringBuilder();
var model = Activator.CreateInstance <T>();
Type t = model.GetType(); //获得该类的Type
string tbName = GetTableName(t); //表名
strSql.AppendFormat("select {0} ", strFields);
strSql.AppendFormat(" from {0} ", tbName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.AppendFormat(" where {0} ", strWhere);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(orderBy);
}
var dt = DbHelperSQL.Query(strSql.ToString(), null).Tables[0];
list = ModelUtil.DataTableParse <T>(dt);
return(list);
}
/// <summary>
/// 多表联合查询(left join)
/// </summary>
/// <typeparam name="T">返回的数据实体类型</typeparam>
/// <typeparam name="T1">表1</typeparam>
/// <typeparam name="T2">表2</typeparam>
/// <param name="joinExpression">联接条件</param>
/// <param name="filter">查询条件</param>
/// <param name="order">排序</param>
/// <param name="fields">查询字段</param>
/// <returns>数据实体列表</returns>
protected static string JoinScript <T, T1, T2>(Expression <Func <T1, T2, bool> > joinExpression, Expression <Func <T1, T2, bool> > filter = null, OrderBy <T> order = null,
params Expression <Func <T1, T2, object> >[] fields)
{
string join = string.Empty;
string strWhere = string.Empty;
string strFields = string.Empty;
string orderBy = string.Empty;
join = LinqCompileExt.GetJoinByLambda(joinExpression, DataBaseType.SqlServer);
if (filter != null)
{
strWhere = LinqCompileExt.GetJoinByLambda(filter, DataBaseType.SqlServer);
}
if (order != null)
{
orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList());
}
foreach (var f in fields)
{
var fieldName = ExpressionField.GetFieldName <T1, T2>(f);
strFields = strFields + "," + fieldName;
}
if (string.IsNullOrEmpty(strFields))
{
strFields = "*";
}
else
{
strFields = strFields.Trim(',');
}
StringBuilder strSql = new StringBuilder();
var model = Activator.CreateInstance <T>();
Type[] agrs = new Type[] { typeof(T1), typeof(T2) }; //获得该类的Type
string tbName = JoinTable(joinExpression.Parameters, agrs); //表名
strSql.AppendFormat("select {0} ", strFields);
strSql.AppendFormat(" from {0} ", tbName);
strSql.AppendFormat(" on {0}", join);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.AppendFormat(" where {0} ", strWhere);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(orderBy);
}
return(strSql.ToString());
}
/// <summary>
/// 查询当前指定字段的值
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="filter">查询条件：lambda条件过滤表达式</param>
/// <param name="order">排序表达式</param>
/// <param name="field">查询字段：lambda字段表达式</param>
/// <returns>返回当前行的查询字段值</returns>
public static object GetValue <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, Expression <Func <T, object> > field = null)
{
string strWhere = string.Empty;
string strFields = string.Empty;
string orderBy = string.Empty;
if (field == null)
{
throw new Exception("请指定查询字段");
//return null;
}
if (filter != null)
{
strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer);
}
if (order != null)
{
orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList());
}
strFields = ExpressionField.GetFieldName <T>(field);
List <T> list = new List <T>();
StringBuilder strSql = new StringBuilder();
var model = Activator.CreateInstance <T>();
Type t = model.GetType(); //获得该类的Type
string tbName = GetTableName(t); //表名
strSql.AppendFormat("select {0} ", strFields);
strSql.AppendFormat(" from {0} ", tbName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.AppendFormat(" where {0} ", strWhere);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(orderBy);
}
return(DbHelperSQL.GetSingle(strSql.ToString()));
}