How can I dynamically build a linq OR query where one of several columns can match the search string?

Question

How can I dynamically build a linq OR query where one of several columns can match the search string? This is not that hard to do in SQL. For example:

SELECT     ID, Title, Description, Comments, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
FROM         Category
WHERE     (Title LIKE '%Supplier%') 
    OR (Description LIKE  '%Supplier%')

Sometimes I will need to add OR (Comments LIKE '%Supplier%') or omit OR (Title LIKE '%Supplier%') and that is why I want to build it dynamically.

I do not believe this is a duplicate question because the potential duplicate is not asked in a way that it can be found using the Stackoverflow search function.

score 5 · Answer 1 · answered Dec 13 '13 at 21:44

5

You just use the or operator like you would elsewhere in C#. The code snippet below should basically do what you want. The string literals could instead be local variables.

 MyObjects.Where(x => x.Someprop == "my string" || x.SomeOtherprop == "my string").Select(...)

For your example you would actually want something more like

 Where(x => x.Comments.Contains("Supplier") || x.Description.Contains("Supplier"))

Note that those SQL wild cards equivalent C# methods will be; Contains, StartsWith, and EndsWith. You could also use RegEx to match more sophisticated patterns.

answered Dec 13 '13 at 21:44

evanmcdonnal

46,131
16
104
115

We are definitely on the right track. However, sometimes I will need to add "OR (Comments LIKE '%Supplier%')" or remove "OR (Title LIKE '%Supplier%')" and that is why I want to build it dynamically. I won't know which columns to query until runtime. How do I handle those cases? – Tarzan Dec 13 '13 at 22:01
@Tarzan you have some different options there but I would recommend just writing different versions of the query and determining which one gets run with a typical if-elseif structure. The other option that comes to mind is to use reflection but that is more work than it's worth imo. – evanmcdonnal Dec 13 '13 at 22:09
I ended up solving it with a combination of ideas from you and from dasblinkenlight. – Tarzan Dec 14 '13 at 00:54

score 2 · Answer 2 · answered Dec 13 '13 at 21:54

Building a query with OR dynamically is not as straightforward as it is to build a query with an AND. If you are set on building the query dynamically, you can use the Predicate Builder. If you do not mind having a single query that can ignore parts of its condition dynamically, you can use the query below:

bool checkTitle = ...
bool checkDescription = ...
string likeString = ...
var res = context.Category
    .Where(item =>
        (checkTitle && item.Title.Contains(likeString))
        ||
        (checkDescription && item.Description.Contains(likeString))
    );

Since str.Contains("xyz") translates to str LIKE '%xyz%', this query would correspond to a SQL query like this:

SELECT ID, Title, Description, Comments, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
FROM   Category
WHERE  (@checkTitle=1 AND Title LIKE '%Supplier%') 
    OR (@checkDescription=1 AND Description LIKE  '%Supplier%')

score 1 · Answer 3 · answered Dec 13 '13 at 21:46

1

Using Lambda. Might work for you.

context.Category.Where(x=>x.Title.Contains("Supplier") || x.Description.Contains("Supplier")).Select(x=>new {....});

answered Dec 13 '13 at 21:46

AFrieze

844
1
10
26

score 0 · Accepted Answer · answered Dec 14 '13 at 00:56

I ended up solving this with a combination of ideas from evanmcdonnal and dasblinkenlight. I gave each of them an upvote since unfortunately, I could not give them each credit for having helped answer the question. This is what I ended up writing.

private static IQueryable<Db.Category> FilterContains(string searchFor, string excludedBrFields, IQueryable<Db.Category> categories)
{
    if (searchFor.StartsWith("*") && searchFor.EndsWith("*"))
    {
        searchFor = searchFor.Substring(1, searchFor.Length - 2);
    }
    searchFor = searchFor.ToLower();

    var predicate = PredicateBuilder.False<Db.Category>();
    if (!excludedBrFields.Contains("Title"))
    {
        predicate = predicate.Or(x => x.Title.ToLower().Contains(searchFor));
    }

    if (!excludedBrFields.Contains("Description"))
    {
        predicate = predicate.Or(x => x.Description != null && x.Description.ToLower().Contains(searchFor));
    }

    if (!excludedBrFields.Contains("Comments"))
    {
        predicate = predicate.Or(x => x.Comments != null && x.Comments.ToLower().Contains(searchFor));
    }
    return categories.Where(predicate.Compile()).AsQueryable();
}

Valin · Answer 5 · 2013-12-13T21:53:25.663

-1

Here is LINQ example:

public IEnumerable<Items> FindBySupplier(string supplier)
{
return context.Items.Where(i => i.Title.ToLower().Contains(supplier.ToLower()) || i.Description.ToLower().Contains(supplier.ToLower());

}

edited Dec 13 '13 at 21:53

answered Dec 13 '13 at 21:45

Valin

2,285
1
15
10

1

You call to lower then pass Contains a string that starts with an uppercase letter... That code will never work. – evanmcdonnal Dec 13 '13 at 21:48
Yeah, sry, I meant Supplier is a variable, not hardcoded string. – Valin Dec 13 '13 at 21:51
that still doesn't work. If you're doing string comparisons and apply to lower or to upper to one of the strings you must apply it to the other your logic is unsound. – evanmcdonnal Dec 13 '13 at 21:53
This example works in my program – Valin Dec 13 '13 at 21:54
Depending on the data source you are querying...the query may be case insensitive. MySQL would be one example of this – AFrieze Dec 13 '13 at 22:01

How can I dynamically build a linq OR query where one of several columns can match the search string?

5 Answers5