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

c# dynamic dynamic-linq linq sql-server

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?

1
10
4/8/2019 7:34:36 AM

Popular Answer

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");
9
4/17/2019 4:17:57 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow