Thursday, May 27, 2010

Why I hate Entity Framework (Version 1) – Importing stored procedures with scalar results

Lets start by writing the following simple store procedure example which returns count. Millions of those are used all over the web for paging:
CREATE PROCEDURE sp_CountProducts
(
 @ProductTypeID AS INT
)
AS
SELECT COUNT(*) FROM Products WHERE ProductTypeID = @ProductTypeID
Now you should add this stored procedure to your data layer. Importing stored procedures into you project data layer is simple with Entity Framework, isn't it? If you import stored procedure which returns entity type as results then yes.
  1. update model to include your procedure
  2. make function import
  3. define result type
But what about stored procedures which returns scalars as results? (Now you are F***ED) Then you have the same steps as with previous procedure plus you should write additional code. You will have to extend your Entity Data Model to be able to use you procedure: (I use separate file: EntityDataModel.cs)
public partial class EntityDataModel: global::System.Data.Objects.ObjectContext
{
        private T ExecuteScalarFunction<T>(string functionName, DbParameter[] parameters)
        {
            DbCommand cmd = ((EntityConnection)this.Connection).CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            cmd.CommandText = this.DefaultContainerName + "." + functionName;
            try
            {
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                var obj = cmd.ExecuteScalar();
                return (T)obj;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        // And now your stored procedure
        public int sp_CountProducts(global::System.Nullable<int> regionId)
        {
            EntityParameter ProductTypeIDParameter;
            ProductTypeIDParameter = new EntityParameter("ProductTypeID", DbType.Int32);
            if (regionId.HasValue)
            {
                ProductTypeIDParameter.Value = regionId;
            }

            return ExecuteScalarFunction<int>(
                "sp_CountProducts",
                new EntityParameter[]{ ProductTypeIDParameter });
        }
}
Now you can use your procedure in the application code like this:
using (EntityDataModel context = new EntityDataModel(ConnectionString))
{
    RowCount = context.sp_CountProducts(productTypeId);
}
What is wrong with EF team? What are you drinking at work guys? Thanks GOD you fixed this stupid flaw in EF 4. (One tiny note: to be able to use EF 4 I will have to buy Visual Studio 2010. Isn't it wonderful?)