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