Entity-framework – call a Stored Procedure from an EntityDataSource


(ASP.net 3.5, C#, SQL Express 2008)

I want to have a Web Form, where I can search for "Solutions" (my table) using a keyword entered by the user and after the result comes back and populates a GridView.

I already have parts of this search via a stored procedure that is hooked up to my Entity Data Model. On this page I have an EntityDataSource. How can I make this EntityDataSource grab data from my stored procedure?

I realize I could just fetch the result via the Entity context (which works), and bind it to the grid, but if I don't hook it up to the EntityDataSource I won't get automatic paging and sorting (which has been another struggle of mine in the past)

Best Solution

Try using the Function Import.

  1. Right click on the EntitySet Name (the heading part)
  2. Choose Add->Function Import

Here is a good blog post for you to check out. ADO.NET Entity Framework Tools: Stored Procedures, by Guy Burstein

Update: Sorry I missed the part about the EntityDataSource so I don't know of any property exposed to access a function import from the EDS, but your can try to use the CommandText property.

<asp:EntityDataSource ID="SolutionsDataSource" runat="server" 
        <asp:ControlParameter Name="Keywords" 
            ControlID="SearchTextbox" Type="String"/>

Update: Well it seems that I have some bad news. After using Reflector to dive deep into the EntityDataSource. The EntityDataSourceView is constructed using QueryBuilderUtils.ConstructQuery, which then in turn calls context.CreateQuery<T>. What you would need to execute the function import is a call to context.ExecuteFunction<T>. There doesn't seem to be any support for the ExecuteFunction in this release, the blogs I was reading did mention that it was planned, but it didn't make it into this release, whether or not it will be in future releases I can't say.

That being said I would recommend using an ObjectDataSource, which you can construct in a way that still supports paging, sorting, etc. If you open an ObjectDataSource question on this topic send me a comment here and I'll take a look.