Stored Function with Scalar Result

This example shows how to use a stored function that executes some operations and returns a scalar value as a result, for example, like this one:

The script for creating a stored function for the SQL Server DBMS is as follows:

CREATE FUNCTION dbo.Sum(@p1 int, @p2 int)
RETURNS int
AS
BEGIN
        RETURN @p1 + @p2;
END
GO

Create a model and add a new method to it corresponding to the function (either at the stage of creation at the Select database objects page of Create Model Wizard, or by dragging the corresponding stored function from the Database Explorer window to the diagram area or in the Model Explorer window of an already existing model).

As a result, we will get a method in the model corresponding to the stored function:

With the following parameters:

For stored functions with scalar results, Entity Developer supports two ways of generating methods.

Methods That Use DbCommand

By default, it generates the corresponding method, having a signature close to the relevant stored function, that creates and executes a DbCommand, calling the corresponding stored function, and gets it result via an output parameter:

C#:

        public System.Nullable<int> Sum (System.Nullable<int> p1, System.Nullable<int> p2)
        {
            System.Nullable<int> result;
            DbConnection connection = this.Database.GetDbConnection();
            bool needClose = false;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
                needClose = true;
            }
           
            try
            {
                using (DbCommand cmd = connection.CreateCommand())
                {
                    if (this.Database.GetCommandTimeout().HasValue)
                        cmd.CommandTimeout = this.Database.GetCommandTimeout().Value;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = @"dbo.[Sum]";
                    DbParameter p1Parameter = cmd.CreateParameter();
                    p1Parameter.ParameterName = "p1";
                    p1Parameter.Direction = ParameterDirection.Input;
                    if (p1.HasValue)
                    {
                        p1Parameter.Value = p1.Value;
                    }
                    else
                    {
                        p1Parameter.DbType = DbType.Int32;
                        p1Parameter.Size = -1;
                        p1Parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(p1Parameter);
                    DbParameter p2Parameter = cmd.CreateParameter();
                    p2Parameter.ParameterName = "p2";
                    p2Parameter.Direction = ParameterDirection.Input;
                    if (p2.HasValue)
                    {
                        p2Parameter.Value = p2.Value;
                    }
                    else
                    {
                        p2Parameter.DbType = DbType.Int32;
                        p2Parameter.Size = -1;
                        p2Parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(p2Parameter);
                   
                    DbParameter returnValueParameter = cmd.CreateParameter();
                    returnValueParameter.Direction = ParameterDirection.ReturnValue;
                    returnValueParameter.DbType = DbType.Int32;
                    returnValueParameter.Size = -1;
                    cmd.Parameters.Add(returnValueParameter);
                    cmd.ExecuteNonQuery();
                    result = (System.Nullable<int>)returnValueParameter.Value;
                }
            }
            finally
            {
                if (needClose)
                    connection.Close();
            }
            return result;
        }

This code allows using this stored function in the application with the help of method wrapper. This allows working with the stored function with all possible convenience, as wrapper methods are strongly typed, are found by IntelliSense and have signatures close to the corresponding stored functions. The methods generated using this way can be used both inside and outside of the LINQ queries.

Such method generation produces Entity Framework Core 1.1 compatible code.

 

Mapping Scalar Methods with DbFunctionAttribute

Entity Developer also supports the way of mapping database scalar functions, introduced in Entity Framework Core 2.0. It just creates a method stub with the DbFunctionAttribute. Entity Framework Core 2.0 automatically registers such methods. When such method is called in a LINQ query, Entity Framework Core 2.0 generates SQL, calling the corresponding database stored function, for this query.

To generate method in this way, you need to set its Direct Call property value to False in the Properties window. The following code is generated for our example function with Direct Call set to false:

        [DbFunction(@"[Sum]", @"dbo")]
        public static System.Nullable<int> Sum (System.Nullable<int> p1, System.Nullable<int> p2)
        {
            throw new NotSupportedException("Direct calls are not supported.");
        }

 

 

       See Also

---

Send feedback on this topic

© 2008 - 2024 Devart. All rights reserved.