The result of a query cannot be enumerated more than once when using stored procedure

Question

I am trying to replace a view with a stored procedure to search results based on a keyword. But when I pass a keyword it throws an error

The result of a query cannot be enumerated more than once

but it works fine if the keyword is left empty. Below is my method to get search results. Can anyone provide any suggestions on how to enumerate the results in this case?

public IEnumerable <BrandNameToIngredient> GetBrandNameToIngMapResults(string Keyword)
{
    IEnumerable<BrandNameToIngredient> lstBrandNametoIng = from map in DB.USP_BRANDNAME_INGREDIENT_MAP()                                                           
                      select new BrandNameToIngredient
                             {
                                 IngredientBrandNameMapID=map.INGREDIENT_PRODUCT_MAP_ID,                            
                                 BrandName = map.FDA_BRAND_NAME,             //From Table 1              
                                 PFCName = map.PFC_DESC==null?"":map.PFC_DESC,  //From Table 1                        
                                 IngredientName = map.INGREDIENT_NAME,       //From Table 2
                                 HCIngredientName = map.HC_INGREDIENT_NAME,   //From Table 2                              
                                 KeywordfromPage = Keyword
                             };

    if (!string.IsNullOrEmpty(Keyword))
    {
        lstBrandNametoIng = lstBrandNametoIng.Where(x => x.BrandName.ToLower().Contains(x.KeywordfromPage.ToLower())        //Able to get result                                                  
                                                        || x.PFCName.ToLower().Contains(x.KeywordfromPage.ToLower())            //Able to get result

                                                        || x.IngredientName.ToLower().Contains(x.KeywordfromPage.ToLower())     //Error Here
                                                        || x.HCIngredientName.ToLower().Contains(x.KeywordfromPage.ToLower())); //Error Here
    }

    return lstBrandNametoIng;
}

Why not passing the keyword to stored procedure as a parameter? — zhe, May 08 '18 at 18:38
Possible duplicate of [The result of a query cannot be enumerated more than once](https://stackoverflow.com/questions/5723555/the-result-of-a-query-cannot-be-enumerated-more-than-once) — Jonathon Chase, May 08 '18 at 18:39
If you wrap your first statement with a `ToList()` you should be good to go. ie `lstBrandNametoIng = (from ... select ...).ToList()` — Jonathon Chase, May 08 '18 at 18:41
@Jonathon I tried wrapping it with a ToList() and still have the same issue. — Programmermid, May 08 '18 at 18:56
@Programmermid instead of the wrap, try `from map in DB.USP_BRANDNAME_INGREDIENT_MAP().ToList()` — Jonathon Chase, May 08 '18 at 19:00

Harsh · Accepted Answer · 2018-05-08T21:20:19.057

Its better to first operate on the Query and then return the enumerable at last.

public IEnumerable<BrandNameToIngredient> GetBrandNameToIngMapResults(string Keyword)
{
    var brandNametoIngQuery = DB.USP_BRANDNAME_INGREDIENT_MAP()
                             .Where(x => string.IsNullOrEmpty(Keyword) 
                                        || x.BrandName.Contains(Keyword, StringComparison.OrdinalIgnoreCase)        
                                        || x.PFCName.Contains(Keyword, StringComparison.OrdinalIgnoreCase)           
                                        || x.IngredientName.Contains(Keyword, StringComparison.OrdinalIgnoreCase)     
                                        || x.HCIngredientName.Contains(Keyword, StringComparison.OrdinalIgnoreCase))
                             .Select(map=> new BrandNameToIngredient
                             {
                                  IngredientBrandNameMapID = map.INGREDIENT_PRODUCT_MAP_ID,
                                  BrandName = map.FDA_BRAND_NAME,             //From Table 1              
                                  PFCName = map.PFC_DESC == null ? "" : map.PFC_DESC,  //From Table 1                        
                                  IngredientName = map.INGREDIENT_NAME,       //From Table 2
                                  HCIngredientName = map.HC_INGREDIENT_NAME,   //From Table 2                              
                                  KeywordfromPage = Keyword
                              }).AsEnumerable();
}

Tried this but still has same issue – Programmermid May 08 '18 at 20:37 — Programmermid, May 08 '18 at 20:37

The result of a query cannot be enumerated more than once when using stored procedure

1 Answers1