使用存储过程时，不能多次枚举查询的结果

Question

我正在尝试用存储过程替换视图，以根据关键字搜索结果。但是当我传递关键字时，它会抛出错误

查询结果不能枚举多次

但是如果关键字留空，它可以正常工作。以下是我获取搜索结果的方法。在这种情况下，任何人都可以就如何枚举结果提供任何建议吗？

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;
}

Answer 1

最好先对查询进行操作，然后最后返回可枚举。

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();
}