技术背景：框架MVC，linq to Entity 需要一定的lambda书写能力
问题：在简单的orm中完成一些简单的增删查改是通过where insert delete update 完成的，但是在这个过程中出现了一个需求：多项条件的and 和or 的组合查询
众所周知直接通过linq 写的话很方便，但是我们的框架使用了linq to entity,如果只用lambda来写比较复杂的and 和or 查询就比较麻烦了。
一：简单and 和or 查询
public void TextFoZK() { using (var dbContext = new CRMDbContext()) { //第一句解析出来的sql是 select * from membertype where commercialtenantid=0 or name='住客' dbContext.MemberType.Where(m => m.CommercialTenantID == 0 || m.Name == "住客"); //第二句解析出来的sql是 select * from membertype where commercialtenantid=0 and name='住客' dbContext.MemberType.Where(m => m.CommercialTenantID == 0 && m.Name == "住客"); } }
二：复杂逻辑的and 和or 查询
public void TextFoZK(int status, string name, int commercialtenantid) { using (var dbContext = new CRMDbContext()) { IQueryable<MemberType> iqm = dbContext.MemberType; if (status > 0) { iqm = iqm.Where(m => m.Status == status); } if (!string.IsNullOrEmpty(name)) { iqm = iqm.Where(m => m.Name == name && m.CommercialTenantID == commercialtenantid); } iqm = iqm.Where(m => m.ID > 0 || m.ID == 1); iqm.ToList(); //select * from membertype where (status=1) and (name='住客' and commercialtenantid=1) and (id>0 or id=1) } }
这里使用了IQuerable的扩展方法where ,代表着每个iquerable之间为and 关系，但是又可以包含or
三：复杂and 和 or
public void TextFoZK(int status, string name, int commercialtenantid) { using (var dbContext = new CRMDbContext()) { IQueryable<MemberType> iqm = dbContext.MemberType; if (status > 0) { iqm = iqm.Where(m => m.Status == status); } if (!string.IsNullOrEmpty(name)) { iqm = iqm.Where(m => m.Name == name && m.CommercialTenantID == commercialtenantid); } //重新声明一个iq，两个iq 之间为or 关系 IQueryable<MemberType> iqmtwo = dbContext.MemberType; iqmtwo = iqmtwo.Where(m => m.ID > 0 || m.ID == 1); iqm = iqm.Union(iqmtwo); iqm.ToList(); } }
这里使用了iquerable中的扩展方法union 可以把多个iq方法合成为一个iq ,之间为union all 关系
第一个iq 为一个结果集，第二个为一个结果集，最后合并两个结果集。
可以满足一个sql过程中查询多处结果的要求，但是生成的sql还是有点麻烦
exec sp_executesql N'SELECT TOP (10) [Project4].[C1] AS [C1], [Project4].[C2] AS [C2], [Project4].[C3] AS [C3], [Project4].[C4] AS [C4], [Project4].[C5] AS [C5], [Project4].[C6] AS [C6], [Project4].[C7] AS [C7], [Project4].[C8] AS [C8], [Project4].[C9] AS [C9], [Project4].[C10] AS [C10] FROM ( SELECT [Project4].[C1] AS [C1], [Project4].[C2] AS [C2], [Project4].[C3] AS [C3], [Project4].[C4] AS [C4], [Project4].[C5] AS [C5], [Project4].[C6] AS [C6], [Project4].[C7] AS [C7], [Project4].[C8] AS [C8], [Project4].[C9] AS [C9], [Project4].[C10] AS [C10], row_number() OVER (ORDER BY [Project4].[C1] ASC) AS [row_number] FROM ( SELECT [Distinct1].[C1] AS [C1], [Distinct1].[C2] AS [C2], [Distinct1].[C3] AS [C3], [Distinct1].[C4] AS [C4], [Distinct1].[C5] AS [C5], [Distinct1].[C6] AS [C6], [Distinct1].[C7] AS [C7], [Distinct1].[C8] AS [C8], [Distinct1].[C9] AS [C9], [Distinct1].[C10] AS [C10] FROM ( SELECT DISTINCT [UnionAll1].[ID] AS [C1], [UnionAll1].[CommercialTenantID] AS [C2], [UnionAll1].[Name] AS [C3], [UnionAll1].[Status] AS [C4], [UnionAll1].[Discount] AS [C5], [UnionAll1].[GiveIntegralScale] AS [C6], [UnionAll1].[Creator] AS [C7], [UnionAll1].[CreatorID] AS [C8], [UnionAll1].[GMT_Create] AS [C9], [UnionAll1].[GMT_Modified] AS [C10] FROM (SELECT [Extent1].[ID] AS [ID], [Extent1].[CommercialTenantID] AS [CommercialTenantID], [Extent1].[Name] AS [Name], [Extent1].[Status] AS [Status], [Extent1].[Discount] AS [Discount], [Extent1].[GiveIntegralScale] AS [GiveIntegralScale], [Extent1].[Creator] AS [Creator], [Extent1].[CreatorID] AS [CreatorID], [Extent1].[GMT_Create] AS [GMT_Create], [Extent1].[GMT_Modified] AS [GMT_Modified] FROM [dbo].[commercialtenant_membertype] AS [Extent1] WHERE [Extent1].[CommercialTenantID] = @p__linq__0 UNION ALL SELECT [Extent2].[ID] AS [ID], [Extent2].[CommercialTenantID] AS [CommercialTenantID], [Extent2].[Name] AS [Name], [Extent2].[Status] AS [Status], [Extent2].[Discount] AS [Discount], [Extent2].[GiveIntegralScale] AS [GiveIntegralScale], [Extent2].[Creator] AS [Creator], [Extent2].[CreatorID] AS [CreatorID], [Extent2].[GMT_Create] AS [GMT_Create], [Extent2].[GMT_Modified] AS [GMT_Modified] FROM [dbo].[commercialtenant_membertype] AS [Extent2] WHERE (0 = [Extent2].[CommercialTenantID]) AND (N''住客'' = [Extent2].[Name])) AS [UnionAll1] ) AS [Distinct1] ) AS [Project4] ) AS [Project4] WHERE [Project4].[row_number] > 0 ORDER BY [Project4].[C1] ASC',N'@p__linq__0 int',@p__linq__0=1
最后提供一种扩展方法
四：多条件之间均为or
/// <summary> /// 传入条件之间为OR查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="source"></param> /// <param name="predicates"></param> /// <returns></returns> public static IQueryable<T> WhereOR<T>(this IQueryable<T> source, params Expression<Func<T, bool>>[] predicates) { if (source == null) throw new ArgumentNullException("source"); if (predicates == null) throw new ArgumentNullException("predicates"); if (predicates.Length == 0) return source.Where(x => false); // no matches! if (predicates.Length == 1) return source.Where(predicates[0]); // simple var param = Expression.Parameter(typeof(T), "x"); Expression body = Expression.Invoke(predicates[0], param); for (int i = 1; i < predicates.Length; i++) { body = Expression.OrElse(body, Expression.Invoke(predicates[i], param)); } var lambda = Expression.Lambda<Func<T, bool>>(body, param); return source.Where(lambda); }
public void TextFoZK(int status, string name, int commercialtenantid) { using (var dbContext = new CRMDbContext()) { IQueryable<MemberType> iqm = dbContext.MemberType; if (status > 0) { iqm = iqm.Where(m => m.Status == status); } if (!string.IsNullOrEmpty(name)) { iqm = iqm.Where(m => m.Name == name && m.CommercialTenantID == commercialtenantid); } var predicates = new List<Expression<Func<MemberType, bool>>>(); predicates.Add(m => m.CommercialTenantID == 0 && m.Name == "住客"); predicates.Add(m=>m.ID>0); //这两个条件之间为or //与iqm之间为and //如果要与iqm之间为or 也可以使用union方法，但是总感觉有点麻烦 iqm = iqm.WhereOR(predicates.ToArray()); iqm.ToList(); //select * from membertype where (status=1) and (name='住客' and commercialtenantid=1) or (id>0 or id=1) } }
我感觉已经研究到这一步了索性就再往深的看一看，于是我找到了IQuerable的where 和union 的底层方法
public static IQueryable<TSource> Union<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2) { if (source1 == null) { throw System.Linq.Error.ArgumentNull("source1"); } if (source2 == null) { throw System.Linq.Error.ArgumentNull("source2"); } return source1.Provider.CreateQuery<TSource>(Expression.Call(null, ((MethodInfo) MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] { typeof(TSource) }), new Expression[] { source1.Expression, GetSourceExpression<TSource>(source2) })); } [__DynamicallyInvokable] public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate) { if (source == null) { throw System.Linq.Error.ArgumentNull("source"); } if (predicate == null) { throw System.Linq.Error.ArgumentNull("predicate"); } return source.Provider.CreateQuery<TSource>(Expression.Call(null, ((MethodInfo) MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] { typeof(TSource) }), new Expression[] { source.Expression, Expression.Quote(predicate) })); }
只是浅薄只能看到这一步。
最后我还求助大神，大神又提出一个方案使用的是expresion方法，这个其实就是我上面提供的whereor 方法内的同样技术，只不过我是封装了的。
Expression<Func<MemberType, bool>> funtyps = c => c.ID > 0; Expression<Func<MemberType, bool>> ortype = c => c.CommercialTenantID == 0 && c.Name == "住客"; funtyps = funtyps.Or(ortype); iqmemebertype = iqmemebertype.Where(funtyps);
应该是是要更好的方案，我只是记录我目前理解的方法。
最后附上关于expression的底层方法or 和 and ,提供了express语句之间可或与查询的接口
/// <summary> /// 用于多条件动态查询 /// zk(-_-) /// </summary> public static class PredicateBuilderUtility { 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); } /// <summary> /// 动态And /// </summary> public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.AndAlso); } /// <summary> /// 动态Or /// </summary> 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); } /// <summary> /// 传入条件之间为OR查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="source"></param> /// <param name="predicates"></param> /// <returns></returns> public static IQueryable<T> WhereOR<T>(this IQueryable<T> source, params Expression<Func<T, bool>>[] predicates) { if (source == null) throw new ArgumentNullException("source"); if (predicates == null) throw new ArgumentNullException("predicates"); if (predicates.Length == 0) return source.Where(x => false); // no matches! if (predicates.Length == 1) return source.Where(predicates[0]); // simple var param = Expression.Parameter(typeof(T), "x"); Expression body = Expression.Invoke(predicates[0], param); for (int i = 1; i < predicates.Length; i++) { body = Expression.OrElse(body, Expression.Invoke(predicates[i], param)); } var lambda = Expression.Lambda<Func<T, bool>>(body, param); return source.Where(lambda); } }