Home > dotnet 4, Entity Framework > EF Code First:Executing Stored Procedure

EF Code First:Executing Stored Procedure

In this post I am going to show you how to use stored procedure with Entity Framework 4.1 even though it is official that Entity Framework 4.1 doesn’t support stored procedure and this was announced by the ADO.NET team at the time of the release. What it means that you can’t write stored procedure which return different data structure other than your entity wrapped in with DbSet and currently there is no way to map it a different entity which is not a database table.

Anyway without going into much details that why this feature didn’t make it to its first release, lets focus on how we can execute stored procedure which maps to a table.

I recommend you read my previous post which explains the idea behind POCOContextScript class, so we will add 2 more constants which has the DML for the stored procedures.

POCOContextScript.cs

public static class POCOContextScript
{
   public const string CreateProcGetAllProducts
      = @"create procedure [dbo].[GetAllProducts]
            as
            begin
                set nocount on;
                select * from product
            end";

   public const string CreateProcGetProductById
      = @"create procedure [dbo].[GetByProductId]
             @productid int
           as
           begin
               set nocount on;
               select * from product p
               where p.id = @productid
           end";
}

Nothing special just 2 sql procedure statement to get all products and get a specific product by id. Now we will follow the way we used in the previous post we can hook this into a class which implements the IDatabaseInitializer interface and lets run a unit test to see how we can invoke them.

GetAllProduct

[TestMethod]
public void Should_be_able_to_execute_sql()
{
   var context = new POCOContext();
   var result = context.Product.SqlQuery("GetAllProducts").ToList();
   Assert.IsTrue(result.Count > 0);
}


GetByProductId

[TestMethod]
public void Should_be_able_to_get_product_by_id()
{
   var context = new POCOContext();
   var parameter = new SqlParameter
   {
      DbType = DbType.Int32,
      ParameterName = "ProductId",
      Value = 1
   };

   var result = context.Product.SqlQuery
                                ("GetByProductId @ProductId",
                                   parameter
                                ).FirstOrDefault();

   Assert.AreEqual(1, result.Id);
}

That’s it for now and I hope the ADO.Net team is working on this feature for the next release and along with bulk copy and other features the next version will be even better.

Advertisements
  1. Tony
    June 2, 2011 at 5:20 pm

    Hi,
    I must say your blog is a very interesting source of technical and some real life project stuff. Keep up the good work.

    Thnx

    Tony

  2. June 6, 2011 at 8:06 pm

    You’re so close to what I need it’s not even funny! How do I call a sproc with multiple parameters? (Without having to create DbParameters for each one).

    • June 9, 2011 at 8:46 pm

      Hi Tony,
      I think its a good practice to use DbParameters, as it provides strong typing and will take care of SQL injection. Moreover you don’t have to do any conversion, but of you really want to use without it you can simply concatenate the parameters as string and format it with you SQL proc string.

  3. Martijn
    June 16, 2011 at 4:26 pm

    Hi,

    Thanks for the post.
    Is it possible in some way to add mapping from “column” names to entity properties using EF Code First?
    E.g.: Country object has Id property while the “column” from the SP is called CountryId. Now I get an exception:

    The data reader is incompatible with the specified ‘Country’. A member of the type, ‘Id’, does not have a corresponding column in the data reader with the same name.

    I did configure the Country object:

    public class CountryConfiguration: EntityTypeConfiguration
    {
    public CountryConfiguration(): base()
    {
    HasKey(p => p.Id);
    Property(p => p.Id)
    .HasColumnName(“LandId”)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
    .IsRequired();
    Property(p => p.Name)
    .HasColumnName(“CountryName”)
    .IsRequired();
    ToTable(“tblCountry”);
    }
    }

    I really need the EF to implement a repository so data access is nicely abstracted away and code first is a great step forward in that. But I’m working with a huge legacy database and want to create repositories on it while using its very complicated SP’s for retrieving, adding, modifying and deleting records.

    Thanks!

  4. Martijn
    June 16, 2011 at 4:30 pm

    HasColumnName(“LandId”)

    “LandId” is CountryId, I’m aware of this mistake, problem is still relevant

  5. September 1, 2011 at 12:05 am

    This did the trick for me. Kinda hacky but guess it’s the best we have right now in EF codefirst land.

    • September 20, 2011 at 8:28 pm

      Hi,
      I am glad that it the trick and I guess this is the only way to do this as EF team doesn’t have stored procedure high in their priority.

  6. May 3, 2012 at 12:59 pm

    HI,
    i have a complex query like this
    ALTER PROCEDURE GETDASHBOARD
    AS
    BEGIN
    SELECT
    E.FirstName + ‘ ‘+ E.LastName AS EmployeeName,
    S.SportName AS SportName,
    P.Points AS Point
    INTO #POINTTABLE
    FROM
    (
    SELECT EmployeeID, SportID, SUM(Point) AS Points
    FROM PointsPerSport
    WHERE MONTH(Date) = MONTH(GETDATE())
    GROUP BY EmployeeID, SportID
    )P
    INNER JOIN Employees E ON E.EmployeeID = P.EmployeeID
    INNER JOIN Games S ON S.SportID = P.SportID
    ORDER BY P.Points DESC
    SELECT DISTINCT TOP 3 Point INTO #TOPPOINT FROM #POINTTABLE ORDER BY Point DESC
    SELECT
    P.EmployeeName,
    P.SportName,
    P.Point
    FROM #POINTTABLE P
    INNER JOIN #TOPPOINT E ON p.Point = e.Point
    GROUP BY p.Point,p.EmployeeName,p.SportName
    ORDER BY P.Point DESC
    END

    and i want to call this sp in mvc entity framework. but when i wanted to add feature for complex return i dont get scheme of sp in complex. it say it return int.

    can you help me.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: