有没有办法比较SQL表达式中的字符串,类似于C#的LIKE
运算符?
假设我有一个字符串列表。在这个列表中,我想搜索一个字符串。在SQL中,我可以这样写:
SELECT * FROM DischargePort WHERE PortName LIKE '%BALTIMORE%'
与以上不同,查询需要一种linq语法。
using System.Text.RegularExpressions;
…
var regex = new Regex(sDischargePort, RegexOptions.IgnoreCase);
var sPortCode = Database.DischargePorts
.Where(p => regex.IsMatch(p.PortName))
.Single().PortCode;
我上面的LINQ语法不起作用。我哪里搞错了?
发布于 2016-02-26 03:07:51
好吧..。有时使用Contains
,StartsWith
或EndsWith
可能会很不舒服,特别是当搜索值确定LIKE
语句时,例如,传递的' value %‘要求开发人员在表达式中使用StartsWith
函数。所以我决定为IQueryable
对象编写扩展。
用法
// numbers: 11-000-00, 00-111-00, 00-000-11
var data1 = parts.Like(p => p.Number, "%11%");
// result: 11-000-00, 00-111-00, 00-000-11
var data2 = parts.Like(p => p.Number, "11%");
// result: 11-000-00
var data3 = parts.Like(p => p.Number, "%11");
// result: 00-000-11
代码
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;
}
}
发布于 2016-04-06 23:10:31
在原生LINQ中,您可以组合使用Contains/StartsWith/EndsWith
或RegExp。
在LINQ2SQL中使用方法SqlMethods.Like()
from i in db.myTable
where SqlMethods.Like(i.field, "tra%ata")
select i
添加Assembly: System.Data.Linq (在System.Data.Linq.dll中)以使用此功能。
发布于 2011-03-21 14:34:16
正如Jon Skeet和Marc Gravell已经提到的,您可以简单地接受一个包含条件。但是在您的like查询中,使用单个()语句是非常危险的,因为这意味着您只能找到1个结果。如果有更多的结果,您将收到一个很好的异常:)
所以我更喜欢使用FirstOrDefault()而不是Single():
var first = Database.DischargePorts.FirstOrDefault(p => p.PortName.Contains("BALTIMORE"));
var portcode = first != null ? first.PortCode : string.Empty;
https://stackoverflow.com/questions/5374481
复制相似问题