How can I use external method in LINQ OrderBy

Question

finalProducts = context.Products.OrderBy(p => p.Name.LevenshteinDistance(query))
                                .Skip(from)
                                .Take(36)
                                .ToList<Product>();

I use method LevenshteinDistance() to find match with query, but it shows error

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

What should I do to sort products from database using this method?

@Evk I am not sure if that's relevant. No offence, I am just eager to know if some database can handle such stuff. — Sнаđошƒаӽ, Apr 09 '17 at 16:12
Possible duplicate of [LINQ to Entities does not recognize the method](http://stackoverflow.com/questions/7259567/linq-to-entities-does-not-recognize-the-method) — Sнаđошƒаӽ, Apr 09 '17 at 16:14
@Sнаđошƒаӽ I was asking this because correct way to solve this is to implement this function (levenstein distance) in database, then map it to EF model. Then you can achieve what OP wants (ordering by that distance in database, not in memory). Of course some arbitrary function cannot be just implemented in database, but this particular one can, because it is simple. — Evk, Apr 09 '17 at 16:25
@Evk Thanks. Actually I was inclined into thinking by your comment that some database provides ordering by Levenshtein distance, since it is kind of a basic thing :P — Sнаđошƒаӽ, Apr 09 '17 at 17:03
@Sнаđошƒаӽ well posgresql has it almost built-in: https://www.postgresql.org/docs/current/static/fuzzystrmatch.html. Though I doubt any EF provider has default mapping for it (like they have mappings for full-text search functions for example). — Evk, Apr 09 '17 at 18:17

score 0 · Answer 1 · edited Apr 09 '17 at 16:09

0

You are 'trying' to create a query to Database, using a method most possibly written by yourself. However, the problem is that framework does not know how to convert that method to a logical query. You need to order the result after you get the results from the database.

edited Apr 09 '17 at 16:09

Sнаđошƒаӽ

16,753
12
73
90

answered Apr 09 '17 at 13:46

Hasan Emrah Süngü

3,488
1
15
33

score 0 · Answer 2 · answered Apr 09 '17 at 13:47

0

You will need to order the in-memory list:

var products = context.Products.ToList();
var finalProducts = products.OrderBy(p => p.Name.LevenshteinDistance(query)).Skip(from).Take(36).ToList<Product>();

...or re-write the logic of your method in a stored procedure and execute this one. LINQ-to-Entities will obviously not be able to translate your custom C# method into T-SQL.

answered Apr 09 '17 at 13:47

mm8

163,881
10
57
88

@Avrohom Your comment is not clear enough. Can you explain/re-word it? – Sнаđошƒаӽ Apr 09 '17 at 17:37
1

I am calling the ToList() method to query the database and then I order the *in-memory* collection so I don't see your point. – mm8 Apr 09 '17 at 17:58
@Avrohom Your comments are self-contradictory. Calling `ToList()` executes the query in database, and the returned object is C# object, and thus the methods called afterwards all apply to C# objects, thus solving the problem. I too don't see your point. – Sнаđошƒаӽ Apr 09 '17 at 18:10
I am enumerating the query and then apply the custom method. So what's the difference beteeen your approach and mine? – mm8 Apr 10 '17 at 10:52

score 0 · Answer 3 · answered Apr 09 '17 at 17:40

As others have commented, the problem is that you are trying to run your own method on the database. You can't do this, as the database doesn't know about your method.

It's important to understand how Linq2Enties queries work. Until you enumerate the query, nothing is actually done. It's only when you enumerate the query (say by calling an extension method like ToList(), ToArray() or any of several OrAbc() methods) that the database accesses the data and returns some results to the code. At that point, any further code runs independently of the database, and so you can call your own method.

So, what you can do is this...

finalProducts = context.Products
                            .ToList<Product>() // Enumerates
                            .OrderBy(p => p.Name.LevenshteinDistance(query))
                            .Skip(from)
                            .Take(36)
                            .ToList<Product>();

That will work. However, you need to be aware of the consequences of doing this, as the database will return every product, which may take some time if you have a lot. You would have to try it and see if this is a problem for you.

If performance is an issue, then you would probably have to implement the distance function as a stored procedure, but that's a lot more work. Try it this way first.

How can I use external method in LINQ OrderBy

3 Answers3