如何将多个查询与EntityFramework交叉进行搜索

本文关键字：搜索 EntityFramework 查询 | 更新日期: 2023-09-27 18:00:23

当人们搜索我的网站时，我不想只搜索值（q），我想用空格作为分隔符来搜索每个单词。我已经编了大部分代码，但有些部分我不知道如何做。

你能检查一下下面代码中的"todo"并建议我一种方法吗？或者可能是完全不同的方法。顺便说一句，如果可能的话，我想保留SQL代码部分，因为这对我来说更自然，但所有的TODO都可以在LINQ中完成。

感谢

这是代码：

[HttpPost]
        public ActionResult Search(string q)
        {
            ViewBag.q = q;
            String[] strQueries = q.Split(' ');
            //TODO: Create an array of type var???
            foreach (string str in strQueries)
            {
                var recipesTemp = db.Recipes.SqlQuery(
                String.Format(
                "SELECT * FROM Recipe WHERE Name LIKE '%{0}%' " +
                "UNION ALL " +
                "SELECT * FROM Recipe WHERE IDRecipe IN ( " +
                "    SELECT IDRecipe FROM Subtitle WHERE Name LIKE '%{0}%') " +
                "UNION ALL  " +
                "SELECT * FROM Recipe WHERE IDRecipe IN ( " +
                "    SELECT IDRecipe FROM RecipeTag " +
                "        INNER JOIN Tag ON Tag.IDTag = RecipeTag.IDTag  " +
                "    WHERE Name LIKE '%{0}%') " +
                "UNION ALL   " +
                "SELECT * FROM Recipe WHERE IDRecipe IN ( " +
                "    SELECT IDRecipe FROM Subtitle " +
                "        INNER JOIN Ingredient ON Ingredient.IDSubtitle = Subtitle.IDSubtitle  " +
                "    WHERE QuantityAndName LIKE '%{0}%')", str)).Distinct().OrderBy(r => r.Name).ToList();
                //TODO: Add recipesTemp to the array of var
            }
            var recipes = //TODO: INTERSECT the results from all the recipesTemp in the array of type var
            return View("Search", recipes);
        }

如何将多个查询与EntityFramework交叉进行搜索

以下代码将完成任务：

        var selectedRecipies = new List<IEnumerable<Recipy>>();
        foreach(...)
        {
            ...
            selectedRecipies.Add(recipesTemp);
        }
        var recipies = selectedRecipies.Aggregate((a, i) => a.Intersect(i));

此外，在你的位置，我也会考虑@KirkWoll的评论，并使用FullTextSearch而不是LIKEs。

对于strQueries中的每个str，您将创建一个按名称排序并存储在List<T>中的不同格式的SQL查询，其中T是Recipe（？）。试试这样的东西：

var allRecipes = strQueries.Select(str => db.Recipes.SqlQuery(
    String.Format(
        "SELECT * FROM Recipe WHERE Name LIKE '%{0}%' " +
        "UNION ALL " +
        "SELECT * FROM Recipe WHERE IDRecipe IN ( " +
        "    SELECT IDRecipe FROM Subtitle WHERE Name LIKE '%{0}%') " +
        "UNION ALL  " +
        "SELECT * FROM Recipe WHERE IDRecipe IN ( " +
        "    SELECT IDRecipe FROM RecipeTag " +
        "        INNER JOIN Tag ON Tag.IDTag = RecipeTag.IDTag  " +
        "    WHERE Name LIKE '%{0}%') " +
        "UNION ALL   " +
        "SELECT * FROM Recipe WHERE IDRecipe IN ( " +
        "    SELECT IDRecipe FROM Subtitle " +
        "        INNER JOIN Ingredient ON Ingredient.IDSubtitle = Subtitle.IDSubtitle  " +
        "    WHERE QuantityAndName LIKE '%{0}%')", str)).Distinct().OrderBy(recipe => recipe.Name))
    .SelectMany(recipe => recipe);

之后，allRecipes应该属于IEnumerable<Recipe>类型。

尝试这种方法，它将产生一个SQL语句：

var names = q.Split(' ');
if(names.Any())
{
    var firstName = names[0];
    var query = context.Recipes.Where (q => q.Name.Contains(firstName));
    // other unions ...
    // query = query.Union( context.Recipes.Where (...
    foreach (var name in names.Skip(1))
    {
        query = query.Union(context.Recipes.Where (q => q.Name.Contains(name)));
        // other unions ...
        // query = query.Union( context.Recipes.Where (...
    }
    // query evaluated now
    var recipes = query.ToList();
}
相关文章：