CODEWITHSUNDEEP
c#entity-frameworkstored-proceduresasp.net-mvc-5
user4864716
user4864716

Reputation:

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

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

Specify name of database context

2. Specify "Code First From Database"

Specify "Code First From Database"

3. Choose Database Connection

Choose Database Connection

4. No stored procedures are available

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

No stored procedures are available

Upvotes: 7

Views: 7259

Answers (4)

user4864716
user4864716

Reputation:

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:

  1. 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),
      -- ....
  1. 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; }
}
  1. In the database context file, add:

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

  1. Do not add anything to the "OnModelCreating" function.

  2. Right-click the Controllers folder and a new scaffolded Item: "MVC 5 Controller with Entity Framework".

  3. 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);
}
  1. Rebuild and view the Index. There should be no compile or runtime errors. The View should show the required data.

Upvotes: 3

Nick Acosta
Nick Acosta

Reputation: 1910

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)?

Upvotes: 4

Tarun Pothulapati
Tarun Pothulapati

Reputation: 572

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

Upvotes: 2

Jwit
Jwit

Reputation: 156

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

Upvotes: 1

Related Questions