c# – LINQ使用“like”而不是“((NVL(INSTR(x,y),0))= 1)”

概述当使用.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,其中有一些很 当使用.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%'

总结

以上是内存溢出为你收集整理的c# – LINQ使用“like”而不是“((NVL(INSTR(x,y),0))= 1)”全部内容，希望文章能够帮你解决c# – LINQ使用“like”而不是“((NVL(INSTR(x,y),0))= 1)”所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。