Entity Framework Code First using a Stored Procedure for a pseudo index?

Question

Until we completely figure out our full text search strategy, we have come up with a reasonable solution to show our Business owners how the functionality will work. With that said, we are using Entity Framework 4.1 and the code first approach. We have created a stored procedure to pull back results in a standard format. Here is an example of the search result class:

public class SearchResult
{
     public Guid EntityId {get;set;}
     public string DisplayText {get;set;}
     public string Icon {get;set;}

}

Now, no data actaully will go into a table for SearchResults. The idea is we would search multiple entities and build the results using that structure. The SP looks similar to this:

CREATE PROCEDURE dbo.Search(
@term varchar(200),
@topSelect int = 5,
@isPrefix BIT = 0,

)
AS
BEGIN
  Declare @SearchTerm varchar(250)
    If (isNull(@isPrefix,0) = 0)
        begin  
            Set @SearchTerm = '%' + @term + '%'
        end
    Else
        begin
            Set @SearchTerm = @term + '%'
        end

select top (@topSelect) 'Project' as 'Controller',
       [Id],
       Name as 'DisplayText',
       'PROJECTICON'       as Icon
from Projects
where Name like @SearchTerm
UNION
select top (@topSelect) 'Rfi' as 'Controller',
       [Id],
       [Number] + ' - ' + [Number] as 'DisplayText',
       'RFIICON'       as Icon
from Rfis
where (Subject like @SearchTerm or
      Description like @SearchTerm or
      Number like @SearchTerm)
End

Can I call the stored procedure with EF and fill the SearchResult "entity" with the results from the SP or do I need to do it without EF?

The idea is the same as using an index, but we are searching the live data. Again, this is a temporary solution for our needs until we have fleshed out what FTS solution we want to use.

Update:

After some research I think this may be the behavior...I will report back.

score 0 · Accepted Answer · edited May 23 '17 at 09:59

As long as you have the same column names as in the entity everything maps just super-duper! Check this out for more info on using an SP with EF. One thing to keep in mind is if you come across this error: "The SqlParameter is already contained by another SqlParameterCollection", make sure to use something like ToList() on the collection or the query will try to run again if you try to use it. At least, that is what I found in my application.

Entity Framework Code First using a Stored Procedure for a pseudo index?

1 Answers1