How to make LINQ execute a (SQL) LIKE range search

I am in big need of help, i have been trying to do this for some time now.

So I have this Query:

Select name from BlaBlaBla

order by 

case when name like '9%' then 1 end,
case when name like '8%' then 1 end,
case when name like '7%' then 1 end,
case when name like '6%' then 1 end,
case when name like '5%' then 1 end,
case when name like '4%' then 1 end,
case when name like '3%' then 1 end,
case when name like '2%' then 1 end,
case when name like '1%' then 1 end,
case when name like '0%' then 1 end,

name

And I want to implement it in a new C#, Asp.Net, class, in my Solution, to the Domain Project, so it will be an OrderType Filter, for some function…

for now I have this:

var param = Expression.Parameter(typeof(T), "item");

var paramName = Expression.Property(param, "Name");
var regexMatch = Expression.Constant("^[0-9]");
var startsWithDigit = Expression.Call(typeof(Regex), "IsMatch", 
                                             null, paramName);

var lambda = Expression.Lambda<Func<T, bool>>(startsWithDigit, 
                                              param);

return namesList.OrderBy(lambda)
           .ThenBy(BlaBla1())
           .ThenByDescending(BlaBla2())
           .ThenByDescending(BlaBla3())
           .ThenBy(BlaBla4());

But it tells me, that Expression does not contain “IsMatch” method.

Can you please help me?
Thank you!!!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The problem here is that expressions containing Regex can’t be translated to SQL, so even when you’d succeed in building a correct expression, you can’t use it in LINQ to a SQL backend. However, SQL’s LIKE method also supports range wildcards like [0-9], so the trick is to make your LINQ translate to SQL containing a LIKE statement.

LINQ-to-SQL offers the possibility to use the SQL LIKE statement explicitly:

return namesList.OrderBy(r => SqlMethods.Like(r.Name, "[0-9]%")) ...

This SqlMethods class can only be used in LINQ-to-SQL though. In Entity Framework there are string functions that translate to LIKE implicitly, but none of them enable the range wildcard ([x-y]). In EF a statement like …

return namesList.OrderBy(r => r.Name.StartsWith("[0-9]")) ...

… would translate to nonsense:

[Name] LIKE '~[0-9]%' ESCAPE '~'

I.e. it vainly looks for names starting with the literal string “[0-9]”. So as long as you keep using LINQ-to-SQL SqlMethods.Like is the way to go.

In Entity Framework 6.1.3 (and lower) we have to use a slightly different way to obtain the same result …

return namesList.OrderBy(r => SqlFunctions.PatIndex("[0-9]%", c.Name) == 1) ...

… because PatIndex in SqlFunctions also supports range pattern matching.

But in Entity Framwork 6.2 we’re back on track with LINQ-to-SQL because of the new DbFunctions.Like function:

return namesList.OrderBy(r => DbFunctions.Like(r.Name, "[0-9]%")) ...

Finally, also Entity Framework core has a Like function:

return namesList.OrderBy(r => EF.Functions.Like(r.Name, "[0-9]%")) ...

Method 2

Below you see a sample for this kind of way to handle cases for your orderings.

    static void Main(string[] args)
    {
        List<Obvious> list = new List<Obvious>();
        for (int i = 0; i < 100; i++)
        {
            list.Add(new Obvious(i.ToString(), i));
        }

        string name = list[30].name;
        switch (name)
        {
            case "9":
                list.OrderBy(o => o.perc)
                    .ThenByDescending(o => o.name);
                break;
            default:
                list.OrderByDescending(o => o.name)
                    .ThenBy(o => o.perc);
                break;
        }
    }

    public class Obvious
    {
        public string name { get; set; }
        public int perc { get; set; }
        public Obvious(string _name, int _perc)
        {
            this.name = _name;
            this.perc = _perc;
        }

    }

Method 3

If I was you I wouldn’t try using Expressions to solve this issue since it brings in a lot of complexity.

I see that you would like to have a generic method, so it can work with different domain entities, yet you are expecting that each entity has a Name property.
You can solve this in a more simple way by defining interface that contains Name property. Like this:

    public static void Main()
    {
        var test = new List<YourDomainEntity>()
        {
            new YourDomainEntity() { Name = "1test", OtherProperty = "1"},
            new YourDomainEntity() { Name = "2test",  OtherProperty = "2" },
            new YourDomainEntity() { Name = "2test", OtherProperty = "1"   }
        };

        var k = Foo(test).ToList();
    }

    public interface INameOrderable
    {
        string Name { get; set; }
    }

    public interface IOtherPropertyOrderable
    {
        string OtherProperty { get; set; }
    }

    public static IEnumerable<T> Foo<T>(IEnumerable<T> list) where T : INameOrderable, IOtherPropertyOrderable
    {
        return list.OrderBy(a => a.Name, new NamesDescComparer()).ThenBy(b => b.OtherProperty);
    }

    public class NamesDescComparer : IComparer<string>
    {
        public int Compare(string x, string y) => -String.CompareOrdinal(x, y);
    }

    class YourDomainEntity : INameOrderable, IOtherPropertyOrderable
    {
        public string OtherProperty { get; set; }
        public string Name { get; set; }
    }

I believe the method Foo is what you are looking for.

Note the where T : INameOrderable part. It restricts usage of this method to entities that implement INameOrderable interface

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0