P
P
Pavel_Develop2014-07-25 14:41:18
SQL
Pavel_Develop, 2014-07-25 14:41:18

How to competently and concisely implement the like sql method in IQueryable Linq?

There is a user interface containing two input fields (From, Where), in which the user enters a phone mask, while the options are: 91* (finds all numbers starting with 91), * 91 (ending with 91) or 1234 (ending with 1234 ). Essentially, the last two outcomes are the same.
Since it happens with strings, I decided to implement a class with an extension method for a string:

public static class StringExtenstions
    {
        public static bool Like(this string str, string value, string[] seps)
        {

            if (String.IsNullOrWhiteSpace(value))
                return true;

            value = value.Trim();
            var position = -1;

            foreach (var sep in seps)
            {
                if (!value.Contains(sep))
                    continue;

                position = value.IndexOf(sep, StringComparison.Ordinal);
                value = value.Replace(sep, String.Empty);
                break;
            }

            return position > 0 ? str.StartsWith(value) : str.EndsWith(value);
        }
    }

And I try to run this creation when selecting phones from the database:
var seps = new[] { "%", "*" };
calls = calls.Where(x => x.Source.Like(filter.From, seps) || x.Transfers.Any(t => t.SubjectFrom.Like(filter.From, seps)));

Unfortunately I get an error:
The LINQ to Entities expression cannot recognize the method "Boolean Like(System.String, System.String, System.String[])", so it cannot be converted to a store expression.
Any ideas on how to properly solve this problem with linq without messing around with a bunch of if conditions and writing sql queries in code (select * from calls where Source like '91%')?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Andrey Panov, 2014-07-25
@PanovAndrey

Good afternoon.
If you explain on your fingers, then Linq to Entities simply generates sql code, which is later executed on the side of the database, respectively, in the expression you cannot use operations that cannot be performed on the server side (as in your case, calling a function).
In my opinion, the simplest (probably the fastest) solution is to write a function on the base side and call it in the application code.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question