Stored procedures in entity framework

Question

I am trying to use a stored procedure in the entity framework that returns nothing. The stored procedure is purely for logging.

I added a function (right click -> add -> function import) which works ONLY when the return value is set to one of the entities. When I change the return type to be Int32, Bool or nothing, or any other value than an entity, the function (method) simply vanishes as soon as I hit the build button.

Any suggestions on how I can get this sorted?

Answer 1

For now you cannot - you have to use one of the entities defined in your model (although I thought basic scalar types like INT should work, too).

Things should get a lot better with EF4 - due out in late 2009 or early 2010.

See these articles for some info on those new features:

• A big step for Stored Procedures in EF4
• Complex Types in the EDM Designer in EF4 and a look at updating complex types in code

If your stored proc is purely for logging, I would probably just surface it outside the Entity Framework context and just call the stored procedure the good old-fashioned ADO.NET way.... why even bother putting all of that into an EF context? You're not retrieving, manipulating and storing data with this logging service - just make it a static method on a static class - keep it simple!

Marc

Answer 2

Thanks for the reply.

I am storing data. The logging gets stored into the database. I wanted it part of the entity framework so I can just use the already existing code for maninpulating the data to log who and what was manipulated.

I will just work around it for now, call the SP using ADO.NET.

Thanks

Answer 3

This is one way of executing a stored procedure from enitity framework:

            using (SEntities se = new SEntities())
            {
                EntityConnection entityConnection = (EntityConnection)se.Connection;
                DbConnection storeConnection = entityConnection.StoreConnection;

                storeConnection.Open();

                DbCommand command = storeConnection.CreateCommand();
                command.CommandText = "NameOfStoredProcedure";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("param1", value_of_param1));
                command.Parameters.Add(new SqlParameter("param2", value_of_param2));

                SqlParameter param3 = new SqlParameter();
                pA.SqlDbType = SqlDbType.Bit;
                pA.ParameterName = "@param3";
                pA.Direction = ParameterDirection.Output;
                command.Parameters.Add(param3);

                command.ExecuteNonQuery();

                returnValue = Convert.ToBoolean(param3.Value);
            }