Translating a subquery in a WHERE clause into LINQ

Question

I'm having a problem translating a where clause that contains a select statement to LINQ-to-SQL. Here is the SQL snippet:

WHERE
(p.prioid IS NULL
OR p.prioid IN (SELECT prioid FROM mc.PRIORITY WITH (NOLOCK) WHERE prioid LIKE '1%' ))
AND s.id IN(@site)
AND (LTRIM(sv.glseg) IN ('703', '704', '705'))
AND (c.crewid IS NULL
OR c.crewid IN (SELECT crewid FROM mc.CREW WITH (NOLOCK) WHERE crewid NOT LIKE '2-%'
AND (crewid LIKE '%MAINT%'
OR crewid LIKE '%ELECT%'
OR crewid LIKE '%INST%')))
AND wot.id IN (SELECT id FROM mc.WORKORDERTYPE WITH (NOLOCK) WHERE id NOT LIKE '%Standing%')

Specifically, I'm having trouble with:

 WHERE
(p.prioid IS NULL
OR p.prioid IN (SELECT prioid FROM mc.PRIORITY WITH (NOLOCK) WHERE prioid LIKE '1%' ))

I translated it into the following LINQ statement, but I'm sure it's incorrect:

where (p.prioid = null || p.prioid == "1%")

Possible duplicate http://stackoverflow.com/questions/835790/how-to-do-sql-like-in-linq — Panagiotis Kanavos, Jun 11 '12 at 14:28
@Panagiotis Kanavos - Thank you for the information. However, that doesn't address the issue I asked about which was the select statement within the where clause. Are you saying that if I fix the "==" into the correct like statement that my translation will be correct? — Programming Newbie, Jun 11 '12 at 14:35

score 1 · Answer 1 · answered Jun 11 '12 at 14:34

1

The subquery can be handled using the Contains() or Any() keywords, but performance may be poor depending on your provider.

answered Jun 11 '12 at 14:34

Jason Coyne

6,509
8
40
70

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

The equivalents to LIKE in Linq-To-SQL are is Contains, StartsWith, EndsWith. L2S translates them to the equivalent LIKE statement.

Check similar question here How to do SQL Like % in Linq?

EDIT:

You may be able to get the exact same results as the original subquery using

Any(pr=>pr.prioid.StartsWith("1")

If p is an object in the Persons set, you could write something

Persons.Any(pr=>pr.prioid.StartsWith("1"))

I'm not sure if L2S can translate this or how the resulting SQL will perform.

edited May 23 '17 at 12:03

Community

1
1

answered Jun 11 '12 at 14:27

Panagiotis Kanavos

120,703
13
188
236

This addresses LIKE but it does not address the subquery. – Aaron Bertrand Jun 11 '12 at 14:31
1

Almost - the subquery checks prioid AND returns the matching prioids. The subquery actually sais "Match the prioids that start with 1, if they actually exist in the PRIORITY table". Is it OK to say "Match all prioids that start with 1", even if it's not an exact match in semantics? – Panagiotis Kanavos Jun 11 '12 at 14:37
@Panagiotis Kanavos - No, they wouldn't be the same. It seems that 3/4 of my problem with translating these statements into LINQ is understanding exactly what it is saying in SQL. The SQL statements I have been working with are much more complex than anything I encountered in my database class, so I've been struggling a bit. Thank you for your help. – Programming Newbie Jun 11 '12 at 14:47
Hint: it's not your fault. All those NOLOCKs and the multiple subqueries? This is a definite code smell – Panagiotis Kanavos Jun 11 '12 at 14:55

Translating a subquery in a WHERE clause into LINQ

2 Answers2