CODEWITHSUNDEEP
c#linq
Y.Solanki
Y.Solanki

Reputation: 45

How to use EXISTS in where condition of LINQ?

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.

Upvotes: 0

Views: 6632

Answers (2)

Rion Williams
Rion Williams

Reputation: 76547

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.

Upvotes: 1

user7174959
user7174959

Reputation:

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

Upvotes: 0

Related Questions