CODEWITHSUNDEEP
c#linqentity-framework-coreentity-framework-6
Hannah Hayes
Hannah Hayes

Reputation: 315

Using LINQ to return a list of entities that match all values in a list passed in as a parameter

I have a class called Business, a class called Tag and a class called BusinessTagLink.

Both Business and Tag have the following property:

public virtual IList<BusinessTagLink>? BusinessTagLinks { get; set; }

and every business is linked to one or more tags via the BusinessTagLink table.

My BusinessTagLink class looks like this:

public class BusinessTagLink : BaseEntity
    {
        public int BusinessTagLinkId { get; set; }
        public int BusinessId { get; set; }
        public int TagId { get; set; }
        public virtual Business Business { get; set; }
        public virtual Tag Tag { get; set; } 
    }

I am trying to build a filter that will allow a user to filter down the list of businesses by interacting with a list of tags. I so far have this:

public static IQueryable<Business> Filter(this IQueryable<Business> businesses, List<int> tagIds)
        {
            if (tagIds.Count == 0)
                return businesses;

            return businesses.Where(x => x.BusinessTagLinks.Any(y => tagIds.Contains(y.TagId)));
        }

This works great if I want to return businesses where any of the business tag link tag IDs match any of the tag IDs passed in as a parameter. However, I would like it to return just businesses that have all of the tag IDs passed in as a parameter. E.g:

Business A is linked to tag IDs 1, 2 and 3 via the business tag links table. Business B is linked to tags 2 and 3. Upon passing in a list of 1, 2 I only want the filter to return Business A.

I'm using Entity Framework Core 6.0.1 and my database is SQL Server.

Upvotes: 1

Views: 1189

Answers (1)

Sonyck
Sonyck

Reputation: 196

Didnt really test it :D hopefully it works :D

public static IQueryable<Business> Filter(this IQueryable<Business> businesses, List<int> tagIds)
{
    if (tagIds.Count == 0)
        return businesses;

    foreach (var tagId in tagIds)
        businesses = businesses.Where(e => e.BusinessTagLinks.Any(l => l.TagId == tagId));

    return businesses;
}

also, your BusinessTagLink table does not need to have auto increment primary key as the unique identifier can be derived from BusinessId and TagId, having a composite PK makes more sense

  1. it will automatically create indexes on those two columns
  2. you will ensure that you do not insert accidentally invalid records as you should always have just one instance of Business-Tag link in your db

Upvotes: 2

Related Questions