c# – LINQ使用“like”而不是“((NVL(INSTR(x,y),0))= 1)”
发布时间:2020-12-15 22:51:48 所属栏目:百科 来源:网络整理
导读:当使用.Contains()/.StartsWith()/.EndsWith()时,生成的SQL如下所示: ((NVL(INSTR(x,y),0))= 1) 有没有办法使用它: LIKE 'x%' or '%x%' or '%x' 因为在查询的执行计划中这两者之间存在巨大的成本差异(44 000 vs 30). 解决方法 当我环顾abit时,我发现了 LIK
当使用.Contains()/.StartsWith()/.EndsWith()时,生成的SQL如下所示:
((NVL(INSTR(x,y),0))= 1) 有没有办法使用它: LIKE 'x%' or '%x%' or '%x' 因为在查询的执行计划中这两者之间存在巨大的成本差异(44 000 vs 30). 解决方法
当我环顾abit时,我发现了
LIKE operator in LINQ,其中有一些很好的例子说明你可以做到这一点.我测试了下面的链接,来自上面的链接
这是adobrzyc发布的使用Like with lambda的扩展 public static class LinqEx { private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains"); private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith",new[] { typeof(string) }); private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith",new[] { typeof(string) }); public static Expression<Func<TSource,bool>> LikeExpression<TSource,TMember>(Expression<Func<TSource,TMember>> property,string value) { var param = Expression.Parameter(typeof(TSource),"t"); var propertyInfo = GetPropertyInfo(property); var member = Expression.Property(param,propertyInfo.Name); var startWith = value.StartsWith("%"); var endsWith = value.EndsWith("%"); if (startWith) value = value.Remove(0,1); if (endsWith) value = value.Remove(value.Length - 1,1); var constant = Expression.Constant(value); Expression exp; if (endsWith && startWith) { exp = Expression.Call(member,ContainsMethod,constant); } else if (startWith) { exp = Expression.Call(member,EndsWithMethod,constant); } else if (endsWith) { exp = Expression.Call(member,StartsWithMethod,constant); } else { exp = Expression.Equal(member,constant); } return Expression.Lambda<Func<TSource,bool>>(exp,param); } public static IQueryable<TSource> Like<TSource,TMember>(this IQueryable<TSource> source,Expression<Func<TSource,TMember>> parameter,string value) { return source.Where(LikeExpression(parameter,value)); } private static PropertyInfo GetPropertyInfo(Expression expression) { var lambda = expression as LambdaExpression; if (lambda == null) throw new ArgumentNullException("expression"); MemberExpression memberExpr = null; switch (lambda.Body.NodeType) { case ExpressionType.Convert: memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression; break; case ExpressionType.MemberAccess: memberExpr = lambda.Body as MemberExpression; break; } if (memberExpr == null) throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression."); var output = memberExpr.Member as PropertyInfo; if (output == null) throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression."); return output; } } 要使用它,您只需添加Like函数即可放置Contains函数.您可以在下面看到一个示例 using (CustomerEntities customerContext = new CustomerEntities()) { IQueryable<Customer> customer = customerContext.Customer.Like(x => x.psn,"%1%"); } 这将创建一个类似于此的SQL查询. SELECT [Extent1].[psn] AS [psn] FROM [dbo].[Customer] AS [Extent1] WHERE [Extent1].[psn] LIKE '%1%' (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |