How to use EXISTS in where condition of LINQ?

Question

I have gone through couple of similar questions but couldn't find what I wanted.

I need to find tags which has a ID in Answers table, since there could be multiple answers records containing the tag ID I need to take care of the duplicates. This is the reason why I cant perform simple join.

I thought about using an EXISTS but couldn't figure out the way to do it yet.

This is the working SQL

SELECT DISTINCT TagName
FROM Tags tag
JOIN Answers ans ON ans.StID = tag.Id  
WHERE tag.SchId = 472
 AND ans.isValid = 1

This is what I tried in LINQ with no success

(from tag in Tags
 where tag.Id.Any(from ans in Answers
                  where ans.StID == tag.Id
                  && tag.SchId == 472
                  && ans.isValid == true
                  select ans.ID)
select tag.TagName

It would be helpful if someone can suggest a way to correctly implement this query in LINQ.

This post suggests `Any` is how to do that. http://stackoverflow.com/questions/4074013/how-to-do-a-sql-where-exists-in-linq-to-entities. When you used `Any` it didn't do that? How are you checking? — Peter Ritchie, Jan 18 '17 at 20:08
`where tag.SchId == 472 && Answers.Any(ans => ans.StID == tag.Id && ans.isValid)` — Ivan Stoev, Jan 18 '17 at 20:08
@PeterRitchie No it didnt work, It said type int? (which is a type of tag id) doesnt contain a definition for Any — Y.Solanki, Jan 18 '17 at 20:11
@Y.Solanki ah, okay, I missed that part. Any/EXISTS is a row-level check so you can't use it on a particular field. I think what ivanstoev showed, or something similar, should do what you want. — Peter Ritchie, Jan 18 '17 at 20:13

score 1 · Answer 1 · answered Jan 18 '17 at 20:10

1

You'll generally want to accomplish this by using the Enumerable.Any() method to pass in a function that will check if any items in your collection meet a given criteria :

var x = Tags.Where(t => t.SchId == 472 && Answers.Any(a => a.StId == tag.Id && a.isValid))
            .Select(t => t.TagName)
            .Distinct();

This should give you a list of distinct tags that have a SchId of 472 and have at least one valid answer.

answered Jan 18 '17 at 20:10

Rion Williams

74,820
37
200
327

Surely `a.StId == tag.Id` should be `a.StId == t.Id` – Auspex Jul 14 '21 at 16:40

score 0 · Answer 2 · answered Jan 18 '17 at 20:10

Look into the Distinct method which will get rid of your duplicates. Try the code below.

(from tag in Tags
where tag.Id.Any(from ans in Answers
                  where ans.StID == tag.Id
                  && tag.SchId == 472
                  && ans.isValid == true
                  select ans.ID).Distinct(ans => ans.ID)
select tag.TagName

How to use EXISTS in where condition of LINQ?

2 Answers2