Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

I am able to do this query just fine with the test repository which is In Memory when I move to the SQL repository 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)

1 Answer

0 votes
by (40.7k points)

Refer to this video, if you want to learn INTERSECT  in detail.

Here, you have to use multiple WHERE clauses to find the entries which match all the criteria:

Example:

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) 

                       );

}

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jan 2, 2021 in SQL by Appu (6.1k points)
0 votes
1 answer
asked May 7, 2020 in SQL by Sudhir_1997 (55.6k points)

Browse Categories

...