Unsupported overload used for query operator 'Intersect'.

Question

I am able to do this query just fine with the test repository which is In Memory when I move to the sqlRepository I get this error Unsupported overload used for query operator 'Intersect'.

I assume it is because sending the query to sql is too complicated for Linq to Sql to do when it is not dealing with the Model.Model.Talent Type. Is there some way around doing a search like this with Intersect?

thanks

public class TalentService : ITalentService
{ 
    ITalentRepository _repository = null;

    private IQueryable<Talent> BasicSearch(string searchExpression)
    {
        IQueryable<Talent> t;
        string[] sa = searchExpression.Trim().ToLower().Replace("  ", " ").Split(' ');
        t = _repository.GetTalents();
        foreach (string s in sa)
        {
            t = t.Intersect(AddBasicSearch(s), new TalentComparer()); 
        }
        return t;
    }


    private IQueryable<Talent> AddBasicSearch(string s)
    {
        IQueryable<Talent> t2 = _repository.GetTalents()
            .Where(tal => tal.EyeColor.ToString().ToLower().Contains(s)
                          || tal.FirstName.ToLower().Contains(s)
                          || tal.LastName.ToLower().Contains(s) 
                          || tal.LanguagesString.ToLower().Contains(s) 
                          );
        return t2;
    } 
}



public class SqlTalentRepository:ITalentRepository
{
    public IQueryable<Model.Model.Talent> GetTalents()
    {
        var tal = from t in _db.Talents  
                  let tLanguage = GetTalentLanguages(t.TalentID)
                  where t.Active == true
                  select new Model.Model.Talent
                  {
                      Id = t.TalentID,
                      FirstName = t.FirstName,
                      LastName = t.LastName, 
                      TalentLanguages = new LazyList<Model.Model.TalentLanguage>(tLanguage),
                      LanguagesString = t.TalentLanguages.ToLanguageNameString(_LanguageRepository.GetLanguages()) 
                  };
        return tal ;
    }

    public IQueryable<Model.Model.TalentLanguage> GetTalentLanguages(int iTalentId)
    {
        var q = from y in this.talentLanguageList
                let Languages = _LanguageRepository.GetLanguages()
                where y.TalentId == iTalentId
                select new Model.Model.TalentLanguage
                {
                    TalentLanguageId = y.TalentLanguageId,
                    TalentId = y.TalentId,
                    LanguageId = y.LanguageId,
                    Language = Languages.Where(x => x.LanguageId == y.LanguageId).SingleOrDefault() 
                };
        return q.AsQueryable<Model.Model.TalentLanguage>();
    }

}



public static class TalentExtensions
{ 
    public static string ToLanguageNameString(this IEnumerable<TalentLanguage> source
        , IEnumerable<Model.Model.Language> allLanguages)
    {
        StringBuilder sb = new StringBuilder();
        const string del = ", ";
        foreach (TalentLanguage te in source)
        {
            sb.AppendFormat("{0}{1}", allLanguages
                .Where(x => x.LanguageId == te.LanguageID).SingleOrDefault().LanguageName, del);
        }
        string sReturn = sb.ToString();
        if (sReturn.EndsWith(del))
            sReturn = sReturn.Substring(0, sReturn.Length - del.Length);
        return sReturn;
    } 
}










public class TestTalentRepository : ITalentRepository
{ 
    IList<Talent> talentList; 

    public TestTalentRepository(ILanguageRepository _LanguageRepo )
    { 
        this._LanguageRepository = _LanguageRepo; 
        talentList = new List<Talent>(); 
        talentLanguageList = new List<TalentLanguage>(); 
        for (int i = 0; i < 55; i++)
        {
            var t = new Talent();
            t.Id = i;
            t.FirstName = (i % 3 == 0) ? "Ryan" : "Joe";
            t.LastName = (i % 2 == 0) ? "Simpson" : "Zimmerman";   
            AddLanguagesToTestTalent(i, t);
            talentList.Add(t);
        }
    }

    private void AddLanguagesToTestTalent(int i, Talent t)
    { 
        IList<Language> Languages = _LanguageRepository.GetLanguages().ToList<Language>();
        Random rLanguages = new Random();
        int numLanguages = rLanguages.Next(Languages.Count - 1) + 1;
        t.TalentLanguages = new LazyList<TalentLanguage>();
        for (int j = 0; j < numLanguages; j++)
        {
            var x = new TalentLanguage();
            x.TalentLanguageId = j;
            x.TalentId = i;
            Random random2 = new Random();
            int rand = random2.Next(Languages.Count);
            var y = Languages.ElementAtOrDefault(rand);
            Languages.RemoveAt(rand);
            x.Language = y;
            x.LanguageId = y.LanguageId;
            t.TalentLanguages.Add(x);
        }
    }


    public IQueryable<Talent> GetTalents()
    {
        var ts = from t in this.talentList 
                 let tLanguage = GetTalentLanguages(t.Id)
             where t.Active == true
             select new Model.Model.Talent
             {
                 Id = t.Id,
                 FirstName = t.FirstName,
                 LastName = t.LastName, 
                 TalentLanguages = new LazyList<Model.Model.TalentLanguage>(tLanguage),
                 LanguagesString = t.TalentLanguages.ToLanguageNameString(_LanguageRepository.GetLanguages()), 
                 City = t.City, 
             };
        return ts.AsQueryable<Model.Model.Talent>(); 
    }

 public IQueryable<Model.Model.TalentLanguage> GetTalentLanguages(int iTalentId)
    {
        var q = from y in this.talentLanguageList
                let Languages = _LanguageRepository.GetLanguages()
                where y.TalentId == iTalentId
                select new Model.Model.TalentLanguage
                {
                    TalentLanguageId = y.TalentLanguageId,
                    TalentId = y.TalentId,
                    LanguageId = y.LanguageId,
                    Language = Languages.Where(x => x.LanguageId == y.LanguageId).SingleOrDefault() 
                };
        return q.AsQueryable<Model.Model.TalentLanguage>();
    }

}

Answer 1

If you're trying to find entries which match all of those criteria, you just need multiple where clauses:

private static readonly char[] SplitDelimiters = " ".ToCharArray();

private IQueryable<Talent> BasicSearch(string search)
{
    // Just replacing "  " with " " wouldn't help with "a      b"
    string[] terms = search.Trim()
                           .ToLower()
                           .Split(SplitDelimiters, 
                                  StringSplitOptions.RemoveEmptyEntries);
    IQueryable<Talent> query = _repository.GetTalents();
    foreach (string searchTerm in terms)
    {
        query = AddBasicSearch(query, searchTerm);
    }
    return query;
}


private IQueryable<Talent> AddBasicSearch(IQueryable<Talent> query, string s)
{
    return query.Where(tal =>  
                       tal.EyeColor.ToString().ToLower().Contains(s)
                       || tal.FirstName.ToLower().Contains(s)
                       || tal.LastName.ToLower().Contains(s) 
                       || tal.LanguagesString.ToLower().Contains(s) 
                       );
}