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.


 

Leave a Reply