Hi.
While exploring the capabilities of Entity Framework 5 + Oracle (ODP 12.1.0.1.2 + DB 12.1.0.1.0) I tried to map output of a procedure to an entity containing a boolean field but I'm getting the following exception:
System.InvalidOperationException was unhandled by user code
HResult=-2146233079
Message=The 'Enabled' property on 'Branch' could not be set to a 'Int16' value. You must set this property to a non-null value of type 'Boolean'.
Source=System.Data.Entity
StackTrace:
at System.Data.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
at System.Data.Common.Internal.Materialization.Shaper.GetPropertyValueWithErrorHandling[TProperty](Int32 ordinal, String propertyName, String typeName)
at lambda_method(Closure , Shaper )
at System.Data.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
at lambda_method(Closure , Shaper )
at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at EF.Oracle.Tests.EntityFrameworkTests.ProcedureCall() in e:\Projects\EntityFrameworkTests\EF.Oracle.Tests\EntityFrameworkTests.cs:line 34
Now 'Branch' is a table containing column 'Enabled' of type NUMBER(1, 0). I'm trying to retrieve full rows using a simple procedure:
CREATE OR REPLACE PROCEDURE GETBRANCHBYSHORTNAME(
SHORTNAME IN NVARCHAR2,
RESULTS OUT SYS_REFCURSOR
) AS
BEGIN
OPEN RESULTS FOR SELECT * FROM "Branch" b WHERE b."ShortName" = SHORTNAME;
END;
I'm using managed version of the provider so in configuration i tried setting this:
<oracle.manageddataaccess.client>
<version number="*">
<settings>
<setting name="TNS_ADMIN" value="C:\Oracle\product\11.2.0\client_1\Network\Admin"/>
</settings>
<edmMappings>
<edmMapping dataType="number">
<add name="bool" precision="1" />
<add name="byte" precision="2" />
<add name="int16" precision="4" />
<add name="int32" precision="9" />
<add name="int64" precision="18" />
</edmMapping>
</edmMappings>
<implicitRefCursor>
<storedProcedure schema="CNAV400" name="GETBRANCHBYSHORTNAME">
<refCursor name="RESULTS">
<bindInfo mode="Output" />
<metadata columnOrdinal="0" columnName="BranchID" providerType="Int32" nativeDataType="Number" />
<metadata columnOrdinal="1" columnName="CreatedBy;" providerType="Int32" nativeDataType="Number" />
<metadata columnOrdinal="2" columnName="CreatedOn;" providerType="Date" nativeDataType="Date" />
<metadata columnOrdinal="3" columnName="Name" providerType="NVarchar2" nativeDataType="NVarchar2" />
<metadata columnOrdinal="4" columnName="ShortName;" providerType="NVarchar2" nativeDataType="NVarchar2" />
<metadata columnOrdinal="5" columnName="Enabled" providerType="Int16" nativeDataType="Number" numericPrecision="1" dataType="System.Boolean" />
</refCursor>
</storedProcedure>
</implicitRefCursor>
</version>
</oracle.manageddataaccess.client>
When I try to get all rows using LINQ it works but when i try using a procedure it fails. If I dont specify any metadata it fails with the same exception. Is this scenario supported or am I missing something?
Also I have encountered a problem with case sensitivity. When I named my procedure "GetBranchByShortName" in database I couldn't even call it from EF. The error was wrong number or types of arguments.
I tried this:
<storedProcedure schema="CNAV400" name="GetBranchByShortName">
And this which was suggested by the designer:
<storedProcedure schema="CNAV400" name=""GetBranchByShortName"">
Any help regarding these issues will be greatly appreciated. Thank you.