SQL word wide "%like%" search in Entity Framework 6 using lambda expression

Question

I have a SQL query shown below and I have to write an equivalent query in Entity Framework in C#. I have to make a join between to tables and I have to use SQL LIKE operator with each word in the search string. In the given example, the search string is "Life-Span Development 16E 99 Subject Index". I have been trying to write c# code for this but unable to achieve the expected result. Could you please help me to convert the SQL query to Entity framework equivalent query?

SQL query:

SELECT 
    [titles].[title],
    [assets].[filename]
FROM 
    titles 
INNER JOIN 
    assets ON titles.ID = assets.ID 
WHERE 
    (title LIKE '%Life-Span%' 
     AND title LIKE '%Development%' 
     AND title LIKE '%16E%' 
     AND title LIKE '%99%' 
     AND title LIKE '%Subject%' 
     AND title LIKE '%Index%')

*C# code:

static void Main(string[] args)
{
    string searchText = "Life-Span Development 16E 99 Subject Index";

    using (Entities db = new Entities())
    {
        var result = db.titles
                    .Join(db.assets,
                            p => p.tid,
                            e => e.tid,
                            (p, e) => new {
                                 title = p.title1,
                                 fileName = e.filename
                            });               

        var searchTextArray = searchText.ToLower().Split(' ');
        result = result.Where(t => searchTextArray.Any(s => t.title.ToLower().Contains(s)));

        foreach(var item in result)
        {
            Console.WriteLine(string.Format("Title = {0} and finename = {1}", item.title, item.fileName));
        }
    }

    Console.ReadKey();
}

Does this compile? It doesn't look like the foreach would work with var? — Alexander Toptygin, Jul 16 '18 at 19:24
@AlexanderToptygin It compiles and runs. For each works with var without any issue. — Simant, Jul 16 '18 at 19:26
Well, how dynamic do you need to get? Because as a base case you could always db.SqlQuery("SELECT......").ToList(); — Alexander Toptygin, Jul 16 '18 at 19:31
@juharr The SQL query is correct and it gives me only one result in my result but the C# query gives me lots of records. My C# query doesn't give me a similar result as SQL query. Is there any alternate way to achieve this query in c#? Thanks! — Simant, Jul 16 '18 at 19:39
Possible duplicate of [How to do a LIKE query with linq?](https://stackoverflow.com/questions/3913697/how-to-do-a-like-query-with-linq) — Vinit, Jul 16 '18 at 19:44

score 2 · Accepted Answer · answered Jul 16 '18 at 19:59

This is the source of your problem:

result = result.Where(t => searchTextArray.Any(s => t.title.ToLower().Contains(s)));

Any() returns true if one match is found (or more, but it stops checking after one). It is the SQL equivalent of using OR.

What you want is this:

result = result.Where(t => searchTextArray.All(s => t.title.ToLower().Contains(s)));

All() returns true if all elements (in searchTextArray) have a match (in thise case, if all searchTextArray elements are contained in a given title). It is the SQL equivalent of using AND.

score 1 · Answer 2 · answered Jul 16 '18 at 19:57

1

Based on the updated failure mode (too many results), change .Any to .All :

    result = result.Where(t => searchTextArray.All(s => t.title.ToLower().Contains(s)));

answered Jul 16 '18 at 19:57

Alexander Toptygin

407
2
8

score 0 · Answer 3 · answered Jul 16 '18 at 19:44

A bit clunky but it does the job

namespace Like
{
    class Program
    {
        static bool Is(char a, char b)
        { 
              return a == b || 
              char.ToUpper(a) == b || 
              char.ToUpper(b) == a;
        }
        static bool IsLike(string sample, string query)
        {
            int k = 0;
            foreach (char c in sample)
            {
                if (!Is(c, query[k]))
                {
                    k = 0;
                    continue;
                }
                if (++k == query.Length)
                    return true;
            }
            return false;
        }

        static void Main(string[] args)
        {
            string testSample = "This is a str1ng 0f charac7er5";
            Console.WriteLine(IsLike(testSample, "this"));
            Console.WriteLine(IsLike(testSample, "of"));
            Console.WriteLine(IsLike(testSample, "chAra"));
            Console.ReadKey();
        }
    }
}

yields

TRUE
FALSE
TRUE

Vinit · Answer 4 · 2018-07-16T20:28:54.523

0

You may use SqlMethods.Like.

update your searchText variable as -

string searchText = "Life-Span%Development%16E%99%Subject%Index";

remove this code line -

var searchTextArray = searchText.ToLower().Split(' ');

then update the Where condition in results as

results = from r in results 
              where SqlMethods.Like(r.title,searchText )
              select r;

edited Jul 16 '18 at 20:28

answered Jul 16 '18 at 19:53

Vinit

2,540
1
15
22

That is for LINQ to SQL and this query is for EF. – NetMage Jul 16 '18 at 20:07

SQL word wide "%like%" search in Entity Framework 6 using lambda expression

4 Answers4