locked
Where clause filter in foreach loop replaces each filter rather than further filtering RRS feed

  • Question

  • Hi everyone,

    I am working with LINQ to EF 4.5, and have a search in which I need to filter the records based on a list of search terms.  Each search term must exist in the searchable fields of any single record for it to be returned with the search results.

    Currently, I take the search string, split it into a List<String> variable, pass that into my business layer, hence into the data layer, and execute the following snippet of LINQ (please forgive any typos - it isn't letting me paste in the code):

    List<String> listOfSearchTerms = new List<String>();
    
    listOfSearchTerms = searchTerms.GenericSearchString;
    
    IQueryable<Entity.Catalog.ServiceOffering> searchResults = 
    from so in context.ServiceOfferings select so;
    
    foreach (String term in listOfSearchTerms)  {
    tempSearchResults = searchResults.Where(so => so.Name.Contains(term) || 
    so.LongDescription.Contains(term) || 
    so.ShortDescription.Contains(term));   
    }
    
    List<ServiceOffering> results = tempSearchResults.ToList();

    As of this point, the Linq query returns the results of the query using only the last search term in the listOfSearchTerms. Do any of you know why this wouldn't work? How would you change the code to make it return a filtered version based on each iteration, rather than only using the last term?  Essentially it would be a 'Where x .... and Where y ....', etc.

    Thank you!

    Tuesday, October 16, 2012 2:57 PM

Answers

  • Hi,

    tempSearchResults=searchResults.Where just take the SAME UNCHANGED query to produce a new one each time so you get only the last one you created with its unique where clause.

    Instead the pattern you saw and likely try to reproduce is  :

    tempSearchResults=tempSearchResults.Where etc... that is you alter a query but the resulting query is assigned to the SAME variable so that you'll keep working on the query you just altered on the next iteration, actually chaining where clauses...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".




    Tuesday, October 16, 2012 4:51 PM
  • Hi Laura;

    This query should give the results you are looking for.

    List<String> listOfSearchTerms = new List<String>();
    listOfSearchTerms = searchTerms.GenericSearchString;
    
    List<ServiceOffering> results = 
        (from so in context.ServiceOfferings
         where listOfSearchTerms.Contains(so.Name) || listOfSearchTerms.Contains(so.LongDescription) || listOfSearchTerms.Contains(so.ShortDescription)
         select so).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, October 16, 2012 6:57 PM

All replies

  • Try putting a Select, SelectMany, or TakeWhile in the chain.

    http://msdn.microsoft.com/en-us/library/system.linq.enumerable.selectmany.aspx

    http://msdn.microsoft.com/en-us/library/system.linq.enumerable.takewhile.aspx

    Tuesday, October 16, 2012 4:33 PM
  • Hi,

    tempSearchResults=searchResults.Where just take the SAME UNCHANGED query to produce a new one each time so you get only the last one you created with its unique where clause.

    Instead the pattern you saw and likely try to reproduce is  :

    tempSearchResults=tempSearchResults.Where etc... that is you alter a query but the resulting query is assigned to the SAME variable so that you'll keep working on the query you just altered on the next iteration, actually chaining where clauses...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".




    Tuesday, October 16, 2012 4:51 PM
  • Hi Laura;

    This query should give the results you are looking for.

    List<String> listOfSearchTerms = new List<String>();
    listOfSearchTerms = searchTerms.GenericSearchString;
    
    List<ServiceOffering> results = 
        (from so in context.ServiceOfferings
         where listOfSearchTerms.Contains(so.Name) || listOfSearchTerms.Contains(so.LongDescription) || listOfSearchTerms.Contains(so.ShortDescription)
         select so).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, October 16, 2012 6:57 PM
  • Fernando - your solution is very similar to the solution I came up with right around the time you posted the answer.  My query is:

    ------------------------------

    Parameter passed into the method:

    List<String> searchTerms

    ------------------------------

    Code:

    List<ServiceOffering> searchResultList = searchResults.ToList

    //where searchResults is an IQueryable<ServiceOffering> representing the full result set

    List<ServiceOffering> query = (from so in searchResultList where searchTerms.All(term=> so.Name.Contains(term) || so.ShortDescription.Contains(term)....);

    -----------------------------------------------------------------

    The reason I did it this way was so that I can pull back records where the search term is only part of the field, not the entire contents of the field.

    Thanks again to everyone who helped!

    

    Wednesday, October 17, 2012 5:40 PM
  • Hi Laura;

    The reason for my query format was because the question had the following statement, "Each search term must exist in the searchable fields of any single record for it to be returned with the search results."

    Have a nice day.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, October 17, 2012 5:56 PM