Why my stored procedures doesn't show up for selection when adding ADO.NET Entity Data Model to my ASP.net MVC project

I'm following this article to create the entityframework model, https://www.asp.net/mvc/overview/getting-started/database-first-development/creating-the-web-application 

I see all the tables for selection and even the diagram I created shows up.  However, when I expanded the Stored Procedures, I see only system spXXXXXXX  procedure for selection.  I created 4 procedures, 2 with sp prefix and 2 with usp (the sp was added to see maybe it only looks for that) but none of my own SP shows up for selection.  How can I fix this?  

If I don't have my SP included in the ADO.NET Entity Data Model then how can I reference and call my SP in the code?  Thank you.

Thank you.
lapuccaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Miguel OzSoftware EngineerCommented:
Could you specify which SQL server/EF/VS version are  you using?
I used VS2013/EF6 to grab some tables and store procedures from AdventureWorks2012 database as shown below:
EF6
In your context class the store procedures are generated as method, for example uspUpdateEmployeeLogin is shown as:
public virtual int uspUpdateEmployeeLogin(Nullable<int> businessEntityID, string loginID, string jobTitle, Nullable<System.DateTime> hireDate, Nullable<bool> currentFlag)
 {
     var businessEntityIDParameter = businessEntityID.HasValue ?
         new ObjectParameter("BusinessEntityID", businessEntityID) :
         new ObjectParameter("BusinessEntityID", typeof(int));
 
     var loginIDParameter = loginID != null ?
         new ObjectParameter("LoginID", loginID) :
         new ObjectParameter("LoginID", typeof(string));
 
     var jobTitleParameter = jobTitle != null ?
         new ObjectParameter("JobTitle", jobTitle) :
         new ObjectParameter("JobTitle", typeof(string));
 
     var hireDateParameter = hireDate.HasValue ?
         new ObjectParameter("HireDate", hireDate) :
         new ObjectParameter("HireDate", typeof(System.DateTime));
 
     var currentFlagParameter = currentFlag.HasValue ?
         new ObjectParameter("CurrentFlag", currentFlag) :
         new ObjectParameter("CurrentFlag", typeof(bool));
 
     return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("uspUpdateEmployeeLogin", businessEntityIDParameter, loginIDParameter, jobTitleParameter, hireDateParameter, currentFlagParameter);
 }

Open in new window


The example use SQL server express but it should be the same.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lapuccaAuthor Commented:
Hi, I'm using SQL SERVER 2012.  This runs locally on my development PC. I amn using VS2013/EF6 but I don't know why I can't see my SP that I created for this database but I do see them in ssms.  I would need to included them in EF model so I can call these SP, right?

Microsoft SQL Server Management Studio                                    11.0.5058.0
Microsoft Analysis Services Client Tools                                    11.0.5058.0
Microsoft Data Access Components (MDAC)                                    6.1.7601.17514
Microsoft MSXML                                    3.0 6.0
Microsoft Internet Explorer                                    9.11.9600.17801
Microsoft .NET Framework                                    4.0.30319.0
Operating System                                    6.1.7601
Miguel OzSoftware EngineerCommented:
I am using EF6/VS 2013/.NET 4.5. From memory, the EF tools will not run properly in  .NET 4.0.30319.0, but thye run OK in /.NET 4.5.
Any particular reason to use .NET 4.0?
A quick workaround for me (to retrofit in VS2010 .NET 4.0 project) was to generate all store procs in  a VS2013/.NET 4.5  class library project and then manually copy them over to my .NET 4.0 project.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

lapuccaAuthor Commented:
Woe, I didn't even notice the .net is only 4.0.  I thought when I install VS2013 it would automatically upgrade me to .net 4.5.  i didn't know I have to update .net to 4.5 myself separately.  Can I do that now or is it too late?
Thank you.
lapuccaAuthor Commented:
Wait, I just realize something.  the SSMS shows .net 4.0 but my VS2013, About MS VS2013 is showing .net 4.6(please see attached).  So is this the problem?

How can I upgrade my mssql / SSMS to use .net 4.6?  Thank you.
VS2013.txt
Miguel OzSoftware EngineerCommented:
Could you post your project properties and the EF exact version?
We need to check that your project properties target framework is 4.5.1/4.6? You can check in yourVS UI properties or inside  the csproj file, in my case I have
<TargetFrameworkVersion>v4.5.1</TargetFrameworkVersion>

Open in new window

lapuccaAuthor Commented:
I started fresh and created this web application following this link and created the Database first model but it still won't show me my stored procedures after I setup the db connection.
https://www.asp.net/mvc/overview/getting-started/database-first-development/creating-the-web-application

Project property targets .net 4.5, screen shot attached.
Thank you.
project-property.jpg
lapuccaAuthor Commented:
In my Nuget manager, EF doesn't show under Installed.  I followed this link to create the data model by selecting the  ADO.NET Entity Data Model option, the EF Designer then shows up for selection.  I didn't have to use the NuGet to install EF for this project.  But, it still won't display my stored procedures.
Miguel OzSoftware EngineerCommented:
You need to use NUGET to get the latest EF, the current version is 6.1.3, then you can start working the steps.
lapuccaAuthor Commented:
In the previous project I installed the EF 6.1x version first but still did not see my stored procedures, only the system sp, which is the exact same situation here with the new project.  I didn't install EF here but following the link steps, it created the xxxxxxxxx.edmx folder.  It created all the classes that reflects all the tables that I want to and all the columns.  

I don't know why my usp doesn't show but could it be the .net version in SQL that is not compatible?
Thank you.
lapuccaAuthor Commented:
Sorry Miguel, I thought EF designer is something else.  I just realized it is EntityFramework.  Yes, I already have that installed for this project, version is 6.1.3. Attached is the screen shot of Nuget showing what's installed.
EF.png
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.