I am looking for design advice for the following scenario:
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.
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.
My questions are:
- Does my approach seem sensible / follow reasonable best practice?
- Has anyone else done something similar with any lessons learned?
- Is there a way to do this more efficiently (i.e. have the results of the stored procedure return/map to the entities directly without an additional look-up required?)
UPDATE
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
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…