CODEWITHSUNDEEP
c#linqentity-framework
Iliya Krinchiyan
Iliya Krinchiyan

Reputation: 267

Select products that has all tags

I am using C# and Entity Framework. I have a Products table(Id, Name) that contains my products, a Tags table(Id, Name) that contains all available tags and a ProductTags table (Id, ProductId, TagId) that is used to assign tags to products. User can specify that he want to see products that have several tags (Int[] SelectedTagIds). The question is: how to get all the products, where each product has all the tags specified by user.

Now I am using this query 

`var reault = Context.Products
                     .Where(x => SelectedTagIds.All(y => 
                           (x.ProductTags.Select(z => z.TagId))
                     .Contains(y)));`

I wonder if this is a correct way or there is a better/faster way?

Upvotes: 0

Views: 212

Answers (1)

Michal Ciechan
Michal Ciechan

Reputation: 13898

You could use intersect and then check the length of both ensure that the intersected has the same count as the search tags, meaning that all search tags exist in ProductTags collection.

        var result =
            products.Where(
                x => x.ProductTags.Intersect(searchTags).Count() == searchTags.Length);

But you would need to run performance analysis which works better for you. But as Oren commented, are you actually having performance issues? As both of these collections are most likely so small, that you would not cause a bottleneck.

EDIT: Just checked the performance of intersect, it is slower than using .All with .Contains

Perf on my machine, creating 1000 result queries and enumerating them using ToList(), on a Set of 1000 products, with 2 search tags gives me the followign performance:

searchTags.All(search => x.ProductTags.Contains(search)) = 202ms

!searchTags.Except(x.ProductTags).Any() = 339ms

x.ProductTags.Intersect(searchTags).Count() == searchTags.Length = 389ms

If you really need to improve performance, you could use HashSet for your ProductTags and SelectedTagIds

EDIT 2: Using HashSets comparison

Run a comparison using hashsets and got the following outputs creating 1000 query objects and executing the into a list using ToList() gave the followign results:

Using List<Tag>

Creating Events took 6ms
Number of Tags = 3000
Number of Products = 1003
Average Number of Tags per Products = 100
Number of Search Tags = 10

.All.Contains = 5379ms - Found 51
.Intersect = 5823ms - Found 51
.Except = 6720ms - Found 51

Using HashSet<Tag>

Creating Events took 26ms
Number of Tags = 3000
Number of Products = 1003
Average Number of Tags per Products = 100
Number of Search Tags = 10

.All.Contains = 259ms - Found 51
.Intersect = 6417ms - Found 51
.Except = 7347ms - Found 51

As you can see, using HashSet is considerably faster, even if you factor in the extra 20ms to create the HashSets. ALthough the hash was a simple hash of the ID field. If you was to use a more complicated Hash results would be different.

Upvotes: 2

Related Questions