C# Linq 中的 SQL LIKE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17081065/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL LIKE in Linq
提问by Sai
Before adding this question, I did search on stackoverflow for similar ones but I couldnt find. Most of the questions over internet were using LIKE with a string (for eg LIKE '%ABC%') but I need to compare with an existing column of a different table.
在添加这个问题之前，我确实在 stackoverflow 上搜索过类似的问题，但我找不到。互联网上的大多数问题都使用带有字符串的 LIKE（例如 LIKE '%ABC%'），但我需要与不同表的现有列进行比较。
I need to write a linq query for the select statement as below -
我需要为 select 语句编写一个 linq 查询，如下所示 -
select *
from [dbo].[BaseClaim]
where WPId like (select WPId from UserProfiles where ID='1459')
I came up with below linq query but its not working as expected -
我想出了以下 linq 查询，但没有按预期工作 -
var result = (from claimsRow in context.BaseClaims
where (from upRow in context.UserProfiles
where upRow.ID == 1459
select upRow.WPId).Contains(claimsRow.WPId)
select claimsRow);
and the sql that above linq generates is as follows -
而上面linq生成的sql如下——
SELECT
[Extent1].[WPId] AS [WPId]
FROM [dbo].[BaseClaim] AS [Extent1]
WHERE EXISTS (SELECT
1 AS [C1]
FROM (SELECT
[UserProfiles].[ID] AS [ID],
[UserProfiles].[WPId] AS [WPId]
FROM [dbo].[UserProfiles] AS [UserProfiles]) AS [Extent2]
WHERE (1459 = [Extent2].[ID]) AND ([Extent2].[WPId] = [Extent1].[WPId]))
So its clear that my linq is not working as its comparing the baseclaim.wpID to userprofiles.wpid instead of LIKE.
所以很明显，我的 linq 无法正常工作，因为它将 baseclaim.wpID 与 userprofiles.wpid 进行比较而不是 LIKE。
采纳答案by p.s.w.g
There's no direct equivalent, but there are some methods work similarly, depending on the pattern.
没有直接的等价物，但有一些方法的工作方式类似，具体取决于模式。
string.Contains("pattern")is equivalent toLIKE '%pattern%'string.StartsWith("pattern")is equivalent toLIKE 'pattern%'string.EndsWith("pattern")is equivalent toLIKE '%pattern'
string.Contains("pattern")相当于LIKE '%pattern%'string.StartsWith("pattern")相当于LIKE 'pattern%'string.EndsWith("pattern")相当于LIKE '%pattern'
However, in your SQL query the pattern is dynamic, so I don't think there is a good way to convert it straight to Linq. If you know at design time that the pattern fits one of these cases you can use this:
但是，在您的 SQL 查询中，模式是动态的，因此我认为没有将其直接转换为 Linq 的好方法。如果您在设计时知道该模式适合这些情况之一，您可以使用它：
var result =
from claimsRow in context.BaseClaims
let wpId = context.UserProfiles.Single(upRow => upRow.ID == 1459).WPId
where claimsRow.WPId.Contains(wpId) // or StartsWith or EndsWith
select claimsRow;
Or possibly
或者可能
var wpId =
(from upRow in context.UserProfiles
where upRow.ID == 1459
select upRow.WPId)
.Single();
var result =
from claimsRow in context.BaseClaims
where claimsRow.WPId.Contains(wpId) // or StartsWith or EndsWith
select claimsRow;
回答by Microtechie
You must use contain for example
例如，您必须使用包含
.Where(a => a.Name.Contains("someStrig")
Contains generate Like when the parameter is constant
参数为常量时包含 generate Like
Hope it helps
希望能帮助到你
回答by Sergey Berezovskiy
This query works with Entity Framework
此查询适用于实体框架
from claimsRow in context.BaseClaims
let wpId = context.UserProfiles.Where(upRow => upRow.ID == 1459)
.Select(upRow => upRow.WPId)
.FirstOrDefault()
where wpId.Contains(claimsRow.WPId)
select claimsRow
But instead of LIKEit generates CHARINDEXoperation
但不是LIKE它生成CHARINDEX操作
SELECT * FROM [dbo].[BaseClaims] AS [Extent1]
LEFT OUTER JOIN (SELECT TOP (1) [Extent2].[WPId] AS [WPId]
FROM [dbo].[UserProfiles] AS [Extent2]
WHERE [Extent2].[ID] = 1459 ) AS [Limit1] ON 1 = 1
WHERE (CHARINDEX([Extent1].[WPId], [Limit1].[WPId])) > 0
Note: with Linq to SQL it throws NotSupportedException:
注意：使用 Linq to SQL 它会抛出 NotSupportedException：
Only arguments that can be evaluated on the client are supported for the String.Contains method.
String.Contains 方法仅支持可以在客户端上计算的参数。
回答by aiapatag
EDITED:
编辑：
The asker said that he has wild cards (%) in his UserProfile.WPId that should work like the wildcards in SQL.
提问者说%他的 UserProfile.WPId 中有通配符 ( )，应该像 SQL 中的通配符一样工作。
You can use this one:
你可以使用这个：
BaseClaims.Where(b => UserProfiles.Where(u => u.ID == "1459").Any(u => u.WPId.Contains(b.WPId))).ToList();
BaseClaims.Where(b => UserProfiles.Where(u => u.ID == "1459").Any(u => u.WPId.Contains(b.WPId))).ToList();
In this example, I'm trying to mimic your entities/tables.
在这个例子中，我试图模仿你的实体/表。
static void Main(string[] args)
{
List<BaseClaim> BaseClaims = new List<BaseClaim>()
{
new BaseClaim(){ WPId = "11123411" }, //match 1
new BaseClaim(){ WPId = "11123123" }, //match 2
new BaseClaim(){ WPId = "44423411" }, //match 3
new BaseClaim(){ WPId = "444AAAA" }, //match 3
new BaseClaim(){ WPId = "444BBBB" }, //match 3
new BaseClaim(){ WPId = "444QWQEQW" }, //match 3
new BaseClaim(){ WPId = "2314" },
new BaseClaim(){ WPId = "3214" }
};
List<UserProfile> UserProfiles = new List<UserProfile>()
{
new UserProfile(){ WPId="%112341%", ID="1459" }, //match 1
new UserProfile(){ WPId="%123", ID="1459" }, //match 2
new UserProfile(){ WPId="444%", ID="1459" }, //match 3
new UserProfile(){ WPId="5555", ID="1459" },
new UserProfile(){ WPId="2222", ID="1459" },
new UserProfile(){ WPId="1111", ID="4444" },
};
char[] asterisk = { '%' };
List<BaseClaim> result = BaseClaims.Where(b => UserProfiles.Where(u => u.ID == "1459").Any(u => u.WPId.StartsWith("%") && u.WPId.EndsWith("%") ? b.WPId.Contains(u.WPId.Trim(asterisk)) :
u.WPId.StartsWith("%") ? b.WPId.EndsWith(u.WPId.Trim(asterisk)) :
u.WPId.EndsWith("%") ? b.WPId.StartsWith(u.WPId.Trim(asterisk)) :
false)).ToList();
//this will result to getting the first 3 BaseClaims
}
