Entity Framework 4 - Code First and Stored Procedures

Question

Hi,

I want to execute stored procedure with one parameter that returns table using EF4 "Code First". I am ok with some DTO just for this purpose, it doesn't have to return entities. I have tried to:

a) create edmx file with function import and add it to my ObjectContext like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.RegisterEdmx("Model.edmx");
}

but I got InvalidOperationException saying "Registration of an existing model cannot be used after code first configuration using the fluent API has been started."

b) access underling connection and execute the procedure:

    var connection = this.objectContext.UnderlyingContext.Connection;
    connection.Open();
    DbCommand command = connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "booklog.Recommendations";
    command.Parameters.Add(
        new EntityParameter("userId", DbType.Guid) { Value = this.userId });
    var reader = command.ExecuteReader();
    // etc.

where

public class MyObjectContext : DbContext
{
    public System.Data.Objects.ObjectContext UnderlyingContext
    {
        get { return this.ObjectContext; }
    }

    // ....
 }

but this approach doesn't work as well. It throws InvalidOperationException with message "The container 'booklog' specified for the FunctionImport could not be found in the current workspace."

Answer 1

Ok, b) is correct, but one doesn't have to use UnderlyingContext, just ObjectContext.Database.Connection. Here is the code:

    var connection = this.objectContext.Database.Connection;
    connection.Open();

    DbCommand command = connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "Recommendations";
    command.Parameters.Add(
        new SqlParameter("param", DbType.Guid) { Value = id });

    var reader = command.ExecuteReader();
    while (reader.Read())
    {
        // ...
    }

    connection.Close();