MVC 5, EF 6, and "Code First from Database" Stored Procedures

Question

In searching for an answer, even with the keywords "Code First from Database" in quotes, I found that the answers did not address the specific terms, so I'm asking the question with a specific emphasis on "Code First from Database" only.

In an ASP.NET MVC 5 web application using Entity Framework 6, I would like to be able to use the "ADO.NET Entity Data Model" wizard to map to an existing stored procedure on a database.

Why am I asking the question and what do I hope to accomplish?

This particular stored procedure is a parameterized query, where the user (in the View) would submit by form a date range and receive the results of that query in the table. On the SQL Server side, it is easy: declare two dates and get your query results.

I would like the user, from the View, to "simply" select a date range and see the results immediately below as supplied by the stored procedure query.

Below are screenshots showing my attempt to use the wizard to map the stored procedure.

1. Specify name of database context

2. Specify "Code First From Database"

3. Choose Database Connection

4. No stored procedures are available

I also notice that the option to import stored procedures is greyed out.

score 4 · Accepted Answer · edited May 23 '17 at 12:33

4

EDIT: Tarun mentioned the stored procedures aren't showing up due to lack of permissions.

Entity Framework provide APIs to call stored procedures though, you can even map a stored procedure to a model.

See this link: https://msdn.microsoft.com/en-us/data/dn468673.aspx

To bind a model to a stored procedure:

 modelBuilder.Entity<Blog>().MapToStoredProcedures();

If you are not interested in mapping a stored procedure see this post:

How to call Stored Procedure in Entity Framework 6 (Code-First)?

edited May 23 '17 at 12:33

Community

1
1

answered Jun 29 '16 at 20:07

Nick Acosta

1,890
17
19

I'm trying this approach, but I am getting this error "No mapping exists from object type System.DateTime[] to a known managed provider native type." `DateTime[] parameters = new DateTime[2] { d1, d2 }; var query = db.Database.SqlQuery("GetCreditSumByDateRange",parameters).ToList();` Still working on it. – Jun 29 '16 at 20:30
I'm marking yours as the answer. There were some learning curve errors I made, but it was the "How to call Stored Procedure in Entity Framework 6 (Code-First)?" that ended up being most beneficial. I'll add a full step-by-step procedure in my own answer shortly. – Jun 29 '16 at 21:20

score 3 · Answer 2 · 2016-06-29T21:59:19.743

The direct answer to my question is that there is no way I can trick the wizard to import a stored procedure.

Instead, this is what I needed to do:

Make sure that my stored procedure had an ID column of type int

SELECT DISTINCT ID = cast(ROW_NUMBER() OVER (
              ORDER BY z.Campus,
                  z.StudentName,
                  z.StudentID
              ) AS INT),
      -- ....

In the Models folder, manually create my class and property definition.

public class PerfOdomoeterDate
{        
  public int ID { get; set; }
  public string Campus { get; set; }
  public string StudentName { get; set; }
  public int StudentID { get; set; }
  public DateTime StartDate { get; set; }
  public DateTime EndDate { get; set; }
  public double Credits { get; set; }
}

In the database context file, add:

public virtual DbSet<PerfOdomoeterDate> PerfOdomoeterDates { get; set; }

Do not add anything to the "OnModelCreating" function.
Right-click the Controllers folder and a new scaffolded Item: "MVC 5 Controller with Entity Framework".
The Index action shown is a "hello world" sample to make sure that basic contact is made with the Stored Procedure. I will definitely customize it with a View Model, parameters, etc. But this should clearly show how to get your code to work.

private PerformanceContext db = new PerformanceContext();        

[HttpGet]
public ActionResult Index()
{   
  // These will be filled by form submission
  DateTime d1 = Convert.ToDateTime("12/1/2014");
  DateTime d2 = Convert.ToDateTime("5/1/2015");

  // supply parameter values required by the stored procedure
  object[] parameters = {
      new SqlParameter("@date1",SqlDbType.DateTime) {Value=d1},
      new SqlParameter("@date2",SqlDbType.DateTime) {Value=d2}
                   };

  // populate the list by calling the stored procedure and supplying parameters
  IEnumerable<PerfOdomoeterDate> query = 
      db.Database.SqlQuery<PerfOdomoeterDate>("PerfOdomoeterDate @date1, @date2", 
          parameters).ToList();            

  return View(query);
}

Rebuild and view the Index. There should be no compile or runtime errors. The View should show the required data.

Thanks for sharing your findings! – Nick Acosta Jun 29 '16 at 21:57 — Nick Acosta, Jun 29 '16 at 21:57

Tarun Pothulapati · Answer 3 · 2016-06-29T20:13:07.620

2

Permissions.(Make sure the db user has the permissions on the stored procedure

edited Jun 29 '16 at 20:13

answered Jun 29 '16 at 20:06

Tarun Pothulapati

572
1
5
13

I had already verified permissions and reconfirmed them. For me, this is not the issue. – Jun 29 '16 at 21:18

score 1 · Answer 4 · answered Jun 29 '16 at 20:06

1

I do not believe you can do this from the Wizard as of yet, generating from stored procs is a relatively new feature.

To map to procs from the code you will need to do the following

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures();

The full details are in this post

https://msdn.microsoft.com/en-us/data/dn468673.aspx

answered Jun 29 '16 at 20:06

Jwit

156
6

In a recent project,I did import some stored procedures using the same wizard,Its a problem with permissions I guess – Tarun Pothulapati Jun 29 '16 at 20:08
@TarunPothulapati - are you sure u didn't use the Database First wizard? I can do the import of stored procs via Database First but not Code First and I believe it's just not supported yet. – Diskdrive Jun 13 '17 at 00:38

MVC 5, EF 6, and "Code First from Database" Stored Procedures

4 Answers4