SQL LIKE in Linq

本文关键字：Linq in LIKE SQL | 更新日期: 2023-09-27 18:18:04

在添加这个问题之前，我确实在stackoverflow上搜索了类似的问题，但我找不到。互联网上的大多数问题都使用LIKE与字符串(例如LIKE '%ABC%')，但我需要与不同表的现有列进行比较。

我需要为select语句写一个linq查询，如下-

 select * 
from [dbo].[BaseClaim]
where WPId like (select WPId from UserProfiles where ID='1459')

我想出了下面的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);

和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]))

所以很明显，我的linq不工作，因为它比较基本声明。wpID到用户配置文件。wpid而不是LIKE。

SQL LIKE in Linq

没有直接的等效，但根据模式的不同，有一些方法的工作方式类似。

  • string.Contains("pattern")等价于LIKE '%pattern%'
  • string.StartsWith("pattern")等于LIKE 'pattern%'
  • string.EndsWith("pattern")等价于LIKE '%pattern'

然而，在你的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;

或

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;

必须使用contains例如

 .Where(a => a.Name.Contains("someStrig")

包含参数为常量时的generate Like

希望有所帮助

此查询适用于实体框架

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

而不是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
注意:Linq to SQL抛出NotSupportedException:

只支持可在客户端求值的参数的字符串。包含方法。

edit:

询问者说他的UserProfile中有通配符(%)。WPId应该像SQL中的通配符一样工作。

你可以用这个:

BaseClaims.Where(b => UserProfiles.Where(u => u.ID == "1459").Any(u => u.WPId.Contains(b.WPId))).ToList();

在这个例子中，我试图模仿你的实体/表。

    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
    }
相关文章：