another option instead using AsEnumerable() in linq EF, when using custom method in linq

Question

I have below code

class Program
    {
        static void Main(string[] args)
        {
            string keyword = "m";
            using (TestingEntities1 db = new TestingEntities1())
            {
                var models = db.Teachers.AsEnumerable().Where(a => RemoveA(a.Name).Contains(keyword));
                foreach (var item in models)
                {
                    Console.WriteLine(item.Name);
                }
            }
        }
        static string RemoveA(string input)
        {
            return input.Replace('a', ' ');
        }
    }

as you can see in my code i must use AsEnumerable() to use custom function works because if i dont use i will get something error saus "LINQ to Entities does not recognize the method ' ' method, and this method cannot be translated into a store expression."

and then i ralized that asenumerable make slow becaseu i found that here How to improve AsEnumerable performance in EF says "you're doing is that you're downloading the whole BilBillMasters and BilBillDetails tables and then doing some processing on those in your application, rather than on the SQL server. This is bound to be slow." please see first anwer for more detail, So is there any way to make my code faster, my case i have more than one million data in my database

Didn't try it out but i believe if you do it inline and use string instead of char, that it gets translated to sql. try it out. a => a.Name.Replace("a", " ").Contains(keyword) — gsharp, Oct 19 '19 at 07:51

score 2 · Answer 1 · answered Oct 19 '19 at 08:00

2

Entity Framework needs to translate your query into a SQL query, which means it needs to know how to convert every part of it into SQL. Your query contains calling a RemoveA function, which Entity Framework doesn't know how to deal with. You can solve this by converting your code into

class Program
{
    static void Main(string[] args)
    {
        string keyword = "m";
        using (TestingEntities1 db = new TestingEntities1())
        {
            var models = db.Teachers.Where(a => a.Name.Replace("a", " ").Contains(keyword));
            foreach (var item in models)
            {
                Console.WriteLine(item.Name);
            }
        }
    }
}

See this MSDN page for which functions you can use inline in a LINQ to Entities query.

Alternatively, another option would be to convert your function into an Expression that Entity Framework can understand, you can see an example of it in this StackOverflow answer.

answered Oct 19 '19 at 08:00

Daniel Crha

675
5
13

i think this is not clean code way, how about if i want to query 1000 times so i must declare a.Name.Replace("a", " ") for 1000 times – Joseph Oct 19 '19 at 12:46
@instantDev that wasn't specified in the question that you want to reuse the function 1000 times. However what's the real use case for your question? if you replace "a" with " " but you're searching for "m" then you can safe your replace at all. – gsharp Oct 19 '19 at 13:03
don;'t you see on question title " when using custom method" – Joseph Oct 19 '19 at 13:08
And the correct answer is "don't use a custom method if you don't want to fetch all the data". – David Browne - Microsoft Oct 19 '19 at 13:21

another option instead using AsEnumerable() in linq EF, when using custom method in linq

1 Answers1