EF Code-First Stored Procedures

Summary

In this post I’m going to show the very basics of calling a stored procedure from Entity Framework using the Code-First technique.

The Database Setup

First, I’m going to create a database named “APIUniversity”. If you’ve tried out the API projects from my previous posts, you probably already have this database in place and you can just use that. Here’s the “Room” table:

CREATE TABLE [dbo].[Room](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [RoomNumber] [int] NULL,
 CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Some data you can use to populate the table:

insert into room (name,roomnumber) values ('Gym',1)
insert into room (name,roomnumber) values ('Math Room',100)
insert into room (name,roomnumber) values ('Chemistry Lab',101)
insert into room (name,roomnumber) values ('Lecture 1',102)
insert into room (name,roomnumber) values ('Lecture 2',103)
insert into room (name,roomnumber) values ('Art Room',104)

And finally, here’s the SQL script to create the stored procedure that we’ll use:

CREATE PROCEDURE [dbo].[select_rooms]
AS
BEGIN
    SELECT id,name FROM Room
END

First, we’ll need a class to define the record that will be returned from this stored procedure:

public class RoomListResult
{
    public int id { get; set; }
    public string Name { get; set; }
}

You don’t need any “usings” for this code.

Now you’ll need to add your stored procedure read method to your context:

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace CodeFirstSample
{
    public class MyContext : DbContext, IDisposable
    {
        public MyContext()
            : base("My Connection String")
        {

        }

        public DbRawSqlQuery<RoomListResult> RoomList()
        {
            return this.Database.SqlQuery<RoomListResult>("select_rooms");
        }
    }
}

Technically, you can perform your stored procedure access from within your linq query, but this method keeps your code clean when you start to add parameters to stored procedures.

Here’s the code you can test in your console application to see the results:

class Program
{
    static void Main(string[] args)
    {
        using (var db = new MyContext())
        {
            var results = db.RoomList();

            foreach (var item in results)
            {
                Console.WriteLine(item.Name);
            }

            Console.ReadKey();
        }
    }
}

You should see a list of room names like this:

I didn’t cover how to handle parameters. I’m saving that for a later post.

The Code

You can download the entire project at my GitHub account by clicking here.  Included in the project is the SQL script you’ll need to generate the table, stored procedure and populate your table with the seed data.

Leave a Reply