Linq to sql, using like in dynamic where clause

Question

I need to use LIKE operators in a dynamic WHERE clause.

I am currently using = operators but need to replace with LIKE :

String whereClause = "1 = 1 AND ";
whereClause = whereClause + (search.Id != null ? "Id = " + search.Id + " AND " : "");
whereClause = whereClause + (search.FirstName != null ? "FirstName = \"" + search.FirstName + "\" AND " : "");
whereClause = whereClause + (search.LastName != null ? "LastName = \"" + search.LastName + "\" AND " : "");
whereClause = whereClause + (search.StudentName != null ? "StudentName = \"" + search.StudentName + "\" AND " : "");
whereClause = whereClause + (search.Country != null ? "CountryLabel = \"" + search.Country + "\" AND " : "");
whereClause = whereClause + (search.ZipCode != null ? "ZipCode = \"" + search.ZipCode + "\" AND " : "");
whereClause = whereClause + (search.City != null ? "City = \"" + search.City + "\" AND " : "");
whereClause = whereClause.Remove(whereClause.Length - 5);

IEnumerable<MyClassDto> res = (
    from ...
    where ...
    select new MyClassDto() {
        ...
    }
).Where(whereClause);

I cannot use Contains(), StartsWith() or EndsWith() as search parameters can be in the form "%my%value".
I cannot manage to use SqlMethods.Like() in dynamic WHERE clause.
I don't want to include it in the WHERE clause of the MyClassDto query as it slow down the execution time, it is why I apply another WHERE clause on the Enumerable, matching only search parameters.

Any suggestions ?

In these situations, I prefer to use old `SqlClient` namespace, and populate the models myself. — Saeed Neamati, Nov 13 '13 at 11:18
I finally used a raw SQL query using `myDb.Database.SqlQuery()`, not optimal for maintenance but seems to be the simplest and fastest solution allowing to use the `LIKE` operator. — Yann39, Nov 14 '13 at 10:49

score 2 · Answer 1 · answered Nov 13 '13 at 11:45

2

I would use a PredicateBuilder.

Please see here: http://www.albahari.com/nutshell/predicatebuilder.aspx

answered Nov 13 '13 at 11:45

hutchonoid

32,982
15
99
104

I wasn't able to use this solution with `SqlMethods.Like()` – Yann39 Nov 14 '13 at 11:54
Please see this question for how to use similar to the like statement. http://stackoverflow.com/questions/835790/how-to-do-sql-like-in-linq – hutchonoid Nov 14 '13 at 12:39
Accepted answer recommands using `Contains()` to replace `LIKE` operator? I am very surprised that this answer got 217 upvotes... Also `SqlMethods.Like()` is available only in Linq to SQL, not in Linq to entities. And I cannot build a dynamic where clause using Linq to SQL. But using the link you provided I came across this website that seems to offers a solution : http://jendaperl.blogspot.ch/2011/02/like-in-linq-to-entities.html – Yann39 Nov 14 '13 at 13:57

Kabbalah · Answer 2 · 2013-11-14T12:44:05.130

This might not be as elegant as the PredicateBuilde mentioned in another answer but it should get the job done.
You could provide a method as a where clause like this:
Edit: Replace == with any other compare operator if needed.

public bool WhereClause(MyClassDto s, SearchClass search)
{
    if ((s.Id == null || s.Id == search.Id) &&
        (s.FirstName == null || s.FirstName == search.FirstName) &&
        (s.LastName == null || s.LastName == search.LastName) &&
        (s.StudentName == null || s.StudentName == search.StudentName) &&
        (s.Country == null || s.Country == search.Country) &&
        (s.ZipCode == null || s.ZipCode == search.ZipCode) &&
        (s.City == null || s.City == search.City))
    {
        return true;
    }
        return false;
}

Usage:

IEnumerable<MyClassDto> res = (
    from ...
    where ...
    select new MyClassDto() {
        ...
    }
).Where(my => WhereClause(my, search));

Alternative solution:

Create a list of methods and pass them to Where:

var whereClause = new List<Func<MyClassDto, SearchClass, bool>>();
if (search.Id != null) whereClause.Add((s, dto) => s.Id == dto.Id);
if (search.FirstName != null) whereClause.Add((s, dto) => s.FirstName == dto.FirstName);
if (search.LastName != null) whereClause.Add((s, dto) => s.LastName == dto.LastName);
if (search.StudentName != null) whereClause.Add((s, dto) => s.StudentName == dto.StudentName);
if (search.Country != null) whereClause.Add((s, dto) => s.Country == dto.Country);
if (search.ZipCode != null) whereClause.Add((s, dto) => s.ZipCode == dto.ZipCode);
if (search.City != null) whereClause.Add((s, dto) => s.City == dto.City);

IEnumerable<MyClassDto> res = (
    from ...
    where ...
    select new MyClassDto() {
        ...
    }
).Where(dto => whereClause.All(func => func(dto, search)));

Your solution works, but the goal was to avoid writting search parameters in the `WHERE` clause if they are not specified, for performance reasons. Even if all parameters are null, the query will be very slower than if you don't specify them. — Yann39, Nov 14 '13 at 10:53
The trick is that it seems there are no way to use `LIKE` in Linq to Entities, it is only allowed in Linq to SQL. So your solution will work using `==` operator, but not using `SqlMethods.Like()`. — Yann39, Nov 14 '13 at 13:48
You could use other operators that work like `Like` instead of `==`. Or is that not possible in your case? — Kabbalah, Nov 14 '13 at 14:12
Unfortunately client is asking for a real `%` operator in the input search parameters. But maybe here is a solution : http://stackoverflow.com/a/4935438/1274485 — Yann39, Nov 14 '13 at 14:19
@Yann39 The accepted answer in your link only deals with the specific case where the text is surrounded by `%`. It won't work with something like `%my%value`. — Kabbalah, Nov 14 '13 at 15:50
Of course, my link is not pointing to the accepted answer, but to Jenda's answer ;) — Yann39, Nov 18 '13 at 15:04

Linq to sql, using like in dynamic where clause

2 Answers2