LINQ query where List should contain ALL elements of another List not working-CodePudding

I have many to many relationship between Video and Tag entities

Here you can see a few entries in Video table, joint table TagVideo, and Tag table.

Each Video can have multiple Tags and each Tag can have multiple Videos.

Im trying to create a LINQ query, where I can pass in list of Tag's and find all Video's which contain ALL of those tags.

This is my LINQ query right now, which I believe should work.

public async Task<List<CreateVideoWithTagNamesDTO>> FindVideosByTag(ICollection<Tag> tags)
{    
    var tagIds = tags.Select(r => r.Id).ToArray();
    var result = await _db
        .Where(p => p.Tags
            .Select(r => r.Id)
            .All(tag => tagIds.Contains(tag)))
        .Select(item => new CreateVideoWithTagNamesDTO
        {
            Description = item.Description,
            Title = item.Title,
            Url = item.Url,
            Tags = _mapper.Map<IList<CreateTagDTO>>(item.Tags)
        })
        .ToListAsync();   
    
    return result;    
}

However this is not working properly, here are some queries I did. First I sent just a single Tag named "Pin" and there is a Video which does have a "Pin" tag. So I should get this video, however I dont get any Videos back

Debugger shows all incoming values correct.

When I query with 2 tags, "Pin" and "Passing" i get back all 3 videos, which Is not correct. As only one video has both tags so Im expecting that video.

Finally, when I query only with "Passing" I get 2 correct videos which have only Passing tag

How do I correct query to only select videos which have correct input tags?

CodePudding user response：

Use the following query:


var tagIds = tags.Select(r => r.Id).ToArray();
var tagCount = tagIds.Length;

var result = await _db.Videos
   .Where(p => p.Tags.Where(t => tagIds.Contains(t.Id)).Count() == tagCount)
   .Select(...)