C# Dynamic Linq: Implement "Like" in The Where Clause

Question

So I want to make a general sorter for my data. I have this code to get data from the database which will extract the data only which contains value.

using System.Linq.Dynamic;

public static IQueryable<object> SortList(string searchString, Type modelType, 
    IQueryable<object> model)
{
    ....

    string toStringPredicate = type == typeof(string) ? propertyName + 
        ".Contains(@0)" : propertyName + ".ToString().Contains(@0)";
    model = model.Where(propertyName + " != NULL AND " + toStringPredicate, value);
}

The model is this:

public class ManageSubscriberItems
{
    public int? UserId { get; set; }
    public string Email { get; set; }
    public Guid SubscriberId { get; set; }
}

When I call:

models = (IQueryable<ManageSubscriberItems>)EcommerceCMS.Helpers.FilterHelper
    .SortList(searchString, typeof(ManageSubscriberItems), models);

if(models.Any())

It throws this error:

"LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."

EDIT

I found the problem, but I still cannot fix it. So if the property is not string, it will throw an error when calling .ToString().Contains().

model = model.Where(propertyName + " != NULL AND " + propertyName + 
    ".ToString().Contains(@0)", value);

What I want is to implement LIKE in the query. Can anyone help me?

Have a look at https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.sqlclient.sqlfunctions.stringconvert?redirectedfrom=MSDN&view=netframework-4.7.2#overloads , i don't think you can use ToString() in LinQ or in EF. What you could do is to make a property/variable and set its value .ToString() before your query, than use that property/variable (and remove the .ToString()). Something along these lines: https://www.c-sharpcorner.com/blogs/exception-linq-to-entities-does-not-recognize-the-method-tostring — Dimitri, Apr 08 '19 at 09:14
Is this LINQ to SQL or LINQ to EF? `ToString` seems to work for me in LINQ to SQL statically or dynamically. — NetMage, Apr 08 '19 at 19:26
If the type is numeric, you could try `SqlFunctions.StringConvert` instead. — NetMage, Apr 08 '19 at 19:29
@NetMage This is Linq to SQL in `IQueryable` form. Probably it will work if I called `ToList()` or `ToEnumerable()` first, but I do not want to process this in the memory. I tried using `SqlFunctions.StringConvert` in the dynamic expression (written in string) and it gives me an error. Right now I can only check if it is numeric, I use `==`, if it is string I use `.Contains()`. This is not what I want. It is not behaving like `Like` operator. — Alvin Stefanus, Apr 09 '19 at 06:56
Maybe [DbFunctions.Like()](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbfunctions.like) may help you — kapsiR, Apr 12 '19 at 07:25

Stef Heyenrath · Answer 1 · 2019-04-17T16:17:57.667

If you use System.Linq.Dynamic.Core with EF Core, you have an option to use

var q = context.Cars.Where(config, "DynamicFunctions.Like(Brand, \"%a%\")");

See this link for an example: https://github.com/StefH/System.Linq.Dynamic.Core/blob/6fc7fcc43b248940560a0728c4d181e191f9eec1/src-console/ConsoleAppEF2.1.1/Program.cs#L117

And I just tested in linqpad connecting to a real database, and code like this just works?

var result1 = Entity1s.Where("Url != NULL AND it.Url.Contains(@0)", "e");

[UPDATE 2019-04-17]]

In case you don't know the type, you can cast it to object and then cast that to a string.

Code:

var r = Entity1s.Select("string(object(Rating))").Where("Contains(@0)", "6");

I am not using EF Core. Contains() can only be used by string type object, other than that throws error. — Alvin Stefanus, Apr 16 '19 at 07:22

score 4 · Answer 2 · answered Apr 15 '19 at 08:16

so the problem here is that IQueryable thing happens on the SQL server not in C#... so SQL server doesn't know anything about .toString() method. so => and Like operator it self works on strings.. so it's nvarchar and varchar data types in SQL server. I could give You an example of how to achieve it if you could tell me more about your problem and what You want to achieve. could do a sample.

score 0 · Answer 3 · answered Apr 16 '19 at 10:23

You already have a "Like" in Linq that can run in the database and works with strings, it's called "IndexOf":

 ((IQueryable)model).Where(m => m.Property.IndexOf(searchString) == 1);

According to MSDN: IndexOf(string)

'The zero-based index position of value if that string is found, or -1 if it is not. If value is Empty, the return value is 0.'

valerysntx · Answer 4 · 2019-04-19T02:33:31.290

So I want to make a general sorter for my data.

instead of fixing 'invoke issue', general way should use generics, like

public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, 
                                       string property,
                                       bool asc = true) where T : class
{
    //STEP 1: Validate MORE!
    var searchProperty = typeof(T).GetProperty(property);
    if (searchProperty == null) throw new ArgumentException("property");

     ....

    //STEP 2: Create the OrderBy property selector
    var parameter = Expression.Parameter(typeof(T), "o");
    var selectorExpr = Expression.Lambda(Expression.Property(parameter, property), parameter)        

    //STEP 3: Update the IQueryable expression to include OrderBy
    Expression queryExpr = source.Expression;
    queryExpr = Expression.Call(typeof(Queryable), asc ? "OrderBy" : "OrderByDescending",
                                  new Type[] { source.ElementType, searchProperty.PropertyType },
                                 queryExpr, 
                                selectorExpr);

    return source.Provider.CreateQuery<T>(queryExpr);
}

having property name string and direction usually used for making 'column sorting' on data.

Next are relation predicates are coming, and 'Linq.Dynamic' seems reasonable when doing from scratch, but there is generic and canonical form exists.

C# Dynamic Linq: Implement "Like" in The Where Clause

EDIT

4 Answers4