0

I have this query to be executed :

Select * From Products WHERE Title like '%search text%' 
ORDER BY Difference(Title, 'search text') DESC

Now I want to implement the above query using EntityFrameworkCore and linq.

So how can I call difference function to order products by closest match in Title column?

Simple Code
  • 2,354
  • 2
  • 27
  • 56

2 Answers2

1

Every IQueryable holds an Expression and a Provider. The Expression holds the query that must be performed. The Provider knows who has to execute the query, usually a database management system. It is the task of the Provider to translate the Expression into the language that the database understands (something SQL-like) and to execute the query. The Provider will fetch the results in an efficient way and return the queried data as an enumerable object.

The IQueryable implements IEnumerable.

When you use LINQ functions like ToList(), FirstOrDefault(), Any(), or use the query in a foreach, then internally IEnumerable.GetEnumerator() is called and Enumerator.MoveNext()

This will order the Provider to translate the Expression into SQL and execute the query. The returned enumerable is used to enumerate over the returned items.

It is the task of the programmer of the class that implements the IQueryable to implement the translation of the Expression into SQL. This is not easy, and I think the people who created entity framework did a great job.

However, some items known in SQL are very difficult to implement. Among those are the notions of SoundEx and Difference. I'm not sure, but I think that one of the reasons that made this difficult is that they are typically something used in SQL, and not in any other kind of IQueryable systems.

In fact, there are a several functions that are not supported by entity framework. See Supported and unsupported LINQ methods (LINQ to entities).

Your DbContext is an abstract representation of your database model. Users of it should not care whether it uses Microsoft SQL, MySQL, or whether it is a data collection that doesn't use anything similar to SQL.

But if you are absolutely certain that it is okay to limit your DbContext to only a certain kind of databases, one that knows the concepts of SoundEx and Difference, consider creating a stored procedure for your query. See How to call a Stored Procedure in Entity Framework

Harald Coppoolse
  • 28,834
  • 7
  • 67
  • 116
  • So I have to execute stored procedure to execute my above query which uses SoundEx and Difference – Simple Code Oct 16 '18 at 09:05
  • 1
    If you want to use SQL Difference, then apparently you do. Another possibility would be to fetch the data without ordering and use a local `Difference` function to order by. But I think such a function will be hard to find, especially because it is not strictly specified what it should return – Harald Coppoolse Oct 16 '18 at 09:18
0

SQL can not understand the Difference function written in c#. To make it work you will have to fetch values from Products table in a c# collection like List

Then do ordering on that list using Difference function

ManishM
  • 583
  • 5
  • 7
  • So if I have a query which matches 1000 products. Does that mean I need to fetch them all and order them as an in-memory collection? – Simple Code Oct 16 '18 at 08:04