How to do SQL Like % in Linq?

Question

I have a procedure in SQL that I am trying to turn into Linq:

SELECT O.Id, O.Name as Organization
FROM Organizations O
JOIN OrganizationsHierarchy OH ON O.Id=OH.OrganizationsId
where OH.Hierarchy like '%/12/%'

The line I am most concerned with is:

where OH.Hierarchy like '%/12/%'

I have a column that stores the hierarchy like /1/3/12/ for example so I just use %/12/% to search for it.

My question is, what is the Linq or .NET equivalent to using the percent sign?

Answer 1

For those how tumble here like me looking for a way to a "SQL Like" method in LINQ, I've something that is working very good.

I'm in a case where I cannot alter the Database in any way to change the column collation. So I've to find a way in my LINQ to do it.

I'm using the helper method SqlFunctions.PatIndex witch act similarly to the real SQL LIKE operator.

First I need enumerate all possible diacritics (a word that I just learned) in the search value to get something like:

déjà     => d[éèêëeÉÈÊËE]j[aàâäAÀÂÄ]
montreal => montr[éèêëeÉÈÊËE][aàâäAÀÂÄ]l
montréal => montr[éèêëeÉÈÊËE][aàâäAÀÂÄ]l

and then in LINQ for exemple:

var city = "montr[éèêëeÉÈÊËE][aàâäAÀÂÄ]l";
var data = (from loc in _context.Locations
                     where SqlFunctions.PatIndex(city, loc.City) > 0
                     select loc.City).ToList();

So for my needs I've written a Helper/Extension method

   public static class SqlServerHelper
    {

        private static readonly List<KeyValuePair<string, string>> Diacritics = new List<KeyValuePair<string, string>>()
        {
            new KeyValuePair<string, string>("A", "aàâäAÀÂÄ"),
            new KeyValuePair<string, string>("E", "éèêëeÉÈÊËE"),
            new KeyValuePair<string, string>("U", "uûüùUÛÜÙ"),
            new KeyValuePair<string, string>("C", "cçCÇ"),
            new KeyValuePair<string, string>("I", "iîïIÎÏ"),
            new KeyValuePair<string, string>("O", "ôöÔÖ"),
            new KeyValuePair<string, string>("Y", "YŸÝýyÿ")
        };

        public static string EnumarateDiacritics(this string stringToDiatritics)
        {
            if (string.IsNullOrEmpty(stringToDiatritics.Trim()))
                return stringToDiatritics;

            var diacriticChecked = string.Empty;

            foreach (var c in stringToDiatritics.ToCharArray())
            {
                var diac = Diacritics.FirstOrDefault(o => o.Value.ToCharArray().Contains(c));
                if (string.IsNullOrEmpty(diac.Key))
                    continue;

                //Prevent from doing same letter/Diacritic more than one time
                if (diacriticChecked.Contains(diac.Key))
                    continue;

                diacriticChecked += diac.Key;

                stringToDiatritics = stringToDiatritics.Replace(c.ToString(), "[" + diac.Value + "]");
            }

            stringToDiatritics = "%" + stringToDiatritics + "%";
            return stringToDiatritics;
        }
    }

If any of you have suggestion to enhance this method, I'll be please to hear you.

Answer 2

Try this, this works fine for me

from record in context.Organization where record.Hierarchy.Contains(12) select record;

Answer 3

I do always this:

from h in OH
where h.Hierarchy.Contains("/12/")
select h

I know I don't use the like statement but it's work fine in the background is this translated into a query with a like statement.

Answer 4

Well indexOf works for me too

var result = from c in SampleList
where c.LongName.IndexOf(SearchQuery) >= 0
select c;

Answer 5

Contains is used in Linq ,Just like Like is used in SQL .

string _search="/12/";

. . .

.Where(s => s.Hierarchy.Contains(_search))

You can write your SQL script in Linq as Following :

 var result= Organizations.Join(OrganizationsHierarchy.Where(s=>s.Hierarchy.Contains("/12/")),s=>s.Id,s=>s.OrganizationsId,(org,orgH)=>new {org,orgH});

Answer 6

.NET core now has EF.Functions.Like