0

I am trying to write code for getting records from EF which are similar to already existing records. (Exactly the same that SO is doing with Questions that may already have your answer). I have a title and I want to found similar records in database.

I found this SO question. I am sure the PredicateBuilder is the way to do this kind of things.

But, I didn't found a way how can I sort results by how many words are in both titles. (First records that matches more words and so on.)

For example:
My database has records:

1 Test my code
2 Another test
3 This is similar test
4 Test code for another partner with bugs
5 Test bugs

and user enters Test my code for bugs.
Result would be sorted like this:

4 Test code for another partner with bugs //(3 matches: test, code and bugs)
5 Test bugs                               //(2 matches: test and bugs)
1 Test my code                            //(2 matches: test and code
2 Another test                            //(1 match: test)
3 This is similar test                    //(1 match: test)

I am using ASP.NET Core 1.1

Community
  • 1
  • 1
Makla
  • 9,899
  • 16
  • 72
  • 142
  • If you down vote a question please explain, because I really didn't find the answer. Thank you. – Makla Mar 03 '17 at 07:56
  • There is no short answer for your question. There is no silver bullet that would do full text search for you. If you are stick to SQL Server please read about full text search there - https://msdn.microsoft.com/en-us/library/ms142571.aspx. If you have options in choosing database you might consider NOSQL storages like `ElasticSearch`. – klashar Mar 03 '17 at 09:08
  • @klashar: Yes I am using SQL Server 2016 and I have sa account for DB. I will check your link and NOSQL storages. – Makla Mar 03 '17 at 09:59
  • 1
    If you decide to go Full-text-search way - check how you can make EF to produce corresponding query - http://www.entityframework.info/Home/FullTextSearch – klashar Mar 03 '17 at 10:43

2 Answers2

2

I think this is the linq code that you are looking for 

List<string> vals = new List<string> { "Test my code",
"Another test",
"This is similar test",
"Test code for another partner with bugs",
"Test bugs"};
string[] searchTerms = "Test my code for bugs".Split(' ');
List<string> results = (from p in vals orderby 
    searchTerms.Count(u=>p.Contains(u)) select p).ToList();
foreach(var val in results)
{
    Console.WriteLine(val);
}

here is the csharppad with the result http://csharppad.com/gist/ee0e1531b873201163c7e4658c9a01f8

Liviu Boboia
  • 1,734
  • 1
  • 10
  • 21
  • 1
    The question was to make EF create effective query and current one will only for objects loaded in memory of ASP.NET Core. – klashar Mar 03 '17 at 10:20
  • this code can easily be translated into linq to sql, i was just showing the he can sort the results – Liviu Boboia Mar 03 '17 at 10:29
  • Any link proving this can be done? As of now it seems you suggesting to load all records from DB to memory when user do search. – klashar Mar 03 '17 at 10:39
  • I edited the response, he just needs to replace the vals in "from p in vals" to his dbContext.table – Liviu Boboia Mar 03 '17 at 11:29
1

Moving comments notes to a separate answer

It seems that you need to have a full text search in your DB to match your query. To do this on SQL server you need:

  1. Create Fulltext index for all applicable fields.
  2. Choose appropriate query that you are going to run to match records from DB.
  3. Make EF to make translate linq to SQL query to proper full text query. You can use suggestion from @Liviu Boboia with proper interseptor

But if you like to get SO behavior please check the site architecture There is a reason why ElasticSearch used for search. Consider possibility to use ElasticSearch at your project

klashar
  • 2,519
  • 2
  • 28
  • 38