SQL Like keyword in Dynamic Linq

Question

I want to use SQL's Like keyword in dynamic LINQ.

The query that I want to make is like this

select * from table_a where column_a like '%search%'

Where the column_a can be dynamically changed to other column etc

In this dynamic LINQ

var result = db.table_a.Where( a=> (a.column_a.Contains("search")) );

But the column can't be dynamically changed , only the search key can

How do we create a dynamic LINQ like

var result = db.table_a.Where("column_a == \"search\"");

That we can change the column and the search key dynamically

http://stackoverflow.com/a/2456070/1193727 – resnyanskiy Feb 17 '12 at 04:40 — resnyanskiy, Feb 17 '12 at 04:40

score 3 · Accepted Answer · edited May 13 '13 at 18:42

Create an ExtensionMethods class with this function

    public static IQueryable<T> Like<T>(this IQueryable<T> source, string propertyName, string keyword)
    {
        var type = typeof(T);
        var property = type.GetProperty(propertyName);
        string number = "Int";
        if (property.PropertyType.Name.StartsWith(number))
            return source;

        var parameter = Expression.Parameter(type, "p");
        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
        var constant = Expression.Constant("%" + keyword + "%");
        MethodCallExpression methodExp = Expression.Call(null, typeof(SqlMethods).GetMethod("Like", new Type[] { typeof(string), typeof(string) }), propertyAccess, constant);
        Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(methodExp, parameter);
        return source.Where(lambda);
    }

And then call it like this:

var result = db.table_a.Like("column_a", "%search%");

score 3 · Answer 2 · edited Sep 14 '11 at 20:19

3

This should work for you:

.Where("AColumnName.Contains(@0)", "Criteria")

edited Sep 14 '11 at 20:19

Jérôme Verstrynge

57,710
92
283
453

answered Feb 09 '11 at 10:30

sholdsto

31
2

score 0 · Answer 3 · edited May 23 '17 at 12:19

0

http://weblogs.asp.net/rajbk/archive/2007/09/18/dynamic-string-based-queries-in-linq.aspx

Addition:

Use an expression tree How do I create an expression tree to represent 'String.Contains("term")' in C#? That is what the dynamic linq library does internally.

edited May 23 '17 at 12:19

Community

1
1

answered Apr 27 '10 at 02:46

Raj Kaimal

8,304
27
18

I can't find the SQL's "like" sample in the above link. There is an "equal" sample using "==" though. But it's not what I'm looking for – strike_noir Apr 27 '10 at 03:47

score 0 · Answer 4 · answered Apr 27 '10 at 23:51

I do not believe there is a direct translation to SQL for the LIKE keyword in LINQ. You could build one if you used expression trees, but I haven't gotten that good yet.

What I do is something like this:

using System.Data.Linq.SqlClient;

if (!string.IsNullOrEmpty(data.MailerName))
    search = search.Where(a => SqlMethods.Like(a.Mailer.Name, string.Format("%{0}%", data.MailerName)));

where search is the query I'm building and data is the object containing the properties that hold the search criteria. I build the query dynamically by listing all of the possible search criteria in this way, which adds the appropriate Where methods to search.

Ali · Answer 5 · 2017-07-25T21:11:38.157

Maybe a bit late but Another approach is add Extention method that use Contains to simulate Like keyword as :

public static class DbHelpers
    {
        public static IQueryable<T> Like<T>(this IQueryable<T> source, string propertyName, string propertyValue)
        {
            var prop = typeof(T).GetProperty(propertyName);
            if (prop == null || prop.PropertyType.Name.StartsWith("Int"))
                return source;

            ParameterExpression parameter = Expression.Parameter(typeof(T), "row");
            Expression property = Expression.Property(parameter, propertyName);
            Expression value = Expression.Constant(propertyValue);

            var containsmethod = value.Type.GetMethod("Contains", new[] { typeof(string) });
            var call = Expression.Call(property, containsmethod, value);
            var lambda = Expression.Lambda<Func<T, bool>>(call, parameter);
            return source.Where(lambda);
        }
    }

And use of it:

var foo = entity.AsQueryable().Like("Name", "bla bla");

If send PropertyName with type of int, the method return original entity that you passed before to it.

SQL Like keyword in Dynamic Linq

5 Answers5