ASP.NET Core 6 MVC : searching for posts that contain a group of tags

Question

I have a web app to create a post, as well as tag that post with as many tags as a user wants (given the tag exists in the app's database). I'm now trying to implement a search function that will return all posts that contain that group of tags (e.g. searching for "Test" and "Another_test" will show posts that contain at least both of those tags.)

The post's models looks like this (some fields removed for brevity):

// Media refers to a 'post'
public class Media
{
    public Guid Id { get; set; }
    public string? Title { get; set; }
    public string? Description { get; set; }
    public DateTime? UploadDate { get; set; }
    public List<Tag>? Tags { get; set; }
}

public class Tag
{
    public Guid Id { get; set; }
    public string Title { get; set; } = String.Empty;
    public List<Media>? Medias { get; set; }
}

I am using MariaDB as my database.

I initially looked around on SO and found this similar post. I tried to implement gaiazov's answer as below:

var selectedTagIds = context.Tags
                            .Include(t => t.Medias)
                            .Where(tag => Tags.Contains(tag.Title))
                            .Select(x => x.Id);

var tagPosts = context.Medias
                      .Include(m => m.Tags)
                      .Where(post => !(from selectedTag in selectedTagIds
                                       join tag in post.Tags on selectedTag equals tag.Id 
                                       into postTags
                                       from tag in postTags.DefaultIfEmpty()
                                       select 1).Any());

return tagPosts.OrderByDescending(p => p.UploadDate);

However, any and all searches would simply return an error

Unknown column 'm.Id' in 'where' clause

Looking at the query that LINQ created shows the following query:

SELECT 
    m.Id, m.Description, m.Title, m.UploadDate, 
    t2.MediasId, t2.TagsId, t2.Id, t2.Title
FROM 
    Medias AS m
LEFT JOIN 
    (SELECT m1.MediasId, m1.TagsId, t3.Id, t3.Title
     FROM MediaTag AS m1
     INNER JOIN Tags AS t3 ON m1.TagsId = t3.Id) AS t2 ON m.Id = t2.MediasId
WHERE 
    NOT (EXISTS (SELECT 1
                 FROM Tags AS t
                 LEFT JOIN  
                     (SELECT m0.MediasId, m0.TagsId, t1.Id, t1.Title
                      FROM MediaTag AS m0
                      INNER JOIN Tags AS t1 ON m0.TagsId = t1.Id
                      WHERE m.Id = m0.MediasId --This line throws an error
                     ) AS t0 ON t.Id = t0.Id
                 WHERE t.Title IN ('Test', 'Another_test') 
                   AND (t0.Id IS NULL)))
ORDER BY 
    m.UploadDate DESC, m.Id, t2.MediasId, t2.TagsId

The WHERE m.Id = m0.MediasId line causes an error since m appears to not be in the scope of that join. I also tried to run the query without that line and it still didn't return all posts containing the two tags.

Answer 1

based on the solution you provided it should be:

var selectedTagIds = _context.Tag
                            .Include(t => t.Medias)
                            .Where(tag => Tags.Contains(tag.Title))
                            .Select(x => x.Id);
var tagposts = _context.Media
              .Include(m => m.Tags)
              .Where(media => !(
                             from selectedTagId in selectedTagIds
                             join tag in media.Tags on selectedTagId equals tag.Id 
                             into postTags
                             from tag in postTags.DefaultIfEmpty()
                             where tag.Id == null
                             select 1
                             ).Any()).ToList();

As the document mentioned:

In a left outer join, all the elements in the left source sequence are returned, even if no matching elements are in the right sequence. To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. You can use null as the default value for any reference type, or you can specify a user-defined default type.

this line where tag.Id == null is important and shound't be deleted

based on LongChalk's solution, notice if ToList() was excued ,your C# codes would be translated into Sql commands and excuted.Delete ToList() and the codes would work

var selectedTagIds = _context.Tag
                            .Include(t => t.Medias)
                            .Where(tag => Tags.Contains(tag.Title))
                            .Select(x => x.Id);
            
            var tagPosts = _context.Media
                                  .Include(m => m.Tags)
                                  .Where(media =>
                                     selectedTagIds.All(id => media.Tags.Select(y => y.Id).Contains(id))).ToList();