Linq optimized intersect query

Question

I have a structure like that:

public class Tag
{
        public int TagId { get; set; }
        public virtual ICollection<Vacancy> Vacancies { get; set; }
        // ...
}

public class Vacancy
{
        public int VacancyId { get; set; }
        public virtual ICollection<Tag> Tags { get; set; }
        // ...
}

These entities are mapped to MS SQL with EF / Code First approach.

After that I fetch somehow from the context (based on user query) a list of tags:

List<Tag> userSelectedTags = ...;

And I want to select all the vacancies which contains all (!) of these tags, something like:

List<Vacancy> vacancies = context.Where(v => v.Tags.Intersect(userSelectedTags)).ToList();

But! The problem is that I may have huge amount of data. And AFAIK Intersect is not the best approach cos it will select all the tags for each vacancy, and then perform Intersect on 'em. I don't want to load SQL Server too much and I'm definitely can write pure sql query for that. But I wonder if LINQ can do it for me? Is there any more gentle way of doing that?

As written, your code wouldn't compile because `v.Tags.Intersect(userSelectedTags)` does not return a `bool` as is required by the `Where` method — smartcaveman, Aug 16 '12 at 20:33
try use Join, like here http://stackoverflow.com/questions/2381049/intersect-linq-query — GSerjo, Aug 16 '12 at 22:01

score 5 · Accepted Answer · answered Aug 17 '12 at 20:58

Arkiliknam's solution is pretty efficient, maybe the most efficient one, but it's got some issues. I started to point them out in a comment, to allow the answer to be improved, but that didn't work well. So here is Arkiliknam v. 2.0:

var result = context.Vacancies; // IQueryable!
var tags = userSelectedTags.Select(t => t.TagId);

foreach(int i in tags)
{
    int j = i; // prevent modified closure.
    result = result.Where(v => v.Tags.Select(t => t.TagId).Contains(j));
}
result.Select(r => ....

It is converted to query with a chain of WHERE EXISTS predicates (as many as there are selected tags).

If this does not perform well you could try another approach:

var r = context.Vacancies
.Where(v => tags.All(i => v.Tags.Select(t => t.TagId).Contains(i)))
.Select (v => ....);

which says that for all selected TagIds each Id should be in a Vacancy's collection of TagIds.

score 1 · Answer 2 · answered Aug 17 '12 at 15:48

1

Not sure if this would be more efficient or not, but I guess if you leave it as IQueryable it should be ok?

IEnumerable<Vacancy> result = vacancies;

foreach(var t in tags){
    result = result.Where(v => v.TagIds.Contains(t));
}

answered Aug 17 '12 at 15:48

Arkiliknam

1,805
1
19
35

Linq optimized intersect query

2 Answers2