EF Code-First Stored Procedures With Parameters

Summary

In an earlier post I showed how to create a method that can access a stored procedure using Entity Framework Code-First.  In this post I’m going to show how to pass parameters and show a few tricks.

The Stored Procedure

First, you’ll need to create a new stored procedure in your APIUniversity database (see this post for the table definition and other stored procedure).

CREATE PROCEDURE [dbo].[select_one_room]
    @roomnum AS int
AS
BEGIN
    SELECT
id,name FROM Room WHERE roomnumber = @roomnum
END

New Method

Now create a new method named GetRoom:

public DbRawSqlQuery<RoomListResult> GetRoom(int roomNumber)
{
    var roomNumberParam = new SqlParameter(“roomnum“, roomNumber);

    return this.Database.SqlQuery<RoomListResult>(“EXEC select_one_room @roomnum“, roomNumberParam);
}

 First, you’ll notice that I added a SqlParameter for the parameter that will be fed into the SqlQuery call.  Second, you might notice that I had to put in the “EXEC” command in the stored procedure string.  Apparently, this can be left off, if you’re stored procedure doesn’t contain any parameters, but will complain with an error “Incorrect syntax near ‘select_one_room'” if it is not used in this case.

Make sure you put commas between multiple parameters.  You can also leave off the “@” symbol in the parameter name when used in the SqlParameter() method, but you’ll need the “@” symbol in the SqlQuery() method string.

To use this stored procedure, your query might look something like this:

using (var db = new MyContext())
{
    var results = db.GetRoom(1);

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

    Console.ReadKey();
}

Bug in the Return Class Column Attribute

For tables, you can rename fields by using an attribute to declare what the column name is inside the database.  Here’s an example where I changed the “Room” class so that the “Name” field is now “RoomName”, but the field in the database remains “Name”:

[Table(“Room“)]
public class Room
{
    [Key]
    public int id { get; set; }

    [Column(“Name“)]
    public string RoomName { get; set; }
    public int RoomNumber { get; set; }
}

This works as expected.

Now, if you try this trick on the “RoomListResult” class, you’ll discover that the attribute is ignored.  This is a known bug (maybe sometime in the future I can amend this post if the bug gets fixed).  Your result will return null for any fields that do not match the field name returned in the stored procedure result.

 Where to Get the Sample Code

I have posted the sample code on my GitHub account.  You can download it by clicking here.  Be sure to search for “My Connection String” and replace with your own database connection string.

 

 

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.

 

Entity Framework Code-First and Code Only

Summary

In this blog post I’m going to explain the basics of Entity Framework code-first.  I’ll show how to setup your context so that a database is not generated.

Code-First / Code-Only

If your database is already in place, then the most obvious method of using EF is to use Database First.  This is where you start a new EDMX file and generate the code that matches your database.  The problem with database first is that there are script files (tt files) based on the xml master file (edmx) that automatically generate your table and context code.  If you make any database changes, you have to run the update on the edmx file and force the script files to regenerate your code.  This can be a headache if you are deploying to a database that has a slightly different design than your test database.  It can also be a serious problem when you are using version control since two different versions of the edmx file are impossible to reconcile.  If you’re unsure what I’m talking about, right-click on the edmx file in your project and open as an xml file.  Scroll through and observe how the data is stored.

In code-first, you define your context and tables manually in code.  Then your database is generated when you run it the first time.  This guarantees that your database is defined the match your EF code.  The issue with this scheme is that you might already have a database designed and you might want more flexibility in your table definitions in code.  Once instance is a situation where you might design a module (dll) that is used for a specific purpose in your program and only needs to query a few fields from a hand-full of tables.  With code-first you can define exactly which fields you want to read and ignore any extra fields.  However, if your database gets generated from the minimal context, you’ll end up with a database that doesn’t contain all the fields you need.  This is where you need to disable the auto-generation part of code-first and turn your ORM into Code-Only.

Here’s an example of a code-first database with one simple table (you’ll need to add NuGet EF 6 to your project):

MyContext.cs file:

using System;
using System.Data.Entity;

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

        }

        // define tables here
        public DbSet<Room> Rooms { get; set; }
    }
}

Room.cs file:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstSample
{
    [Table(“Room“)]
    public class Room
    {
        [Key]
        public int id { get; set; }
        public string Name { get; set; }
        public int RoomNumber { get; set; }
    }
}

In the above example one table named “Room” will get created in a database named according to the connection string.  If you wish to prevent database creation from happening you can alter the context constructor like this:

public class MyContext : DbContext, IDisposable
{
    public MyContext()
        : base(“My Connection String“)
    {
        Database.SetInitializer<MyContext>(null);
    }

    // define tables here
    public DbSet<Room> Rooms { get; set; }
}

This will cause the context to skip the database generator.

When you’re laying out your code for your database it’s standard practice to create a sub-directory in your project and name it something like DAL (for data access layer) or similar.  Then create your context cs file and all your table cs files in that directory.  You can put all your tables in one file, but it’s easier to find code if your put one file per table and name the file the name of the table.

It’s also standard practice to pluralize the table name and leave the record instance or class name non-plural.  In the example above the record is “Room” and the table is “Rooms”.  If your table is named “Rooms” in your database, then you can alter the table attribute to match ([Table(“Rooms“)]).

The connection string can be a string, a variable or it can be read from your app.config or web.config file using the ConfigurationManager object like so:

private string ConnectionString = ConfigurationManager.ConnectionStrings[“My Connection“].ToString();

You’ll have to add a reference to System.Configuration and put in a using for System.Configuration in order to use the ConfigurationManager object.