CODEWITHSUNDEEP
c#linqdynamicwhere-clausesql-like
Yann39
Yann39

Reputation: 15719

Linq to sql, using like in dynamic where clause

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);
  1. I cannot use Contains(), StartsWith() or EndsWith() as search parameters can be in the form "%my%value".
  2. I cannot manage to use SqlMethods.Like() in dynamic WHERE clause.
  3. 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 ?

Upvotes: 2

Views: 1607

Answers (2)

Kabbalah
Kabbalah

Reputation: 501

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)));

Upvotes: 0

hutchonoid
hutchonoid

Reputation: 33306

I would use a PredicateBuilder.

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

Upvotes: 2

Related Questions