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.
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.
Compared to SQL, LINQ is simpler, tidier, and higher-level. It's rather like comparing C# to C++.
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.
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.
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;
You must use contain for example
.Where(a => a.Name.Contains("someStrig")
Contains generate Like when the parameter is constant
Hope it helps
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With