SQL LIKE in Linq

Question

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.

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') 

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

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

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

Answer 1

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

• string.Contains("pattern") is equivalent to LIKE '%pattern%'
• string.StartsWith("pattern") is equivalent to LIKE 'pattern%'
• string.EndsWith("pattern") is equivalent to 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:

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;

Answer 2

You must use contain for example

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

Contains generate Like when the parameter is constant

Hope it helps

Answer 3

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 LIKE it generates CHARINDEX operation

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:

Only arguments that can be evaluated on the client are supported for the String.Contains method.