Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL LIKE in Linq

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.

like image 531
Sai Avatar asked Jun 13 '13 07:06

Sai


People also ask

How do you like a query in LINQ?

In LINQ to SQL, we don't have a LIKE operator, but by using contains(), startswith(), and endswith() methods, we can implement LIKE operator functionality in LINQ to SQL.

Is SQL like LINQ?

Compared to SQL, LINQ is simpler, tidier, and higher-level. It's rather like comparing C# to C++.

Which is faster SQL or LINQ?

Stored procedures are faster as compared to LINQ query since they have a predictable execution plan and can take the full advantage of SQL features. Hence, when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure.

Is LINQ to SQL deprecated?

LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it's no longer under active development.


3 Answers

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;
like image 152
p.s.w.g Avatar answered Sep 20 '22 03:09

p.s.w.g


You must use contain for example

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

Contains generate Like when the parameter is constant

Hope it helps

like image 45
microtechie Avatar answered Sep 19 '22 03:09

microtechie


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.

like image 26
Sergey Berezovskiy Avatar answered Sep 18 '22 03:09

Sergey Berezovskiy