Home » .Net Framework

Entity framework doens't show my stored procedure on "Update model from database"


Using VS 2010 and sql server 2005 express edition, i have created an EF model and imported some table into it using the wizard.

I have written a stored procedure in the same database and opened the  "Update model from database" wizard - but for some reason, my SP is not there.

I have restarted VS and SQL server, and it didn't do the trick.

finally, i downloaded anklab sql profiler and started a trace, found the query the EF wizard is running to get the SP names from the DB - and when I run it in the sql server management studio express - I can see my SP name!

I don't really know what i'm doing wrong. can someone please help me?




3 Answers Found


Answer 1

Hi amazingwolf,

Entity framework  will not show  your stored  procedure untill you map it to one of your database  entities or custom entities.

To do this, go to the model  browser, select your stored procedure-->right click-->Create function import and select one of the entities as the return types.

Save it and then you will be able to do something like this in your code:

ObjectResult<EntityName> entityRecords = objectContext.StoredProcedure(param1, param2);





Answer 2

Hi Kumar,


I opened the model  browser, but under "Stored procedures" there is no SP.

I have tried to click"Function Imports" and search for my SP there - but the drop down of "stored procedure  name" does not contain it.


What is the way to "tell" the model about my SP?...

I have googled this, but no luck yet.


Thank you for your help!


Answer 3

Found the problem - db user did not have execute permissions on stored  procedure...

Thanks Kumar!



For many years, I am so used to build DAL with custom stored procedure and writing mapping code in C#. I found this approach yeilds better performance, because of the use of stored procedure, fact that we can do more work in database -inside database and in a stored procedure - we also have full control on SQL used.

Now I am using Entity Framework. I am using it in a simple way: by directly dropping tables and using LINQ to Entities etc to do CRUD. While this approach is very easy and working well for me in creating personal websites quickly with DAL support, I am not sure if this is good for Enterprise applications where perforamnce is key.

So, I am looking for clear pointers from ADO.NET Entity Framework Team on following:

Q1. Can you point me to good examples of building Entity Model with purely by Stored procedure support. I know how to use Stored procedures with Entity Framework. But I like the easy way to create Entities by drag and drop of tables [than creating them by hand]. How Can I merge these two approaches so that:
             - I create entities by dragging tables from database
             - But when I use CRUD on tables/entities, they internally use Stored procedures?

Q2. What are performance details and recommendations: Comparing POCO with DataReader/Stored procedure kind of manual mapping VS. Using Entity Framework.

I am not looking for basic Entity Framework documentation but interested more in how you recommend building perforamnce sensitive services, using Entity Framework and stored procedures.

Q3. Is it old fashion approach to use stored procedures. Is new development guidelines are recommending not to use stored procedures anymore and use Entities and LINQ to Entities directly with Entity Framework? Am I wrong in believing that Stored procedure approach is always good as we don't allow direct access to any TABLES to end users?

Q4. For a long time I am strong believer of "Don't allow direct access to TABLES to any users and all access is done ONLY through stored procedures". I understand we are not database agnostic this way. But this is more secured approach and also yeilded better performance. What is your input and guidelines in this area?


Hi, I have a issue, i am calling stored procedure through entity model like result=ctx.spname(parmas), i am expecting 0 or 1 as a result after the execution of sp, but its returning unknown values like 178 for number records. Please suggest what went wrong Thanks


In my Entity Framework v4 project, I have a table with two columns that are computed by the database (via triggers, etc.). In order to get EF to properly insert records into the table, I have to manually mark the columns as "Computed" in the EF Storage Model (the StoreGeneratedPattern attribute), which is not supported by the designer -- so I have to make the edits by hand to the XML in the .EDMX file.

The problem is that whenever my database schema changes, and I need to update the storage model in my project, the "Update Model Wizard" overwrites the whole Storage Model section of the .EDMX, eliminating my manual changes. This means that I have to keep a special list of such changes and manually re-apply them EVERY TIME I do an update! For the love of god, this has got to be one of the dumbest glitches in EF.

Anyway, my question is this... Is there any way to prevent the Storage Model overwrites? Is there a way to flag the columns in the database, so that EF will automatically mark them as computed? As a last resort, is the some REALLY EASY XML tool/technique that can automatically apply the changes for me after every refresh?

Thank you in advance for any help.




I am using stored procedures returns columns from pivot table which is generated dynamically ie., Code from Stored prodecure: Execute (@PivotTableSQL). @PivotTableSQL dynamically generate select statement by using pivot. So the resultset columns cannot be identified in Add Function import window. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance.


How can i automatically generate  stored procedures using Ado.Net  ENtity Data Model 3.5

I am trying to activate a stored procedure in Silverlight using a Data Service in Visual Basic.  I have pieced together information from various posts on how to receive data from a stored procedure.  My data base is connected with an Entity Data Model, the connections are in place, and can query the database with simple LINQ statements.  Due to the complexity of my queries I need to run a stored procedure which I setup in the services file.  The last piece is calling the connection in the services to activate the procedure.  Every sample I have seen does not work or returns "Object reference not set to an instance of an object"  Can someone offer the last piece to call the stored procedure.....

Imports System
Imports System.Collections.Generic
Imports System.Data.Services
Imports System.Data.Objects
Imports System.Web
Imports System.Linq
Imports System.ServiceModel.Web
Imports System.Data.Services.Common
Imports Insider_DBModel

Public Class InsiderTradingDataService
    ' TODO: replace [[class name]] with your data class name
    Inherits DataService(Of Insider_DBModel.Insider_DBEntities1)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(ByVal config As DataServiceConfiguration)
        config.SetEntitySetAccessRule("*", EntitySetRights.All)
        config.SetServiceOperationAccessRule("*", ServiceOperationRights.All)
        config.SetServiceOperationAccessRule("Symbols", ServiceOperationRights.All)
    End Sub

    Public Function Symbols(ByVal Lastname As String) As IQueryable(Of Index)
        Dim entities As New Insider_DBEntities1
        Return entities.Symbols(Lastname).AsQueryable

    End Function

End Class

 Private currentResult As IAsyncResult
    Private Sub getInsideData_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
        ' Instantiate the data service context and clear any existing bindings.

        ' Define the delegate to callback into the process
        Dim callback As AsyncCallback = AddressOf OnInsiderQueryComplete


            svcContext.BeginExecute(Of IEnumerable(Of Index))(New Uri("http://localhost:50702/SLTest.Web/InsiderTradingDataService.svc/Symbols?LastName='DJI'"), callback, Nothing)

        Catch ex As Exception
            ErrorMsg.Text = ex.Message
        End Try
    End Sub

    Private Sub OnInsiderQueryComplete(ByVal result As IAsyncResult)

            ' Cache the query result for the delegate.
            currentResult = result

            ' Use the Dispatcher to ensure that the
            ' asynchronous call returns in the correct thread.
            Dispatcher.BeginInvoke(AddressOf QueryCompletedByDispatcher)
        Catch ex As Exception
            ErrorMsg.Text = ex.Message
        End Try

    End Sub

    Private Sub QueryCompletedByDispatcher()
        Dim response As IEnumerable(Of Index) = Nothing

        ' Define the delegate to callback into the process
        Dim callback As AsyncCallback = AddressOf OnInsiderQueryComplete


            If Not Inside_IndexToken Is Nothing Then
                svcContext = CType(currentResult.AsyncState, Insider_DBEntities1)
                response = svcContext.EndExecute(Of Index)(currentResult)
                Dim query = CType(currentResult.AsyncState, DataServiceQuery(Of Index))
                response = query.EndExecute(currentResult)
            End If

            For Each o As InsiderTradingService.Index In response

            Inside_IndexToken = CType(response, QueryOperationResponse(Of Index)).GetContinuation()

            If Not Inside_IndexToken Is Nothing Then
                svcContext.BeginExecute(Of Index)(Inside_IndexToken, callback, svcContext)
                Me.DataGrid1.DataContext = Inside_IndexCollection
                Me.DataGrid1.ItemsSource = Inside_IndexCollection

                ' Select the first order, if any orders exist.
                If Inside_IndexCollection.Count > 0 Then
                    Me.DataGrid1.SelectedIndex = 0
                End If
            End If
        Catch ex As Exception
             ErrorMsg.Text = ex.Message
        End Try
    End Sub




I am new to entity framwork , it is really very good , but I want to know what is the differance between using entity framwork with stored procedure or without stored procedure , which one the faster and what is the benifits for using stored procedures with entity framwork


Currently Using Visual Studio 2010, ADO Entity Model, Domain Services

Can someone help me with the code to get my stored procedure into the context and correctly calling the stored procedure in code for the datagrid?  I am currently getting an error that I must specify a key.


Function GetContracGenInfo() AsIQueryable(OfStoredProcedure_Result)
Dim db AsNewDomainService1()
Dim matchingContracts = db.GetContracGenInfo
Return matchingContracts.OrderBy(Function(e) e.Contract)

I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.


EF does handle stored procedures with OUTPUT parameters, but the generated methods are not the easiest to work with or safest you could ask for. The ObjectParameter objects you have to create, pass to the method and extract the value from are not type checked and really

ObjectParameter nameParam = new ObjectParameter( "Name", typeof(string));
ObjectParameter ageParam = new ObjectParameter( "Age", typeof(int));
context.GetPersonsNameAndAge( personId, nameParam, ageParam);
string name = Convert.IsDBNull(nameParam.Value) ? null : (string)nameParam.Value;
int? age = Convert.IsDBNull(ageParam.Value) ? null : (int?)ageParam.Value;

is not what I would want to have to write.

The ObjectParameter objects do make some sense for stored procedures that return both a resultset and some OUTPUT parameters, because in that case the OUTPUT parameters are only available after the resultset has been consumed and therefore you need some objects that will be populated after the ObjectResult<...> gets processed but for the stored procedures that do not return any resultset, there is no reason not to create the ObjectParameter objects within the method and extract the values there as well. So that the procedure above may be called as

string name;
int? age;
context.GetPersonsNameAndAge( personId, out name, out age);

Things are not so simple for procedures returning both. I'm considering this interface:

var results = context.FetchSomeDataAndOutParams(in1, in2);
List<Things> = results.Rows.ToList();
int? out1 = results.Out1;
string out2 = results.Out2;

That is the method would return an object that would contain the ObjectResult<Things> as property named "Row" (or "rows" or ...?) and a typed property for each OUTPUT parameter that would read the value from the ObjectParameter object that was passed to base.ExecuteFunction(...). And no typecasts in hand made code.

There would of course have to be a separate class for each such stored procedure, but I don't think that's a huge deal.

What do you think? Would you like this interface more than the default one? Would you suggest a different one?

I plan to make the modified template public once finished!



Im using Entity framework with POCO entity generator template + SQL Server. I have mapping to stored procedure that takes two floats as input.

After executing function from code with two doubles as parameters: 1.23 and 4.56, SQL Profiler shows:

exec storedProcedureName @arg1 = 1,23, @arg2= 4,56

This is causing an error because it looks like that stored procedure takes 4 arguments, but it should take only two and there should be dots insted of commas in these floats.

My SQL Server collation is SQL_Latin1_General_CP1_CI_AS. I tried to set:

           Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
           Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;

in DataContext Constructor, and directly before ExecuteFunction<>(..) of Entity Framework DataContext object, both with no result.

Im confused, please give me some advice.


Hi there,

I have a stored procedure called 'GetPrices' with a Table-Valued Parameter called 'StoreIDs' and I would like to call it from my Entity Framework. But when I try to add the Stored Procedure to the EDM, i get the following error:

The function 'GetPrices' has a parameter 'StoreIDs' at parameter index 2 that has a data type 'table type' which is not supported. The function was excluded.

Is there any workaround this? Any thoughts?




Please let me know how can I get Selected Columns value from Stored procedure into Bussiness Object in Entity Framework 4.0.

I want to return selected value without create custom entity and assign that values into bussiness object.




I Can Not Get Result From Stored Procedures With Entity Framework on .net 3.5

i think that entity framework dont like stored procedures .





I am fairly new to EF4.0/LINQ and have been struggling getting working SQL functionality ported over to the EF way. The end to end scenario is I am trying to achieve is to bind GridView results to a Full Text search to get Google/Bing style search results from a DB table. 

In the previous working system I had a SQLDataSource with a custom SELECT query.

SELECT * FROM [Table] WHERE (FREETEXT (([Col1],[Col2]), @searchTxt)),



which worked decent enough - but of course didn't have Google/Bing syntax/results. Now I am trying to get this to work in EF using an EntityDataSource

    <asp:EntityDataSource ID="EntityDataSource" runat="server"
            <asp:ControlParameter ControlID="srchBox" Name="searchValue"
                PropertyName="Text"  Type="String" />

This is after creating a stored proc version of http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx. EF4.0 doesn't seem to support TVF's? I imported my stored proc and added the function reference, but I can't seem to reference it from the EntityDataSource. The post below alluded to this perhaps being addressed in a future release, but gave no definitive answer.

What am I missing, a helper LINQ class to call the Entity Function? Does anybody have a end-to-end solution using EF which implements Google/Bing style searching against a full text indexed table columns, and binds the results to a grid?

Many thanks, newbie EF user!

Ref: http://stackoverflow.com/questions/717740/use-stored-procedures-on-the-entity-data-source-possible


Are there any good examples out there of the Entity Framework handling a stored procedure that returns multiple result sets? 

On a posting at stackoverflow, mention is made of the EF Extensions (all in C#, none in VB for the VS 2010 version).



Hi have an entity(table in sql) called scheduledtask

I have stored procedure which returns my entity type result.

I want to modify these result set and insert into my table.

Altough my result set is same type as my entity it does not letme


using (OnsiteSchedulerV3TestEntities db = newOnsiteSchedulerV3TestEntities())



ObjectResult<ScheduledTaskData> std =


"", "", "", DateTime.Now);



(ScheduledTaskData s in std)


db.AddToScheduledTaskData(s);tis line cause an error because entity state is unchanged but I can not change it because it is read only.

I tried to create new entity and set all parameters one by one and add that new entity than I can add that entities by save changes causes error saying entity keys are not unique



Could you please help me I have set primary keys because it did not let me to add before I set primary keys.

I can not understand if I have duplicate object in my entity or is it because I am trying to insert from a stored procedure result.


Reside Ozsoy

Software Developer


I've read all the MSDN documentation and the blogs and there seems to be some sort of disconnect between EF entities that are not table-mapped or mapped to CRUD procs individually.

I followed this example (http://blogs.msdn.com/b/efdesign/archive/2008/07/18/using-stored-procedures-to-get-load-structured-data.aspx) but the Domain Service wizard doesn't seem to want to show complex types, it will only show entities. If use an entity instead of the complex type, I get validation errors about it not being mapped and I think this is preventing it from showing up in the wizard. I have a single procedure that is returning a single batch and it takes a few parameters. I have imported that procedure into the store model and then the conceptual model fine. I also set the return type on the conceptual function import to the entity, but it still fails to validate.

How the heck am I supposed to use a single read-only procedure to return entities? This doesn't seem like a really hard task, yet EF invariably seems unable to do the simplest task.


Is it possible to generate SQL server CE and SQL server database using the same EDMX model. There is a SQL script generation option which gives us a SQL which can be run on both the DBs, but I am not sure if the EDMX model is specific to a single type of database.

Thanks for your help.

Ganesh Ranganathan

Hi ,

I'm currently trying to evaluate if entity framework may be used as the base tool for our main application.

We've generated the metadata files corresponding to our database structure and built a class library based on those files to integrate house rules via partial classes.

A client application is suppose to reference the class library to manage the UI.

This was working fine until we built the class library on our whole database (1000 tables)  when running the client application, it seems to reparse the model (metadata file) and last for hours (event if only 2 or 3 entities ).

Is it a standard behaviour ? I've read that performance may be bad on large model at design time, but in this case it's at runtime. Is the spliting of the model the only solution (which clearly can not be easily done on our case).

Thanks in advance for your answers.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure