如何做SQL像%在Linq?

我在SQL中有一个过程，我试图转化为Linq:

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

我最关心的一行是:

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

我有一个存储层次结构的列，例如/1/3/12/，所以我只使用%/12/%来搜索它。

我的问题是，Linq或。net中使用百分号的等价是什么?

446817 次浏览
小开
最佳答案 
.Where(oh => oh.Hierarchy.Contains("/12/"))

你也可以使用.StartsWith()或.EndsWith()。

小开

我假设您正在使用Linq-to-SQL*(参见下面的注释)。如果是，使用字符串。包含字符串。StartsWith和字符串。EndsWith生成使用SQL LIKE操作符的SQL。

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 }

或

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

注意: * =如果您正在使用ADO。Net实体框架(EF / L2E)，注意它不会像Linq-to-SQL那样进行相同的转换。尽管L2S进行了适当的转换，但L2E v1(3.5)将转换为一个t-sql表达式，该表达式将强制对您正在查询的表进行全表扫描，除非在where子句或连接筛选器中有其他更好的鉴别器 更新:这在EF/L2E v4 (.net 4.0)中是固定的，所以它会像L2S一样生成一个SQL LIKE。

小开

如果你使用VB。NET，那么答案将是“*”。这是你的where从句的样子…

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

备注:“*”匹配零个或多个字符。下面是关于Like操作符的msdn文章。

小开

用这个:

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

indexOf也适用于我

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

试试这个，我觉得挺好的

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

我总是这样做:

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

我知道我不使用like语句，但它在后台工作很好，这是翻译成一个查询与like语句。

小开

使用这样的代码

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;
}
小开

包含在Linq中使用，就像就像在SQL中使用一样。

string _search="/12/";

……

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

你可以像下面这样在Linq中编写SQL脚本:

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

如果你不匹配数字字符串，最好使用通用大小写:

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

对于那些像我一样在LINQ中寻找“类似SQL”方法的人来说，我有一些工作得非常好的东西。

我在一个情况下，我不能改变数据库以任何方式改变列排序规则。 所以我必须在LINQ中找到一种方法来做到这一点

我正在使用帮助方法SqlFunctions.PatIndex，其行为类似于真正的SQL LIKE操作符。

首先，我需要枚举搜索值中所有可能的变音符(我刚刚学会的一个词)，以获得类似于:

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

然后在LINQ中例如:

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

因此，为了满足我的需求，我写了一个Helper/Extension方法

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

如果你们有任何建议来改进这个方法，我很乐意倾听。

小开

.NET core现在有EF.Functions.Like

  var isMatch = EF.Functions.Like(stringThatMightMatch, pattern);
小开 
System.Data.Linq.SqlClient.SqlMethods.Like("mystring", "%string")
小开

晚了，但是我把它放在一起，以便能够使用SQL Like样式通配符进行字符串比较:

public static class StringLikeExtensions
{
/// <summary>
/// Tests a string to be Like another string containing SQL Like style wildcards
/// </summary>
/// <param name="value">string to be searched</param>
/// <param name="searchString">the search string containing wildcards</param>
/// <returns>value.Like(searchString)</returns>
/// <example>value.Like("a")</example>
/// <example>value.Like("a%")</example>
/// <example>value.Like("%b")</example>
/// <example>value.Like("a%b")</example>
/// <example>value.Like("a%b%c")</example>
/// <remarks>base author -- Ruard van Elburg from StackOverflow, modifications by dvn</remarks>
/// <remarks>converted to a String extension by sja</remarks>
/// <seealso cref="https://stackoverflow.com/questions/1040380/wildcard-search-for-linq"/>
public static bool Like(this String value, string searchString)
{
bool result = false;


var likeParts = searchString.Split(new char[] { '%' });


for (int i = 0; i < likeParts.Length; i++)
{
if (likeParts[i] == String.Empty)
{
continue;   // "a%"
}


if (i == 0)
{
if (likeParts.Length == 1) // "a"
{
result = value.Equals(likeParts[i], StringComparison.OrdinalIgnoreCase);
}
else // "a%" or "a%b"
{
result = value.StartsWith(likeParts[i], StringComparison.OrdinalIgnoreCase);
}
}
else if (i == likeParts.Length - 1) // "a%b" or "%b"
{
result &= value.EndsWith(likeParts[i], StringComparison.OrdinalIgnoreCase);
}
else // "a%b%c"
{
int current = value.IndexOf(likeParts[i], StringComparison.OrdinalIgnoreCase);
int previous = value.IndexOf(likeParts[i - 1], StringComparison.OrdinalIgnoreCase);
result &= previous < current;
}
}


return result;
}


/// <summary>
/// Tests a string containing SQL Like style wildcards to be ReverseLike another string
/// </summary>
/// <param name="value">search string containing wildcards</param>
/// <param name="compareString">string to be compared</param>
/// <returns>value.ReverseLike(compareString)</returns>
/// <example>value.ReverseLike("a")</example>
/// <example>value.ReverseLike("abc")</example>
/// <example>value.ReverseLike("ab")</example>
/// <example>value.ReverseLike("axb")</example>
/// <example>value.ReverseLike("axbyc")</example>
/// <remarks>reversed logic of Like String extension</remarks>
public static bool ReverseLike(this String value, string compareString)
{
bool result = false;


var likeParts = value.Split(new char[] {'%'});


for (int i = 0; i < likeParts.Length; i++)
{
if (likeParts[i] == String.Empty)
{
continue;   // "a%"
}


if (i == 0)
{
if (likeParts.Length == 1) // "a"
{
result = compareString.Equals(likeParts[i], StringComparison.OrdinalIgnoreCase);
}
else // "a%" or "a%b"
{
result = compareString.StartsWith(likeParts[i], StringComparison.OrdinalIgnoreCase);
}
}
else if (i == likeParts.Length - 1) // "a%b" or "%b"
{
result &= compareString.EndsWith(likeParts[i], StringComparison.OrdinalIgnoreCase);
}
else // "a%b%c"
{
int current = compareString.IndexOf(likeParts[i], StringComparison.OrdinalIgnoreCase);
int previous = compareString.IndexOf(likeParts[i - 1], StringComparison.OrdinalIgnoreCase);
result &= previous < current;
}
}


return result;
}
}
小开

如果您需要为单元测试这样的客户端操作提供LIKE功能，CodeProject的这个方法很好地模拟了通配符的行为。

有点像@史蒂夫·阿克曼的回答，但更全面。

/// Published on CodeProject: http://www.codeproject.com/Articles/
///         608266/A-Csharp-LIKE-implementation-that-mimics-SQL-LIKE
/// </remarks>
public static bool Like(this string s, string match, bool CaseInsensitive = true)
{
//Nothing matches a null mask or null input string
if (match == null || s == null)
return false;
//Null strings are treated as empty and get checked against the mask.
//If checking is case-insensitive we convert to uppercase to facilitate this.
if (CaseInsensitive)
{
s = s.ToUpperInvariant();
match = match.ToUpperInvariant();
}
//Keeps track of our position in the primary string - s.
int j = 0;
//Used to keep track of multi-character wildcards.
bool matchanymulti = false;
//Used to keep track of multiple possibility character masks.
string multicharmask = null;
bool inversemulticharmask = false;
for (int i = 0; i < match.Length; i++)
{
//If this is the last character of the mask and its a % or * we are done
if (i == match.Length - 1 && (match[i] == '%' || match[i] == '*'))
return true;
//A direct character match allows us to proceed.
var charcheck = true;
//Backslash acts as an escape character.  If we encounter it, proceed
//to the next character.
if (match[i] == '\\')
{
i++;
if (i == match.Length)
i--;
}
else
{
//If this is a wildcard mask we flag it and proceed with the next character
//in the mask.
if (match[i] == '%' || match[i] == '*')
{
matchanymulti = true;
continue;
}
//If this is a single character wildcard advance one character.
if (match[i] == '_')
{
//If there is no character to advance we did not find a match.
if (j == s.Length)
return false;
j++;
continue;
}
if (match[i] == '[')
{
var endbracketidx = match.IndexOf(']', i);
//Get the characters to check for.
multicharmask = match.Substring(i + 1, endbracketidx - i - 1);
//Check for inversed masks
inversemulticharmask = multicharmask.StartsWith("^");
//Remove the inversed mask character
if (inversemulticharmask)
multicharmask = multicharmask.Remove(0, 1);
//Unescape \^ to ^
multicharmask = multicharmask.Replace("\\^", "^");
                

//Prevent direct character checking of the next mask character
//and advance to the next mask character.
charcheck = false;
i = endbracketidx;
//Detect and expand character ranges
if (multicharmask.Length == 3 && multicharmask[1] == '-')
{
var newmask = "";
var first = multicharmask[0];
var last = multicharmask[2];
if (last < first)
{
first = last;
last = multicharmask[0];
}
var c = first;
while (c <= last)
{
newmask += c;
c++;
}
multicharmask = newmask;
}
//If the mask is invalid we cannot find a mask for it.
if (endbracketidx == -1)
return false;
}
}
//Keep track of match finding for this character of the mask.
var matched = false;
while (j < s.Length)
{
//This character matches, move on.
if (charcheck && s[j] == match[i])
{
j++;
matched = true;
break;
}
//If we need to check for multiple charaters to do.
if (multicharmask != null)
{
var ismatch = multicharmask.Contains(s[j]);
//If this was an inverted mask and we match fail the check for this string.
//If this was not an inverted mask check and we did not match fail for this string.
if (inversemulticharmask && ismatch ||
!inversemulticharmask && !ismatch)
{
//If we have a wildcard preceding us we ignore this failure
//and continue checking.
if (matchanymulti)
{
j++;
continue;
}
return false;
}
j++;
matched = true;
//Consumse our mask.
multicharmask = null;
break;
}
//We are in an multiple any-character mask, proceed to the next character.
if (matchanymulti)
{
j++;
continue;
}
break;
}
//We've found a match - proceed.
if (matched)
{
matchanymulti = false;
continue;
}


//If no match our mask fails
return false;
}
//Some characters are left - our mask check fails.
if (j < s.Length)
return false;
//We've processed everything - this is a match.
return true;
}

提交答案