How to do SQL Like % in Linq?

问题:

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?

回答1:

.Where(oh => oh.Hierarchy.Contains(\"/12/\"))

You can also use .StartsWith() or .EndsWith().

回答2:

Use this:

from c in dc.Organization
where SqlMethods.Like(c.Hierarchy, \"%/12/%\")
select *;

回答3:

I\'m assuming you\'re using Linq-to-SQL* (see note below). If so, use string.Contains, string.StartsWith, and string.EndsWith to generate SQL that use the SQL LIKE operator.

from o in dc.Organization
join oh in dc.OrganizationsHierarchy on o.Id equals oh.OrganizationsId
where oh.Hierarchy.Contains(@\"/12/\")
select new { o.Id, o.Name }

or

from o in dc.Organization
where o.OrganizationsHierarchy.Hierarchy.Contains(@\"/12/\")
select new { o.Id, o.Name }

Note: * = if you are using the ADO.Net Entity Framework (EF / L2E) in .net 3.5, be aware that it will not do the same translation as Linq-to-SQL. Although L2S does a proper translation, L2E v1 (3.5) will translate into a t-sql expression that will force a full table scan on the table you\'re querying unless there is another better discriminator in your where clause or join filters.
Update: This is fixed in EF/L2E v4 (.net 4.0), so it will generate a SQL LIKE just like L2S does.

回答4:

If you are using VB.NET, then the answer would be \"*\". Here is what your where clause would look like...

Where OH.Hierarchy Like \'*/12/*\'

Note: \"*\" Matches zero or more characters. Here is the msdn article for the Like operator.

回答5:

Well indexOf works for me too

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

回答6:

Use such code

try
{
    using (DatosDataContext dtc = new DatosDataContext())
    {
        var query = from pe in dtc.Personal_Hgo
                    where SqlMethods.Like(pe.nombre, \"%\" + txtNombre.Text + \"%\")
                    select new
                    {
                        pe.numero
                        ,
                        pe.nombre
                    };
        dgvDatos.DataSource = query.ToList();
    }
}
catch (Exception ex)
{
    string mensaje = ex.Message;
}

回答7:

In case you are not matching numeric strings, always good to have common case:

.Where(oh => oh.Hierarchy.ToUpper().Contains(mySearchString.ToUpper()))

回答8:

Try this, this works fine for me

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

回答9:

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.

回答10:

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});

回答11:

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.

回答12:

.NET core now has EF.Functions.Like

回答13:

System.Data.Linq.SqlClient.SqlMethods.Like(\"mystring\", \"%string\")