Stored procedure search using entity framework

Question

Hi everyone.

I want to implement a search logic that I have seen and use. It works like this:

There is stored procedure that is loaded in entity framework and it is used as a method in C#, it takes parameters in order to perform search. That method is returning list of views (DB View). The views in list have just some of the properties that are needed to be shown in the gird. For example I am searching for users by some parameters and result is list of users. Instead of returning list of User object I am using a vUser view that contains only properties that will be listed on gird. For example ID, Name, and Surname. But still data is correct because stored procedure search is done against table Users in database.

View is also loaded as C# object in entity framework. I am using SQL Server 2005.

How I can implement this?

thank you very much.

Answer 1

Yes you can do this.

Unfortunately in EF 3.5 you need to create an EntityType (that has just the columns you project in the sproc) so that you can create a FunctionImport that exposes the results of the SPROC.

So steps:

1. Make sure you include the Store Proc when creating the model (orwhen refreshing the model from the database)
2. Create an EntityType that exactly matches the shape returned by the SPROC
3. Create a FunctionImport that using that Sproc and say it returns the EntityType you created in (2)

In EF 4.0 you can skip step (2) because the EF tooling automates this process.

See this post from Julie for more background

Hope this helps

Alex

Answer 2

This is not answer just extension of the question.

In order to do all the job in entity framework layer I must have valid store procedure that does search of 'users' in this case.

I did some search on Google and spot that there are two ways to return data with store procedure. One with out parameter and one with select inside brackets @parameter( SELECT goes here )

What is the best way to write store procedure that can be used as source for import function in entity framework that will return view that contains only some attributes of the user. For example Id, Name, Surname, Phone and Address because they will be shown in a grid as a result of search.

Thanks a lot everyone sorry but this is new for me