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.

Answer 4

EDITED:

The asker said that he has wild cards (%) in his UserProfile.WPId that should work like the wildcards in SQL.

You can use this one:

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

    }