Returning oracle ref cursor using Entity Framework

Viewed 1127

I'm trying to receive the result from an Oracle stored procedure that has a record list of office information using Entity Framework, and for the life of me I can't get this working. I'm using EF6 with the Functions add-on. I've tested the stored procedure. It is working as expected and executable by my Oracle user. The closest I've gotten is no results returned, when I know I should get some back.

I added comments to show different things I've tried.

This method calls preps the data for the procedure. It is returning null at the moment so i can verify the result list.

public ObjectResult<OfficeLocation> SP_GET_OFFICE_LIST(int radius, double latitude, double longitude)
    {
        var latdec = Convert.ToDecimal(latitude);
        var longdec = Convert.ToDecimal(longitude);
        try
        {
            var radiusParameter = new ObjectParameter("v_radius", radius);
            var latitudeParameter = new ObjectParameter("v_latitude", latdec);
            var longitudeParameter = new ObjectParameter("v_longitude", longdec);
            var officeList = new List<OfficeLocation>();

            //Did not work and returns 0 results
            //var output = new AgentInfoEntities().ObjectContext().ExecuteFunction<OfficeLocation>("SP_GET_OFFICE_LIST", latitudeParameter, longitudeParameter, radiusParameter);
            //Tried calling the function directly
            var output2 = OracleFunctions.SP_GET_OFFICE_LIST(latdec, longdec, radius);

            return null;
        }
        catch (Exception e)
        {
            Log.Error("**** Exception: " + e);
            throw e;
        }
    }

This function calls the procedure

public static class OracleFunctions
{
    [Function(FunctionType.StoredProcedure, nameof(SP_GET_OFFICE_LIST), Schema = "schema_name")]
    public static IQueryable<OfficeLocation> SP_GET_OFFICE_LIST(decimal latitude, decimal longitude, int radius)
    {
        var parameters = new List<ObjectParameter>(3);

            parameters.Add(new ObjectParameter("v_latitude", latitude));
            parameters.Add(new ObjectParameter("v_longitude", longitude));
            parameters.Add(new ObjectParameter("v_radius", radius));

            //Try by passing parameters as an array
            return new AgentInfoEntities().ObjectContext()
                .CreateQuery<OfficeLocation>("[SP_GET_OFFICE_LIST](@v_latitude, @v_longitude, @v_radius)",
                    parameters.ToArray());
    }
}

In the AgentInfoEntities class that inherits from DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
        base.OnModelCreating(modelBuilder);
        modelBuilder.Conventions.Add(new FunctionConvention(typeof(OracleFunctions)));
        modelBuilder.ComplexType<OfficeLocation>();
        modelBuilder.AddFunctions(typeof(OracleFunctions));
}
0 Answers
Related