LINQ总结
近自己写个小软件，琢磨着不用之前那种拼接SQL的三层方法，DAL层想用一下Linq。总结如下：
通常都是GET，POST：
先来一波简版的：
///查
public List<SYS_User> GetALL()
{
return entity.SYS_Users.ToList();
}
///增
public int Add(SYS_User f)
{
entity.SYS_Users.Add(f);
return entity.SaveChanges();
}
///改
public int Edit(SYS_User f)
{
entity.Entry(f).State = EntityState.Modified;
return entity.SaveChanges();
}
///删
public int Delete(int id)
{
SYS_User f = entity.SYS_Users.Find(id);
entity.SYS_Users.Remove(f);
return entity.SaveChanges();
}
看到这些，会想到在实际应用中，不是这样的，光说查询，就会遇到很多花式的查询。
然后去百度，去看大神的帖子，可能是我百度的姿势不对，找到的资料自己总结了一下。
但是还是感觉不是很舒服，可能我对Linq的理解太浅薄了。下面记录一下：
第一种方法：
/// <summary>
/// 第一种linq封装方法查询
/// </summary>
/// <param name="keywords"></param>
/// <returns></returns>
public List<SYS_User> Get(params string[] keywords)
{
var predicate = PredicateBuilder.True<SYS_User>();
foreach (var keyword in keywords)
{
string temp = keyword;
predicate = predicate.And(s => s.User_Name.Contains("1"));
}
//predicate = predicate.And(s => s.User_Name=="1");
//predicate = predicate.Or(s => s.User_Name=="2");
var result = entity.SYS_Users.Where(predicate).ToList();
return result;
}
当然，对应类加个备份吧。
/// <summary>
/// 动态查询
/// 参考URL:https://blog.csdn.net/kongwei521/article/details/27197753
/// </summary>
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
{
// build parameter map (from parameters of second to parameters of first)
var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);
// replace parameters in the second lambda expression with parameters from the first
var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);
// apply composition of lambda expression bodies to parameters from the first expression
return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
{
return first.Compose(second, Expression.And);
}
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
{
return first.Compose(second, Expression.Or);
}
}
public class ParameterRebinder : ExpressionVisitor
{
private readonly Dictionary<ParameterExpression, ParameterExpression> map;
public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
{
this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
}
public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
{
return new ParameterRebinder(map).Visit(exp);
}
protected override Expression VisitParameter(ParameterExpression p)
{
ParameterExpression replacement;
if (map.TryGetValue(p, out replacement))
{
p = replacement;
}
return base.VisitParameter(p);
}
}
第二种方法：
/// <summary>
/// 第二种linq封装方法动态查询
/// </summary>
public void GetDong()
{
#region 试水
var query = SpecificationBuilder.Create<SYS_User>();
query = query.Equals(d => d.User_Name, "1");
query = query.Equals(d => d.Phone, "1");
var result = entity.SYS_Users.Where(query.Predicate).ToList();
#endregion
#region 执行Json动态查询
//{a => (((a.User_Name == "1") And (a.Phone == "1")) Or (a.Phone == "ccccc"))}
string queryJson = "{\"[EQUAL][And]User_Name\":\"1\",\"[EQUAL][And]Phone\":\"1,ccccc\"}";
if (!string.IsNullOrEmpty(queryJson))
{
var predicate = Utils.GetSerchExtensions<SYS_User>(queryJson);
var result1 = entity.SYS_Users.Where(predicate).ToList();
//query.Predicate = query.Predicate.And(predicate);
}
#endregion
#region 组合单个查询条件
QueryEntity queryEntity = new QueryEntity()
{
LogicOperation = LogicOperation.EQUAL,
PredicateType = PredicateType.AND,
Column = "User_Name",
Value = "1"
};
var qqqqq = Utils.GetSerchExtensions<SYS_User>(new List<QueryEntity>() { queryEntity });
var a = entity.SYS_Users.Where(qqqqq).ToList();
#endregion
#region 组合多个查询条件
List<QueryEntity> queryEntities = new List<QueryEntity>();
queryEntities.Add(new QueryEntity
{
LogicOperation = LogicOperation.EQUAL,
PredicateType = PredicateType.AND,
Column = "User_Name",
Value = "1"
});
queryEntities.Add(new QueryEntity
{
LogicOperation = LogicOperation.EQUAL,
PredicateType = PredicateType.AND,
Column = "Phone",
Value = "1"
});
qqqqq = Utils.GetSerchExtensions<SYS_User>(queryEntities);
a = entity.SYS_Users.Where(qqqqq).ToList();
#endregion
}
缺的方法在这：
/// <summary>
/// 手动去拼接动态表达式
/// </summary>
/// <typeparam name="T"></typeparam>
public class ExpressionHandle<T> where T : class
{
public Expression PrepareConditionLambda(IList<ParamObject> paramList, ParameterExpression paramExp)
{
List<Expression> expList;
if (null == paramList)
{
expList = new List<Expression>(1);
var valueEqual = Expression.Constant(1);
var expEqual = Expression.Equal(valueEqual, valueEqual);
expList.Add(expEqual);
}
else
{
expList = new List<Expression>(paramList.Count);
#region 筛选条件
foreach (var p in paramList)
{
var exp = Expression.Property(paramExp, p.Name);
var propertyType = typeof(T).GetProperty(p.Name).PropertyType; //得到此字段的数据类型
var value = propertyType == typeof(Guid?) ? new Guid(p.Value.ToString()) : Convert.ChangeType(p.Value, TypeHelper.GetUnNullableType(propertyType));
switch (p.Operation)
{
case LogicOperation.LIKE:
var containsMethod = typeof(string).GetMethod("Contains");
var valueLike = Expression.Constant(value, propertyType);
var expLike = Expression.Call(exp, containsMethod, valueLike);
expList.Add(expLike);
break;
case LogicOperation.EQUAL:
var valueEqual = Expression.Constant(value, propertyType); //值
var expEqual = Expression.Equal(exp, valueEqual); //拼接成 t=>t.name=valueEqual
expList.Add(expEqual);
break;
case LogicOperation.LT:
var valueLT = Expression.Constant(value, propertyType); //值
var expLT = Expression.LessThan(exp, valueLT); //拼接成 t=>t.name<valueEqual
expList.Add(expLT);
break;
case LogicOperation.GT:
var valueGT = Expression.Constant(value, propertyType);
var expGT = Expression.GreaterThan(exp, valueGT);
expList.Add(expGT);
break;
case LogicOperation.NOTEQUAL:
var valuent = Expression.Constant(value, propertyType);
var expnt = Expression.NotEqual(exp, valuent);
expList.Add(expnt);
break;
//case LogicOperation.CONTAINS:
// //var valueCT = Expression.Constant(value, propertyType);
// var type = typeof(string);
// var expCT = Expression.Constant(exp, type);
// expList.Add(expCT);
// break;
}
}
#endregion
}
return expList.Aggregate<Expression, Expression>(null, (current, item) => current == null ? item : Expression.And(current, item));
}
}
/// <summary>
///
/// </summary>
public class QueryModel
{
IList<ParamObject> _items = new List<ParamObject>();
public IList<ParamObject> Items
{
get
{
return _items;
}
set
{
_items = value;
}
}
}
/// <summary>
/// 参数
/// </summary>
public class ParamObject
{
public string Name { get; set; }
public LogicOperation Operation { get; set; }
public object Value { get; set; }
}
/// <summary>
/// 排序
/// </summary>
public class SortObject
{
public string OrderBy { get; set; }
public SortOperation Sort { get; set; }
}
/// <summary>
/// 排序方式
/// </summary>
public enum SortOperation
{
ASC,
DESC
}
/// <summary>
/// 查询方式
/// </summary>
public enum LogicOperation
{
LIKE, //包含,模糊查询
EQUAL, //等于
LT, //小于
GT, //大于
CONTAINS, //包含，In查询
NOTEQUAL //不等于
}
/// <summary>
///
/// </summary>
public static class TypeHelper
{
public static Type GetUnNullableType(Type conversionType)
{
if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
//如果是泛型方法，且泛型类型为Nullable<>则视为可空类型
//并使用NullableConverter转换器进行转换
var nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
conversionType = nullableConverter.UnderlyingType;
}
return conversionType;
}
}
/// <summary>
/// 连接方式
/// </summary>
public enum PredicateType
{
AND, OR
}
/// <summary>
/// 查询方式
/// </summary>
public enum SearchType
{
Between, Like, Equals
}
/// <summary>
/// 查询实体
/// </summary>
public class QueryEntity
{
/// <summary>
/// 查询方式
/// </summary>
public LogicOperation LogicOperation { get; set; }
/// <summary>
/// 连接方式
/// </summary>
public PredicateType PredicateType { get; set; }
/// <summary>
/// 列名
/// </summary>
public string Column { get; set; }
/// <summary>
/// 列值
/// </summary>
public object Value { get; set; }
}
}
public class Utils
{
#region 把查询条件拼接为Extensions
/// <summary>
/// 把查询条件拼接为Extensions
/// </summary>
/// <typeparam name="TEntity">查询实体</typeparam>
/// <param name="searchJson">查询条件，例如：[like][or]name:123</param>
/// <returns></returns>
public static Expression<Func<TEntity, bool>> GetSerchExtensions<TEntity>(String searchJson) where TEntity : class, new()
{
try
{
var ja = (JArray)JsonConvert.DeserializeObject("[" + searchJson + "]"); //把查询条件转换为Json格式
var enumerableQuery = new EnumerableQuery<KeyValuePair<string, JToken>>(ja[0] as JObject);
return GetSerchExtensions<TEntity>(enumerableQuery); //把查询条件拼接为Extensions
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 把查询条件拼接为Extensions
/// </summary>
/// <typeparam name="TEntity">查询实体</typeparam>
/// <param name="enumerableQuery">查询条件</param>
/// <returns></returns>
public static Expression<Func<TEntity, bool>> GetSerchExtensions<TEntity>(EnumerableQuery<KeyValuePair<string, JToken>> enumerableQuery) where TEntity : class, new()
{
var paramExp = Expression.Parameter(typeof(TEntity), "a");
if (null == enumerableQuery || !enumerableQuery.Any())
{
var valueEqual = Expression.Constant(1);
var expEqual = Expression.Equal(valueEqual, valueEqual);
return Expression.Lambda<Func<TEntity, bool>>(expEqual, paramExp); //如果参数为空，返回一个a=>1=1 的值
}
var modeltypt = typeof(TEntity); //实体类型
var keyList = enumerableQuery.Select(e => e.Key).ToList(); //取出Json 的每个字符串
Expression whereExp = null;
keyList.ForEach(s =>
{
var searchTypeStr = s.Substring(1, s.LastIndexOf("][", StringComparison.Ordinal) - 1); //查询方式 Like
var ab = s.Substring(s.LastIndexOf("][", StringComparison.Ordinal) + 2);
var joinTypeStr = ab.Remove(ab.LastIndexOf("]", StringComparison.Ordinal)); //连接方式 or
var searchField = s.Substring(s.LastIndexOf("]", StringComparison.Ordinal) + 1); //查询的列名 name
var value = enumerableQuery.FirstOrDefault(v => v.Key == s).Value.ToString(); //值 123
LogicOperation searchType; //查询方式
PredicateType joinType; //连接方式
if (Enum.TryParse(searchTypeStr.ToUpper(), out searchType) && Enum.TryParse(joinTypeStr.ToUpper(), out joinType) && modeltypt.GetProperties().Any(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase))) //这个实体有这个列名
{
var firstOrDefault = modeltypt.GetProperties().FirstOrDefault(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase));
if (firstOrDefault == null) return;
var selCol = firstOrDefault.Name; //查询的列名
var splitList = value.Split(',').ToList(); //这个位置是的处理是默认认为当查询值中包含,的视为或者的查询：例如 A='abc,def' 处理成 (A='def' OR A='abc'),但是时间上这块无法满足就要查询包含,的数据的求
for (var i = 0; i < splitList.Count; i++)
{
var val = splitList[i];
if (val == null || string.IsNullOrWhiteSpace(val)) continue;
var expressionFuncEquals = PrepareConditionLambda<TEntity>(selCol, val, paramExp, searchType); //得到这个查询的表达式
whereExp = i != 0 ? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals)) : (joinType == PredicateType.OR ? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals))
: (whereExp == null ? expressionFuncEquals : Expression.And(whereExp, expressionFuncEquals)));
}
}
});
return Expression.Lambda<Func<TEntity, bool>>(whereExp, paramExp); ;
}
/// <summary>
/// 把查询条件拼接为Extensions
/// </summary>
/// <typeparam name="TEntity">实体类</typeparam>
/// <param name="queryEntitys">查询实体</param>
/// <returns></returns>
public static Expression<Func<TEntity, bool>> GetSerchExtensions<TEntity>(List<QueryEntity> queryEntitys) where TEntity : class, new()
{
var paramExp = Expression.Parameter(typeof(TEntity), "a");
if (null == queryEntitys || !queryEntitys.Any())
{
var valueEqual = Expression.Constant(1);
var expEqual = Expression.Equal(valueEqual, valueEqual);
return Expression.Lambda<Func<TEntity, bool>>(expEqual, paramExp); //如果参数为空，返回一个a=>1=1 的值
}
var modeltypt = typeof(TEntity); //实体类型
Expression whereExp = null;
queryEntitys.ForEach(q =>
{
LogicOperation searchType = q.LogicOperation; //查询方式
PredicateType joinType = q.PredicateType; //连接方式
var searchField = q.Column; //查询的列名 name
var value = q.Value; //值 123
if (modeltypt.GetProperties().Any(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase))) //这个实体有这个列名
{
var firstOrDefault = modeltypt.GetProperties().FirstOrDefault(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase));
if (firstOrDefault == null) return;
var selCol = firstOrDefault.Name; //查询的列名
var splitList = value.ToString().Split(',').ToList(); //这个位置是的处理是默认认为当查询值中包含,的视为或者的查询：例如 A='abc,def' 处理成 (A='def' OR A='abc'),但是时间上这块无法满足就要查询包含,的数据的求
for (var i = 0; i < splitList.Count; i++)
{
if (splitList[i] == null || string.IsNullOrWhiteSpace(splitList[i])) continue;
var expressionFuncEquals = PrepareConditionLambda<TEntity>(selCol, splitList[i], paramExp, searchType); //得到这个查询的表达式
whereExp = i != 0
? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals))
: (joinType == PredicateType.OR ? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals))
: (whereExp == null ? expressionFuncEquals : Expression.And(whereExp, expressionFuncEquals)));
}
}
});
return Expression.Lambda<Func<TEntity, bool>>(whereExp, paramExp); ;
}
/// <summary>
/// 得到字段查询的表达式
/// </summary>
/// <typeparam name="TEntity">实体</typeparam>
/// <param name="name">查询列名</param>
/// <param name="dateValue">数据值</param>
/// <param name="paramExp">参数</param>
/// <param name="searchType">查询方式(默认是等于查询)</param>
/// <returns></returns>
private static Expression PrepareConditionLambda<TEntity>(string name, object dateValue, ParameterExpression paramExp, LogicOperation searchType = LogicOperation.EQUAL)
{
if (dateValue == null) throw new ArgumentNullException("dateValue");
if (paramExp == null) throw new ArgumentNullException("paramExp");
var exp = Expression.Property(paramExp, name);
var propertyType = typeof(TEntity).GetProperty(name).PropertyType; //得到此字段的数据类型
object value; //propertyType == typeof(Guid?) ? new Guid(dateValue.ToString()) : Convert.ChangeType(dateValue, TypeHelper.GetUnNullableType(propertyType));
if (propertyType == typeof(Guid?) || propertyType == typeof(Guid))
{
value = new Guid(dateValue.ToString());
}
else
{
value = Convert.ChangeType(dateValue, TypeHelper.GetUnNullableType(propertyType));
}
Expression expEqual = null;
switch (searchType)
{
case LogicOperation.EQUAL: //等于查询
var valueEqual = Expression.Constant(value, propertyType); //值
expEqual = Expression.Equal(exp, valueEqual); //拼接成 t=>t.name=valueEqual
break;
case LogicOperation.LIKE: //模糊查询
var containsMethod = typeof(string).GetMethod("Contains");
var valueLike = Expression.Constant(value, propertyType);
expEqual = Expression.Call(exp, containsMethod, valueLike);
break;
}
return expEqual;
}
#endregion
}
类似Format写法：
直如弦，死道边；曲如钩，反封侯
