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?

Cavetto

Have a look at learn.microsoft.com/en-us/dotnet/api/… , 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: c-sharpcorner.com/blogs/… – Shoa

Is this LINQ to SQL or LINQ to EF? ToString seems to work for me in LINQ to SQL statically or dynamically. – Hartzell

If the type is numeric, you could try SqlFunctions.StringConvert instead. – Hartzell

@Hartzell 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. – Cavetto

Which version from EF are you using? – Ekaterinburg

Maybe DbFunctions.Like() may help you – Sanitation

Answer 1

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

Ekaterinburg

I am not using EF Core. Contains() can only be used by string type object, other than that throws error. – Cavetto

Much appreciated. I couldn't find this functionality until I saw this comment. Works perfectly for efcore – Moriarty

Answer 2

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.

Campbellbannerman

Answer 3

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.'

Fasta

Answer 4

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.

Gorse