CODEWITHSUNDEEP
c#entity-framework-core
Sam
Sam

Reputation: 29009

Combine .Contains and .Like

I've got a list of search patterns (LIKE) and want to find all items in a table matching to at least one of them. My list would be something like this:

string[] searchList = {"mailto:Sam@%", "mailto:info@%", "phone:65331%"};

In SQL I would do this:

var sqlCommand = "SELECT [Id], [Area]  WHERE [TenantId] = @TenantId AND (";
for (var i = 0; i < searchList.Length; i++)
{
  if (i > 0)
    sqlCommand += " OR ";
  sqlCommand += $"[Area] LIKE search{i}";
  sqlCmd.Parameters.AddWithValue($"search{i}", searchList[i]);
}
sqlCommand += ")";
sqlCmd.CommandText = sqlCommand;

Is there any way to achieve this using Linq2Sql? If it would always be three items in the array it would be simple:

var result = await context
  .MyTable
  .Where(mt => mt.TenantId == tenantId 
    && (EF.Functions.Like(mt.Area, searchList[0])
     || EF.Functions.Like(mt.Area, searchList[1])
     || EF.Functions.Like(mt.Area, searchList[2])))

But of course it might be two items in the searchList. Or four. Or any other number.

I would need some kind of .Contains like this, only with LIKE comparison:

var result = await context
  .MyTable
  .Where(mt => mt.TenantId == tenantId 
    && searchList.Contains(mt.Area)) // does not work due to wildcards

Any way to combine a .Like and .Contains for these cases?

Or use some kind of loop in the .Where-clause?

UPDATE

To use

var result = await context
.MyTable
.Where(mt => mt.TenantId == tenantId &&
     searchList.Any(listItem => EF.Functions.Like(mt.Area, listItem)));

or

var result = await context
.MyTable
.Where(mt => mt.TenantId == tenantId &&
     searchList.Any(listItem => mt.Area.StartsWith(listItem)));

is a brilliant idea, but the Linq2Sql-Interpreter ist not able to translate these to SQL queries:

System.InvalidOperationException: "The LINQ expression 'DbSet<MyTable> .Where(t => t.TenantId == tenantId && __searchList_1 .Any(sl => sl == "" || t.LastName != null && sl != null && t.LastName.StartsWith(sl)))' could not be translated.

UPDATE 2

I just remembered I could build my SQL query string and use context.ExecuteQuery<>(sqlCommand) which would solve my problem.

Upvotes: 1

Views: 414

Answers (4)

TraderMoe
TraderMoe

Reputation: 84

you can

using System.Linq;

var result = await context.MyTable
                          .Where(entry => mt.TenantId == tenantId && 
                                 searchList.Any(listItem => mt.Area.StartsWith(listItem, StringComparison.InvariantCultureIgnoreCase)));

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205759

And (&&) conditions are easily emulated by simply chaining multiple Where calls.

However LINQ has no integrated support for combining Or (||) conditions, so you have to use some expression predicate builder library, or performing the desired combination using Expression class methods directly.

For top level queries it could be encapsulated in a custom extension method like this:

public static partial class QueryableExtensions
{
    public static IQueryable<T> WhereAny<T, V>(this IQueryable<T> source, IEnumerable<V> values, Expression<Func<T, V, bool>> match)
    {
        var parameter = match.Parameters[0];
        var body = values
            .Select(value => Expression.Constant(value, typeof(V)))
            .Select(value => Expression.Invoke(match, parameter, value))
            .Aggregate<Expression>(Expression.OrElse);
        var predicate = Expression.Lambda<Func<T, bool>>(body, parameter);
        return source.Where(predicate);
    }
}

and the usage in your case would be:

var result = await context.MyTable
    .Where(mt => mt.TenantId == tenantId) 
    .WhereAny(searchList, (mt, s) => EF.Functions.Like(mt.Area, s))
    .ToListAsync();

Upvotes: 3

Cetin Basoz
Cetin Basoz

Reputation: 23827

Yes. It looks like the way you do it with SQL. result is IQueryable and thus you could chain multiple Where() before sending the query back to server. ie:

string[] searchList = {"mailto:Sam@", "mailto:info@", "phone:65331"};

var result = db.MyData.Where( x => x.TenantID = tenantID);

for (var i = 0; i < searchList.Length; i++)
{
  var tmp = searchList[i];
  result = result.Where(x => x.Area.Contains( tmp );
}

// enumerate result here. 
// For example, result.ToList()

PS: I assumed you wanted to join them with AND as in your SQL sample.

As @juharr claims you want to connect them with OR. I am not sure. He might be right. Then here you go:

string[] searchList = {"mailto:Sam@", "mailto:info@", "phone:65331"};

var result = db.MyData.Where( x => x.TenantID = tenantID)
             .AsEnumerable()
             .Where(x => searchList.Any(se => x.Area.StartsWith(se)));

UPDATE: Here is the alternative for server side with OR for completeness:

string[] searchList = {"mailto:Sam@%", "mailto:info@%", "phone:65331%"};

var sb = new StringBuilder();
sb.Append($@"select * 
    from MyTable
    where TennantId = {{{searchList.Length}}} and (");

for (int i = 0; i < searchList.Length; i++)
{
    if (i > 0) sb.Append(" or ");
    sb.Append($" Area like {{{i}}}");
}
sb.Append(")");
var query = sb.ToString();

// var tennantId = whatever
Array.Resize(ref searchList, searchList.Length+1);
searchList[searchList.Length-1] = tennantId.ToString();

var result = ctx.ExecuteStoreQuery<Employee>(query, searchList);

Upvotes: 0

AnGG
AnGG

Reputation: 831

Yes, String Contains is supported and also iterate over array with Any

var result = await context.MyTable
  .Where(mt => mt.TenantId == tenantId && searchList.Any(a => a.Contains(mt.Area))

Beware not all the overloads of Contains are supported in LINQ to SQL,

Next time please tell us what .NET and EF version do you use, There are diffrences between .Net Core, EF Core and EF 6.

Upvotes: 0

Related Questions