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));
}