Hi,
Is there or will be a way in the future to pass/retrieve Oracle Objects (UDT's) via Entity Framework?
P.e. via ADO.net we could do this :
oracnn.Open();
Oracle.DataAccess.Client.OracleCommand oracmd = new Oracle.DataAccess.Client.OracleCommand();
oracmd.CommandText = "MYFUNCTION";
oracmd.CommandType = CommandType.StoredProcedure;
oracmd.Connection = oracnn;
Oracle.DataAccess.Client.OracleParameter retParam = new Oracle.DataAccess.Client.OracleParameter("RET_VALUE", Oracle.DataAccess.Client.OracleDbType.Object, ParameterDirection.ReturnValue);
Oracle.DataAccess.Client.OracleParameter param = new Oracle.DataAccess.Client.OracleParameter("PARAM", Oracle.DataAccess.Client.OracleDbType.Object, myObject, ParameterDirection.Input);
retParam .UdtTypeName = "MYOBJ";
param.UdtTypeName = "MYOBJ";
oracmd.Parameters.Add(retParam );
oracmd.Parameters.Add(param);
oracmd.ExecuteNonQuery();
MyOracletType output = oracmd.Parameters["RET_VALUE"].Value as MyOracletType ;
By doing this the Oracle Provider converts the oracle object in a .Net Object
But how can get the same result via Entity Framework 6 ?
( This is a very useful thing to do via Entity Framework, because in some old project's, Oracle databable's were Inserted/Updated/Deleted/Selected via Stored Procedures/functions that Receives/Retrieves an Oracle Object )
But when I try a similar approach via dbcontext from entity framework...
using(var context = new MyContext() )
{
var myObject = MyOracletType ();
// Set some properties
// Oracle.ManagedDataAccess.Client.OracleParameter doesn't have an OracleDbType.Object parameter type, so i try this way... xD
var res1 = db.Database.ExecuteSqlCommand("MYFUNCTION", myObject);
}
I get the following exception :
"Additional information: Value does not fall within the expected range."
With this stack trace :
at Oracle.ManagedDataAccess.Client.OracleParameter.set_Value(Object value)
at System.Data.Entity.Core.Objects.ObjectContext.CreateStoreCommand(String commandText, Object[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__58()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters)
at CodeFirstNewDatabaseSample.Program.Main(String[] args) in d:\NB16790\Projects\CodeFirstNewDatabaseSample\CodeFirstNewDatabaseSample\Program.cs:line 35
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
So, for what I understood from the exception, Oracle Object types aren't implemented in the ManagedDataAccess class, and I think the documentation says the same thing in this chapter "Differences between the ODP.NET Managed Driver and Unmanaged Driver". So, this brings to my initial question,Is there or will be in the future, a way to pass/retrieve Oracle Objects (UDT's) via Entity Framework?
Thank you,
César Afonso