小开

Typically you use String.StartsWith/EndsWith/Contains. For example:

var portCode = Database.DischargePorts
.Where(p => p.PortName.Contains("BALTIMORE"))
.Single()
.PortCode;

I don't know if there's a way of doing proper regular expressions via LINQ to SQL though. (Note that it really does depend on which provider you're using - it would be fine in LINQ to Objects; it's a matter of whether the provider can convert the call into its native query format, e.g. SQL.)

EDIT: As BitKFu says, Single should be used when you expect exactly one result - when it's an error for that not to be the case. Options of SingleOrDefault, FirstOrDefault or First should be used depending on exactly what's expected.

小开

Regex? no. But for that query you can just use:

 string filter = "BALTIMORE";
(blah) .Where(row => row.PortName.Contains(filter)) (blah)

If you really want SQL LIKE, you can use System.Data.Linq.SqlClient.SqlMethods.Like(...), which LINQ-to-SQL maps to LIKE in SQL Server.

小开

You can call the single method with a predicate:

var portCode = Database.DischargePorts
.Single(p => p.PortName.Contains("BALTIMORE"))
.PortCode;

小开

As Jon Skeet and Marc Gravell already mentioned, you can simple take a contains condition. But in case of your like query, it's very dangerous to take a Single() statement, because that implies that you only find 1 result. In case of more results, you'll receive a nice exception :)

So I would prefer using FirstOrDefault() instead of Single():

var first = Database.DischargePorts.FirstOrDefault(p => p.PortName.Contains("BALTIMORE"));
var portcode = first != null ? first.PortCode : string.Empty;

小开

  .Where(e => e.Value.StartsWith("BALTIMORE"))

This works like "LIKE" of SQL...

小开

A simple as this

string[] users = new string[] {"Paul","Steve","Annick","Yannick"};
var result = from u in users where u.Contains("nn") select u;

Result -> Annick,Yannick

小开

Well... sometimes it may be uncomfortable to use Contains, StartsWith or EndsWith especially when searching value determine LIKE statment e.g. passed 'value%' require from developer to use StartsWith function in expression. So I decided to write extension for IQueryable objects.

Usage

// 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

Code

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;
}
}

小开

   public static class StringEx
{
public static bool Contains(this String str, string[] Arr, StringComparison comp)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.IndexOf(s, comp)>=0)
{ return true; }
}
}


return false;
}


public static bool Contains(this String str,string[] Arr)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.Contains(s))
{ return true; }
}
}


return false;
}
}




var portCode = Database.DischargePorts
.Single(p => p.PortName.Contains( new string[] {"BALTIMORE"},  StringComparison.CurrentCultureIgnoreCase) ))
.PortCode;

小开

Just add to string object extention methods.

public static class StringEx
{
public static bool Contains(this String str, string[] Arr, StringComparison comp)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.IndexOf(s, comp)>=0)
{ return true; }
}
}


return false;
}


public static bool Contains(this String str,string[] Arr)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.Contains(s))
{ return true; }
}
}


return false;
}
}

usage:

use namespase that contains this class;


var sPortCode = Database.DischargePorts
.Where(p => p.PortName.Contains(new string [] {"BALTIMORE"},  StringComparison.CurrentCultureIgnoreCase) )
.Single().PortCode;

小开

In native LINQ you may use combination of Contains/StartsWith/EndsWith or RegExp.

In LINQ2SQL use method SqlMethods.Like()

    from i in db.myTable
where SqlMethods.Like(i.field, "tra%ata")
select i

add Assembly: System.Data.Linq (in System.Data.Linq.dll) to use this feature.

小开

Ideally you should use StartWith or EndWith.

Here is an example:

DataContext  dc = new DCGeneral();
List<Person> lstPerson= dc.GetTable<Person>().StartWith(c=> c.strNombre).ToList();


return lstPerson;

小开

List<Categories> categoriess;
private void Buscar()
{
try
{
categoriess = Contexto.Categories.ToList();
categoriess = categoriess.Where(n => n.CategoryID >= Convert.ToInt32(txtCatID.Text) && n.CategoryID <= Convert.ToInt32(txtCatID1.Text) && (n.CategoryName.Contains(txtCatName.Text)) ).ToList();

小开

@adobrzyc had this great custom LIKE function - I just wanted to share the IEnumerable version of it.

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) });


private static 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).Compile();
}


public static IEnumerable<TSource> Like<TSource, TMember>(this IEnumerable<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 Extension Linq / SQL

LikeExtension Class

Tested in .NET 5

 public static class LikeExtension {


private static string ColumnDataBase<TEntity, TKey>(IModel model, Expression<Func<TEntity, TKey>> predicate) where TEntity : class {


ITable table = model
.GetRelationalModel()
.Tables
.First(f => f
.EntityTypeMappings
.First()
.EntityType == model
.FindEntityType(predicate
.Parameters
.First()
.Type
));


string column = (predicate.Body as MemberExpression).Member.Name;
string columnDataBase = table.Columns.First(f => f.PropertyMappings.Count(f2 => f2.Property.Name == column) > 0).Name;


return columnDataBase;


}


public static IQueryable<TEntity> Like<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {


string columnDataBase = ColumnDataBase(context.Model, predicate);
return context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);


}


public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {


string columnDataBase = ColumnDataBase(context.Model, predicate);
return await context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);


}


public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {


DbSet<TEntity> entities = query as DbSet<TEntity>;
string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
return await entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);


}


public static IQueryable<TEntity> Like<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {


DbSet<TEntity> entities = query as DbSet<TEntity>;
string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
return entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);


}


}

Repository

    public async Task<IEnumerable<TEntity>> LikeAsync<TKey>(Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) {


return await context.LikeAsync(predicate, text, cancellationToken);


}


public IQueryable<TEntity> Like<TKey>(Expression<Func<TEntity, TKey>> predicate, string text) {


return context.Like(predicate, text);


}

Use

 IQueryable<CountryEntity> result = countryRepository
.Like(k => k.Name, "%Bra[sz]il%") /*Use Sync*/
.Where(w => w.DateRegister < DateTime.Now) /*Example*/
.Take(10); /*Example*/

Or

 IEnumerable<CountryEntity> result = await countryRepository
.LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/

Or

 IQueryable<CountryEntity> result = context.Countries
.Like(k => k.Name, "%Bra[sz]il%")
.Where(w => w.Name != null); /*Example*/

Or

 List<CountryEntity> result2 = await context.Countries
.Like(k => k.Name, "%Bra[sz]il%")
.Where(w => w.Name != null) /*Example*/
.ToListAsync(); /*Use Async*/

Or

 IEnumerable<CountryEntity> result3 = await context.Countries
.Where(w => w.Name != null)
.LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/

小开

You can also use the EF function tested in .net5

public async Task<IEnumerable<District>> SearchDistrict(string query, int stateId)
{
return await _dbContext
.Districts
.Include(s => s.State)
.Where(s => s.StateId == stateId && EF.Functions.Like(s.Name, "$%{query}%"))
.AsNoTracking()
.ToListAsync();
}

小开

Great custom LIKE function by @adobrzycdon't work for me in LINQPad.

Here version which work in LINQPad (tested on LINQPad 5 and LINQPad 6)

Code

void Main()
{
var users = from au in ApplicationUsers
select au;
    

users.Like(u => u.UserName, "Ada Byron").Dump();
users.Like(u => u.UserName, "%yro%").Dump();
users.Like(u => u.UserName, "% Byron").Dump();
users.Like(u => u.UserName, "Ada %").Dump();


users.Like(u => u.UserName, "%yro%").Like(u => u.UserName, "Ada %").Dump();
// => SQL =>
// DECLARE @p0 NVarChar(1000) = 'Ada %'
// DECLARE @p1 NVarChar(1000) = '%yro%'
// SELECT [t0].[UserName], ...
// FROM [ApplicationUsers] AS [t0]
// WHERE ([t0].[UserName] LIKE @p0) AND ([t0].[UserName] LIKE @p1)
}


// based on LinqEx by adobrzyc (https://stackoverflow.com/a/35636138/1351740)
public static class LinqExFork
{
private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
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 memberInfo = GetMemberInfo(property);
        

MemberExpression member;
if (memberInfo is PropertyInfo)
member = Expression.Property(param, memberInfo.Name);
else if (memberInfo is FieldInfo)
member = Expression.Field(param, memberInfo.Name);
else
throw new InvalidOperationException("Unable to determine propery or field info from expression.");


var startWith = value.StartsWith("%");
if (startWith)
value = value.Remove(0, 1);
            

var endsWith = value.EndsWith("%");
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 MemberInfo GetMemberInfo(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 member info from expression.");


var output = memberExpr.Member as MemberInfo;
if (output == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine member info from expression.");


return output;
}
}

LINQ 中的 LIKE 运算符

Like Extension Linq / SQL

LikeExtension Class

Repository

Use