CODEWITHSUNDEEP
c#linqlinq-to-sql
James
James

Reputation: 2881

Many to Many database query with LINQ

I have the following database tables in many-to-many relationships

Article
- Id

ArticleTag
- ArticleId
- TagId

Tag
- Id

*All Ids are int

I'm having trouble figuring out how to write a LINQ to SQL query that will return a list of Articles matching multiple tags that belong to the same Article.

Example: Return articles with tags 2 and 5

Article 1 has tags 2,3,5
Article 2 has tags 2,3,6,7
Article 3 has tags 2,5,9
Article 4 has tags 1,2,9,12

The result would only be Articles 1 and 3

Upvotes: 1

Views: 88

Answers (1)

Cyril Gandon
Cyril Gandon

Reputation: 17068

This query return the Article Ids which have exactly the tag 2 and 5 :

ctx.ArticleTags.Where(at => at.TagId == 2 || at.TagId == 5)
           .GroupBy(at => at.ArticleId)
           .Where(group => group.Count() == 2)
           .Select(group => group.Key);

The generated SQL will be :

SELECT ArticleId
FROM ArticleTag
WHERE TagId = 2 OR TagId = 5
GROUP BY ArticleId
HAVING Count(1) = 2

For a generic one, you can extend the comportement, if we suppose you have a list of tags :

var tagIds = new List<int>(){ 2, 5 };
ctx.ArticleTags.Where(at => tagIds.Contains(at.TagId))
           .GroupBy(at => at.ArticleId)
           .Where(group => group.Count() == tagIds.Count)
           .Select(group => group.Key);

Note that in this case, the generated SQL will use IN instead of a list or OR :

SELECT ArticleId
FROM ArticleTag
WHERE TagId in (2, 5)
GROUP BY ArticleId
HAVING Count(1) = 2

Upvotes: 1

Related Questions