带有存储过程的代码优先实体框架从复杂的全文搜索返回结果 [英] Code-First Entity Framework w/ Stored Procedure returning results from complex Full-text Searches

问题描述

我正在为以下场景寻找设计建议:

I am looking for design advice for the following scenario:

我有一个代码优先的 EF5 MVC 应用程序.我正在构建一个全文搜索功能，它将合并来自许多表的多个加权列.由于我无法使用这些表的索引创建视图(其中一些包含文本/二进制列)，我创建了一个存储过程，它将输出我的对象的 ID(例如.PersonID)和根据搜索词与该对象关联的排名.

I have a code-first EF5 MVC application. I am building a full-text search function which will incorporate multiple weighted columns from many tables. As I cannot create view with an index from these tables (some of them contain text / binary columns), I have created a stored procedure which will output the ID of my object (eg. PersonID) and the rank associated with that object based on the search terms.

我目前的方法是创建一个帮助类来执行全文搜索，它调用存储过程并根据返回的 ID 从上下文加载所有对象.

My current approach is to create a helper class for executing full text searches which call the stored procedure(s) and load all the objects from the context based on the returned IDs.

我的问题是:

1. 我的方法是否合理/是否遵循合理的最佳实践?
2. 有没有其他人做过类似的事情?
3. 有没有办法更有效地做到这一点(即让存储过程的结果直接返回/映射到实体，而无需额外查找?)

更新

将我的详细实现从对问题的编辑移到了自己的答案中，以更符合@meta.stackexchange.com 经常推荐的内容

Moved my detailed implementation from an edit of the question into its own answer to be more in line with what is recommended frequently @ meta.stackexchange.com

推荐答案

将此作为答案而不是对我的问题的编辑发布:

Posting this as an answer rather than an edit to my question:

从@Drauka 的(和谷歌)提供的一些见解这里是我为我的初始迭代所做的.

Taking some of the insight provided by @Drauka's (and google) here is what I did for my initial iteration.

1. 创建存储过程来进行全文搜索.即使支持，在 EF 中完成也太复杂了(例如，我的一些实体通过业务逻辑相关，我想将它们分组作为单个结果返回).存储过程映射到具有实体 ID 和等级的 DTO.
2. 我修改了这个博主的片段/代码来调用存储过程，并填充我的 DTO:http://www.lucbos.net/2012/03/calling-stored-procedure-with-entity.html

3. 我用来自存储过程结果的总计和分页信息填充我的结果对象，然后只加载当前结果页面的实体:

1. Created the stored procedure to do the full text searching. It was really too complex to be done in EF even if supported (as one example some of my entities are related via business logic and I wanted to group them returning as a single result). The stored procedure maps to a DTO with the entity id's and a Rank.
2. I modified this blogger's snippet / code to make the call to the stored procedure, and populate my DTO: http://www.lucbos.net/2012/03/calling-stored-procedure-with-entity.html

3. I populate my results object with totals and paging information from the results of the stored procedure and then just load the entities for the current page of results:

int[] projectIDs = new int[Settings.Default.ResultsPerPage];
foreach (ProjectFTS_DTO dto in 
          RankedSearchResults
          .Skip(Settings.Default.ResultsPerPage * (pageNum - 1))
          .Take(Settings.Default.ResultsPerPage)) {
             projectIDs[index] = dto.ProjectID;
             index++;
        }

IEnumerable<Project> projects = _repository.Projects
            .Where(o=>projectIDs.Contains(o.ProjectID));

全面实施:

由于这个问题收到了很多意见，我认为有必要发布更多有关我的最终解决方案的详细信息，以供其他人帮助或可能的改进.

As this question receives a lot of views I thought it may be worth while to post more details of my final solution for others help or possible improvement.

完整的解决方案如下:

DatabaseExtensions 类:

DatabaseExtensions class:

public static class DatabaseExtensions {
    public static IEnumerable<TResult> ExecuteStoredProcedure<TResult>(
             this Database database, 
             IStoredProcedure<TResult> procedure, 
             string spName) {
        var parameters = CreateSqlParametersFromProperties(procedure);
        var format = CreateSPCommand<TResult>(parameters, spName);
        return database.SqlQuery<TResult>(format, parameters.Cast<object>().ToArray());
    }

    private static List<SqlParameter> CreateSqlParametersFromProperties<TResult>
             (IStoredProcedure<TResult> procedure) {
        var procedureType = procedure.GetType();
        var propertiesOfProcedure = procedureType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

        var parameters =
            propertiesOfProcedure.Select(propertyInfo => new SqlParameter(
                    string.Format("@{0}", 
                    (object) propertyInfo.Name), 
                    propertyInfo.GetValue(procedure, new object[] {})))
                .ToList();
        return parameters;
    }

    private static string CreateSPCommand<TResult>(List<SqlParameter> parameters, string spName)
    {
        var name = typeof(TResult).Name;
        string queryString = string.Format("{0}", spName);
        parameters.ForEach(x => queryString = string.Format("{0} {1},", queryString, x.ParameterName));

        return queryString.TrimEnd(',');
    }

    public interface IStoredProcedure<TResult> {
    }
}

保存存储过程输入的类:

Class to hold stored proc inputs:

class AdvancedFTS : 
         DatabaseExtensions.IStoredProcedure<AdvancedFTSDTO> {
    public string SearchText { get; set; }
    public int MinRank { get; set; }
    public bool IncludeTitle { get; set; }
    public bool IncludeDescription { get; set; }
    public int StartYear { get; set; }
    public int EndYear { get; set; }
    public string FilterTags { get; set; }
}

结果对象:

public class ResultsFTSDTO {
    public int ID { get; set; }
    public decimal weightRank { get; set; }
}

最后调用存储过程:

public List<ResultsFTSDTO> getAdvancedFTSResults(
            string searchText, int minRank,
            bool IncludeTitle,
            bool IncludeDescription,
            int StartYear,
            int EndYear,
            string FilterTags) {

        AdvancedFTS sp = new AdvancedFTS() {
            SearchText = searchText,
            MinRank = minRank,
            IncludeTitle=IncludeTitle,
            IncludeDescription=IncludeDescription,
            StartYear=StartYear,
            EndYear = EndYear,
            FilterTags=FilterTags
        };
        IEnumerable<ResultsFTSDTO> resultSet = _context.Database.ExecuteStoredProcedure(sp, "ResultsAdvancedFTS");
        return resultSet.ToList();

    }

这篇关于带有存储过程的代码优先实体框架从复杂的全文搜索返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！