CODEWITHSUNDEEP
c#linqentity-framework
user2413009
user2413009

Reputation:

entity framework with linq query and stored expression error

I want to search for file name inside a database table

If the user entered too many words in the input field I want to find all the file names that contain all the words entered.

    private bool SmartSearch(string textTosSearchIn, string textTosSearch)
    {
        textTosSearch = textTosSearch.ToLower();
        textTosSearch = textTosSearchIn.ToLower();
        var allStrings = textTosSearch.Split(' ');

        foreach (var item in allStrings)
        {

            if (!textTosSearchIn.Contains(item.Trim()))
            {
                return false;
            }
        }

        return true;
    }

r = con.MyFiles.Where(x => SmartSearch(fname.Text, x.FileName)).ToList();

this error fired

LINQ to Entities does not recognize the method 'Boolean SmartSearch(System.String, System.String)' method, and this method cannot be translated into a store expression.

How can I make a stored expression to do this search in linq?

Upvotes: 2

Views: 897

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

Entity Framework does not execute C# code on server side. It translates C# code which you have on client side to SQL text command, and then executes SQL on server. So, in your case EF cannot translate custom C# method SmartSearch into SQL.

From your code it looks like you have FileName field in database, which you split by white space (pretty strange to me). Then you check if any of these parts contained in text you have on client. Unfortunately String.Split cannot be translated into SQL, so either move filtering to client 

 con.MyFiles.AsEnumerable() // transfers all data from db to client
            .Where(f => SmartSearch(fname.Text, f.FileName))
            .ToList();

or consider to re-work your filtering algorithm. E.g. getting all file names which have any of entered words looks to me like this:

 var words = fname.Text.Split();
 var files = con.MyFiles.Where(f => words.Any(w => f.FileName.Contains(w)));

Upvotes: 2

Boris Parfenenkov
Boris Parfenenkov

Reputation: 3279

SmartSearch can't be translated in SQL. If you want use this function in LINQ, you may, previously, get List of yout object, and then filter:

con.MyFiles.ToList().Where(x => SmartSearch(fname.Text, x.FileName)).ToList();

But this will be work slowly (because you get all MyFiles in program)

Also you may write stored function in SQL (for your smartSearch), get it in EntityFramework and then call this function in LINQ (link to example).

Upvotes: 0

Related Questions