SQL喜欢的LINQ [英] SQL LIKE in Linq

问题描述

在添加这个问题，我没有搜索计算器相似的人，但我无法找到。大多数通过互联网的问题是使用像一个字符串（如LIKE'％ABC％），但我需要一个不同的表的现有列比较。

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.

我需要写如下的SELECT语句LINQ查询 -

I need to write a linq query for the select statement as below -

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

我想出了下面的LINQ查询，但它不是按预期工作 -

I came up with below linq query but its not working as expected -

     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如下 -

and the sql that above linq generates is as follows -

   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不工作作为其比较baseclaim.wpID到userprofiles.wpid代替等。

So its clear that my linq is not working as its comparing the baseclaim.wpID to userprofiles.wpid instead of LIKE.

推荐答案

有没有直接对应，但也有一些方法的工作原理类似，根据不同的模式。

There's no direct equivalent, but there are some methods work similarly, depending on the pattern.

• string.Contains（模式）等同于 LIKE'％格局％'
• string.StartsWith（模式）等同于 LIKE'格局％'
• string.EndsWith（模式）等同于 LIKE'％模式

• string.Contains("pattern") is equivalent to LIKE '%pattern%'
• string.StartsWith("pattern") is equivalent to LIKE 'pattern%'
• string.EndsWith("pattern") is equivalent to LIKE '%pattern'

不过，在您的SQL查询的方式是动态的，所以我不认为有将其转换直接到LINQ的好方法。如果你知道，在这个模式正好符合这些情况下，您可以使用这一设计时间：

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:

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;

这篇关于SQL喜欢的LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！