CODEWITHSUNDEEP
c#sql-serverlinqdynamicdynamic-linq
Alvin Stefanus
Alvin Stefanus

Reputation: 2153

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

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?

Upvotes: 13

Views: 6652

Answers (4)

valerysntx
valerysntx

Reputation: 526

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.

Upvotes: 0

Stef Heyenrath
Stef Heyenrath

Reputation: 9830

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

Upvotes: 16

Diogo Neves
Diogo Neves

Reputation: 644

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

Upvotes: 0

Edgars Salmiņš
Edgars Salmiņš

Reputation: 71

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.

Upvotes: 4

Related Questions