Entity Framework stored procedure not available

Question

I added a reference to a stored procedure in my edmx file, then right clicked on it and selected "Create Function Import", it was added to the Function Imports folder under EntityContainer in the model browser.

As I understand it I should be able to use it like so:

sampleEntities db = new sampleEntities();
db.SampleStoredProcedure();

but it does not show up on the db object. Is there a step I'm missing? The Function Import is set to public, has no return value, and one parameter that I can see when I expand it.

Solution 1

Does your stored procedure return a simple (that is to say scalar) value? If so, the designer will not generate the code for you:

If the Return Type is set to a simple type, Visual Basic or C# is not automatically generated for the Function Import.

However, this has been fixed in the newest version of the Entity Framework:

You can select the None and Scalar return types as you could before. However, when the “Function Import” is created, some new code is injected into the Model code behind file that materializes the stored procedure into an operation on the ObjectContext itself.

Solution 2

You could execute stored procedure with EntityCommand class, add a new stored procedure to entity data model, then add it as a function and name it, then execute the stored procedure:

public bool ExecuteCMD(string Command) 
{
    using (var entities = new DbEntities())
    {
        var conn = new System.Data.EntityClient.EntityConnection(entities.Connection.ConnectionString);
        try
        {
            conn.Open();
            using (EntityCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "DbEntities." + Command;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

Comments

[jhunter]

So you're saying there is no way to run a SP that has no return value with Entity Framework? Why is it even an option then?

[Andrew Hare]

I am not saying that there is no way, I am saying that the current version of the Entity Framework requires you to implement the C# code yourself.