CODEWITHSUNDEEP
c#sqlentity-framework
Joseph
Joseph

Reputation: 35

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

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

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

Upvotes: 0

Views: 77

Answers (1)

Daniel Crha
Daniel Crha

Reputation: 685

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.

Upvotes: 2

Related Questions