LINQ query with EF Core to find records with matching items from many-to-many relationship

Question

I'm quite new to .NET and EF Core and I'm building a classified listing type application. I want to compare records and find matching wanted/for sale classifieds based on their tags (linked via a many to many relationship in entity framework).

First I'm trying to make sure the user isn't trying to add a duplicate listing. I've spent an ungodly amount of time trying to figure out how to write the query but everything I try ends up with an exception at runtime and I feel like I'm trying the same things over and over.

I thought this query should work but I can't figure out why it doesn't, it builds without any errors but I get an exception at runtime of "Specified filter criteria was invalid":

public bool IsDuplicate(Classified classified)
{
    var idSet = classified.ClassifiedTags.Select(ct => ct.Tag.Id).ToHashSet<int>();
    var query = from c in _context.Classified 
                where c.ClassifiedTags.Select(ct => ct.Tag.Id).ToHashSet<int>().SetEquals(idSet) &&
                      c.User == classified.User
                select c;

    // .... return true if a record is found
}

The tables have a many to many join set up in EF Core: Classified <-> ClassifiedTag <-> Tag

My ApplicationDbContext looks like this

public DbSet<Tag> Tags { get; set; }
public DbSet<Classified> Classified { get; set; }
public DbSet<ClassifiedTag> ClassifiedTags { get; set; }

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    builder.Entity<Tag>().ToTable("Tag");
    builder.Entity<ClassifiedTag>().ToTable("ClassifiedTag");

    builder.Entity<ClassifiedTag>()
        .HasKey(ct => new { ct.ClassifiedId, ct.TagId });

    builder.Entity<ClassifiedTag>()
        .HasOne(c => c.Classified)
        .WithMany(ct => ct.ClassifiedTags)
        .HasForeignKey(ct => ct.ClassifiedId);

    builder.Entity<ClassifiedTag>()
        .HasOne(t => t.Tag)
        .WithMany(ct => ct.ClassifiedTags)
        .HasForeignKey(ct => ct.TagId);
}

My Classified model looks like this:

public class Classified
{
    public Guid Id { get; set; }
    public int Type { get; set; }
    public ApplicationUser User { get; set; }
    public DateTime DateCreated { get; set; }
    public List<ClassifiedTag> ClassifiedTags { get; set; }
}

public class ClassifiedTag {
    public Guid ClassifiedId { get; set; }
    public Classified Classified { get; set; }
    public int TagId { get; set; }
    public Tag Tag { get; set; }
}

And my Tag model looks like this:

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<ClassifiedTag> ClassifiedTags { get; set; }
}

I feel like this should be pretty straightforward but I'm really stuck for ideas at this point, any help would be really appreciated!

Answer 1

In your query ToHashSet<int>() can't be converted to SQL and an exception occurs.