How can I add variable count of SqlMethods.Like() in one query?

Question

Can you help me with next: I'd like to add variable count of SqlMethods.Like() in one query?

For example: I've four words in list catCode, how can I create LINQ query with four SqlMethods.Like()?

using (var db = new MappingDataContext(_connection))
{
    db.ObjectTrackingEnabled = false;

    return (
        from r in db.U_CDW_REPORTs
        where (catCode.Length > 0 ? 
            SqlMethods.Like(r.CATEGORY, catCode) : r.CATEGORY != "*")
        where r.SI_QTY > 0
        orderby r.SI_QTY descending
        select new Item
        {
            ...
        }).ToList();
}

Answer 1

You mean you have 4 disjoint LIKE clauses?

from entityRow in ctx.MyEntity
where (SqlMethods.Like(entityRow.Col, "%Pattern1%")
    || SqlMethods.Like(entityRow.Col, "%Patte%rn2%") 
    || SqlMethods.Like(entityRow.Col, "Pattern3%") 
    || SqlMethods.Like(entityRow.Col, "%Patte%rn4%")) 
select entityRow;

UPDATE:

In that case, take a look at this: LINQ for LIKE queries of array elements

Answer 2

EDIT: made the code simpler. Also notice the updated explanation below.

You can build upon the query with multiple Where clauses in the following manner. Note that this approach ANDs the Where clauses, meaning all search terms will need to exist. In other words it's similar to ... where (condition1) && (condition2) && (conditionN).

string[] words = { "A", "B", "C" };
var query = dc.Products.AsQueryable(); // gives us an IQueryable<T> to build upon
foreach (var s in words)
{
    query = query.Where(p => SqlMethods.Like(p.ProductName, "%" + s + "%"));
}

foreach (var item in query)
{
    Console.WriteLine(item.ProductName);
}

The idea is to set the first part of the query up, then loop over the search terms and update the query. Of course you should update your pattern as needed; I used %word% for illustration purposes only.

Answer 3

What you need is dynamically OR-ing SqlMethod.Like operations. You need the PredicateBuilder.

---

Update: Here is an example of how to use the predicate builder.

string[] searchItems =
    new string[] { "Pattern1", "Pattern2", "Pattern3" };

var likeExpression = PredicateBuilder.False<U_CDW_REPORT>();

foreach (string searchItem in searchItems)
{
    var searchPattern = "%" + searchItem + "%";
    likeExpression = likeExpression.Or(r => 
        SqlMethods.Like(r.CATEGORY, searchPattern));
}

return (
    from r in db.U_CDW_REPORTs.Where(likeExpression)
    where r.SI_QTY > 0
    orderby r.SI_QTY descending
    select new Item { ... }).ToList();