Multiple like in lambda expression

Question

I have a string[] variable which i want to use in the lambda expression to filter data using like clause. I tried using contains, but it works like the 'in' clause, which is not what I need.

Please see the code below which behaves like in clause:

var inventories = ..............AsQuerable();
string[] strArray = <<somevalues>>;
inventories = inventories.Where(c => !strArray.Contains(c.columnName));

Could someone provide me the lambda expression which could filter records using like instead of in using an array.

It's not clear which string you want to match with `LIKE`. The way you use `Contains` isn't anywhere near `LIKE`. It's an exact match. — Gert Arnold, Aug 24 '18 at 20:04
Are you trying the like condition for every value in strArray? e.g. c.columnName = 'lorem ipsum'. strArray = {'a', 'l', 'p'}. Now, you want to match c.columnName with each value of strArray. And if it matches with anyone of them, then pick that value. Is my understanding right? — ManishM, Aug 26 '18 at 14:52

score 1 · Answer 1 · answered Aug 24 '18 at 14:25

1

The only methods LINQ provide for this purpose is .Where(), .StartsWith() or .EndsWith().. Also, there is pretty similar question here How to do SQL Like % in Linq?

answered Aug 24 '18 at 14:25

OlegI

5,472
4
23
31

Mike Derksen · Answer 2 · 2018-08-24T14:36:25.653

I'm not entirely sure what you are trying to do, but I think this is what you are trying to accomplish. If not, please provide some more information and I will update my answer.

To check if a string is equal to a part of another string (which like does), you can use the .Contains method:

bool contains = "Some fancy sentence".Contains("fancy");

This will evaluate to true. Given your example, this would result in the following:

var inventories = ..............AsQuerable();
string[] strArray = <<somevalues>>;
inventories = inventories.Where(inv => !strArray.Any(s => inv.columnName.Contains(s)));

This checks all inventories and removes all inventories where the inventory column name (partially) occurs in any of the strArray values.

score 0 · Accepted Answer · answered Aug 24 '18 at 14:29

Unlike VB.NET, C# has no builtin like-operator. However, you can use the operator from VB.NET easily. What you have to do is to reference the assembly Microsoft.VisualBasic.dll and include a using Microsoft.VisualBasic.CompilerServices; at the top of your file. Then you can do

 var inventories = ..............AsQuerable();
 string[] strArray = <<somevalues>>;
 inventories = inventories.Where(c => !strArray.Any(s => LikeOperator.LikeString(c.columnName, s, CompareMethod.Text)));

score 0 · Answer 4 · answered Aug 24 '18 at 19:59

It is not pure Lambda expression, but Maybe it can help:

List<SomeObject> inventories = new List<SomeObject>();
// add objects to list...
string[] strArray = <<somevalues>>;
// result will be stored here
List<SomeObject> filtered = new List<SomeObject>();

foreach (var itm in strArray)
{
    // LIKE '%SOMEVALUE%'
    var match = inventories.Where(x => x.columnName.Contains(itm)).ToList();
    // LIKE '%SOMEVALUE'
    // var match = inventories.Where(x => x.columnName.StartsWith(itm)).ToList();
    // LIKE 'SOMEVALUE%'
    // var match = inventories.Where(x => x.columnName.EndsWith(itm)).ToList();
    foreach (var m in match)
        filtered.Add(m);
}

Multiple like in lambda expression

4 Answers4