CODEWITHSUNDEEP
c#linqentity-frameworkcollections
Ingen Speciell
Ingen Speciell

Reputation: 107

Select items where collection is in another collection (linq)

I'm struggling with a linq query where I need to find items in a collection that matches an id in another collection.

Users can log in and save a property search where you can specify if you'd like to search on flats, houses, and so on, and in which regions. We then want to display a list of the users who have a search for specific regions etc.

This is the code where I'm trying to do the search:

    SearchSetting searchSetting = mySearchSetting;
    List<RegionSearch> regionSearchList = myRegionSearchList;

    var searches =
        SearchSettingsRepository.Instance.Where(s =>
            (!searchSetting.Flat || s.Flat == searchSetting.Flat) &&
            (!searchSetting.House || s.House == searchSetting.House) &&
            (!searchSetting.Castle || s.Castle == searchSetting.Castle),
            new[] { "RegionSearches" })
            .Where(s => s.RegionSearches.Intersect(regionSearchList)
                .Any())
                .ToList();

So if myRegionSearchList contains RegionA and RegionB, I'd like a list of all the users who have both regions specified in SearchSetting.RegionSearches. At the moment the above is returning zero results even though I know there are users which have both regions selected.

We're using Entity Framework database first, and we have a generic repository that all repositories inherit from, so the above search is calling this method:

public abstract class GenericRepository<TDb, TModel>
    where TDb : DbContext, new()
    where TModel : class
{
    public List<TModel> Where(Expression<Func<TModel, bool>> pred, string[] include = null)
    {
        List<TModel> items;
        using (var db = new TDb())
        {
            if (include == null)
            {
                items = db.Set<TModel>().Where(pred).ToList();
            }
            else
            {
                DbQuery<TModel> query = null;
                foreach (var inc in include)
                {
                    query = db.Set<TModel>().Include(inc);
                }

                items = query.Select(t => t).Where(pred).ToList();
            }
        }
        return items;
    }
}

Let me know if you need any more information and I'll update the question.

Upvotes: 1

Views: 3252

Answers (1)

Aducci
Aducci

Reputation: 26644

I would try the following:

  1. Combine the first and second Where predicate
  2. Replace the Intersect with an All / Any (I am assuming RegionSearch has an ID unique identifier)
  3. If you are still having issues, I would use a SQL profiler to see the generated SQL

Update

List<RegionSearch> regionSearchList = myRegionSearchList;
List<int> regionSearchListIds = regionSearchList.Select(x => x.ID).ToList();

Code sample:

SearchSettingsRepository.Instance.Where(s =>
            (!searchSetting.Flat || s.Flat == searchSetting.Flat) &&
            (!searchSetting.House || s.House == searchSetting.House) &&
            (!searchSetting.Castle || s.Castle == searchSetting.Castle) &&
            regionSearchListIds.All(r => s.RegionSearches.Any(x => x.ID == r)), 
            new[] { "RegionSearches" });

Upvotes: 1

Related Questions