代码优先实体框架瓦特/从复杂全文搜索存储过程返回结果 [英] 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.

我目前的做法是创建一个辅助类执行全文搜索调用其中的存储步骤（S）和从根据返回的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做即使支持（作为一个例子我的一些实体通过业务逻辑相关的，我想将它们分组返回作为一个单一的结果）。该存储过程映射到DTO与实体的id和等级


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.

完整的解决方案是这样的：

The complete solution looks like:

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

结果对象：

Results object:

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

最后调用存储过程：

Finally calling the stored procedure:

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屋！