Entity Framework FAQ: Sprocs and Functions

Article
1/17/2024

[[articles:Entity Framework FAQ|Back to EF FAQs Table of Contents]]

Can stored procedures return polymorphic results?

Yes, stored procedures that retrieve entity instances can return polymorphic results rather than being restricted only to results of a single type.

How can I use stored procedures that only return simple types?

EF 4 supports stored procedures that return collections of scalars, complex types, and entities. So for stored procedures involving scalar values, the EF expects them to return a collection of scalar values rather than just one. See examples provided on MSDN: How to: Execute a query Using a Stored Procedure with In and Out Parameters and devtoolshed.com: using-stored-procedures-entity-framework-scalar-return-values.

For read stored procedures EF 3.5 SP1 only supports those that return entities. In this case, you can use classic ADO.NET to accomplish your tasks, and because the EF exposes the underlying database connection, it's relatively straightforward to integrate this kind of code with other EF-based code. This process is made even simpler with the addition of the EFExtensions library, which can be found at: http://code.msdn.microsoft.com/EFExtensions. With the EFExtensions library you could add a partial method to your context that would call your stored procedure with code that looks something like this:

public int  callMySproc()
{    DbCommand command = this.CreateStoreCommand("MySproc", 
                                CommandType.StoredProcedure);
     int result;
     using (this.Connection.CreateConnectionScope())
     {
        result = command.ExecuteNonQuery();
     }
     return result;
}

If your stored procedure returns a single value you have to use one of the methods that extract a single value from a collection, for example FirstOrDefault().

Does the Entity Framework support the ability to have a single stored procedure that returns multiple result sets?

The Entity Framework does not have support for returning multiple result sets from a single stored procedure out-of-the-box. However, there is a way to read multiple result sets with ObjectContext's Translate method that was added in Entity Framework 4. To accomplish this task you need to read the data by using ADO.NET. You do this by creating SqlConnection and SqlCommand objects, setting the command text to the name of the stored procedure, and then calling ExecuteReader to get the data reader. Then you can use the Translate method to materialize the entity objects from the reader. The Translate method takes a reader, an entity set name, and a merge option. Note that if you want to add the materialized objects to the context, so they can be tracked and synchronized with their related objects, you need to use MergeOption.AppendOnly. The following example demonstrates how to do that:

using (var context = new SchoolEntities())
{
    string providerString = @"Data Source=.;Initial Catalog=School;Integrated Security=True";
    using  (var conn = new SqlConnection(providerString))
    {
        var cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "GetStudentGrades";
        cmd.Parameters.AddWithValue("StudentID", 2);
        conn.Open();
        var reader = cmd.ExecuteReader(
                        System.Data.CommandBehavior.CloseConnection);
        var students = context.Translate<Person>(
                        reader, "People", MergeOption.AppendOnly).ToList();
        reader.NextResult();
        context.Translate<StudentGrade>(reader, 
                        "StudentGrades", MergeOption.AppendOnly) .ToList();
        foreach(var student in students)
        {
            foreach (var grade in student.StudentGrades)
            {
                Console.WriteLine(grade.Grade);
            }
        }
    }
}

Also see this post.

How do I execute a query using a stored procedure with out-parameters?

Stored procedures returning results do not populate output parameters until after all results have been read. So you have two alternatives:

If you are interested in the results, you have to iterate completely through them.
If you don't need the results, you can just call Dispose().

For more information, see How to: Execute a Query Using a Stored Procedure with In and Out Parameters.

How do I call a stored procedure when using the ExecuteStoreCommand method?

ExecuteStoreCommand was designed to support arbitrary SQL statements such as INSERTs, UPDATEs, DELETEs, and SELECTs. You can also use it to do things like setting isolation level (for example, SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED). Therefore, the CommandType of the DCommand is set to CommandType.Text rather than CommandType.StoredProcedure. For that reason you need to specify the full syntax to invoke the stored procedure, including the "OUT" modifier on any output parameters for which you want to obtain the values back, that is:

context.ExecuteStoreCommand("EXEC StoredProcedure1 @param1, @param2 OUT", 
                                                        storeParams);

Function import for stored procedures with no results uses the ExecuteFunction overload that returns an int to report affected rows. Why does the function return -1 even though I know the rows were affected?

Make sure the SET NOCOUNT property in SQL Server is OFF. If it is set to ON, the number of rows affected by a Transact-SQL statement will not be returned as part of the results.

How do I define custom functions (also called user-defined functions or model-defined functions or MDFs) in the conceptual model?

The Entity Framework enables you to define custom functions in the conceptual model. These functions are written in Entity SQL. You can use these functions in Entity SQL or LINQ queries or from other model-defined functions. MDFs can return scalar values, anonymous types, complex types, or collections of entity references. Model-defined functions are composable and can be used as building blocks in more complex expressions. To define a function, you need to edit the .edmx file manually. See the following topic for more information: How to: Define Custom Functions in the Conceptual Model.

The following is an example of a model-defined function that returns a collection of entity objects:

   <Function Name="GetStudentGradesMDF" ReturnType="Collection(SchoolModel.StudentGrade)">
        <Parameter Name="ID" Type="Edm.Int32" />
        <DefiningExpression>
            select VALUE sg
            from SchoolEntities.StudentGrades as sg
            where sg.StudentID == ID
        </DefiningExpression>
    </Function>

The following is an example of a model-defined function that returns a collection of anonymous types:

<Function Name="StudentInfo">
  <Parameter Name="PersonID" Type="Edm.Int32"/>
  <ReturnType>
    <CollectionType>
      <RowType>
        <Property Name="FirstName" Type="Edm.String"/>
        <Property Name="Grade" Type="Edm.Decimal"/>
      </RowType>
    </CollectionType>
  </ReturnType>
  <DefiningExpression>
    select sg.Person.FirstName, sg.Grade 
    from SchoolEntities.StudentGrades as sg 
    where sg.Person.PersonID == PersonID
  </DefiningExpression>
</Function>

Also, see the following articles:

Is it possible to call model-defined functions in LINQ queries?

Yes. See How to: Call Model-Defined Functions (LINQ to Entities).