2

Closed

Entity Framework not able to get results when 3rd party provider returns column numbers in the result set

description

Entity Framework not able to get results when 3rd party provider returns column numbers in the result set
Current behavior : Since the DB2 stored procedure returns multiple results with column numbers( Not Names), it is not getting identified with entity framework and hence Identifier is empty or null is coming as in the document that’s shared with you earlier.
Expected behavior : To see the result set even when the stored procedure call returns results with column numbers.

file attachments

Closed Nov 22, 2013 at 6:41 PM by RoMiller
EF Team Triage: The requirement for column names is fundamental to how EF is implemented. Unfortunately, given the amount of work required to remove this constraint, it is not something that we are planning to do.

comments

moozzyk wrote Nov 8, 2013 at 5:31 PM

Can you attach the edmx you got after reverse engineering the database but before importing the stored procedure?

aviswana wrote Nov 12, 2013 at 1:51 PM

Uploaded the Edmx that's generated after reverse engineering and before function import

moozzyk wrote Nov 12, 2013 at 7:36 PM

@Aviswana - I looked at the edmx but it is basically empty - there is no content in the Store section. This is a bit surprising since in the screenshot you had attached the store model branch in the model browser did have functions and they are read from the Store section of edmx. If you are sure you saved the edmx after you reverse engineer db then I would probably need more details and/or repro steps to be able to investigate the issue. Can you also check what errors (if any) are written to the Output window?
You seem to be using VS2013 - so there is an option of debugging the issue on your side. EF Designer is now open source so you could build it and debug. I think that the NotSupportedException you are seeing in the Function Import window is thrown when calling Microsoft.VisualStudio.Data.Package.DataMappedObjectSelector.SelectMappedObjects<IVsDataTabularFunction> from the DataSchemaServer.GetProcedureOrFunction() method (https://entityframework.codeplex.com/SourceControl/latest#src/EFTools/EntityDesign/VisualStudio/Data/Sql/DataSchemaServer.cs). SelectMappedObjects<IVsDataTabularFunction> call into the DDEX provider (in your case the provider for DB2) and we have no way of fixing this on our side. I have seen this happening if the DDEX provider did not return a valid IVsDataColumn.NativeDataType value (http://msdn.microsoft.com/en-us/library/vstudio/microsoft.visualstudio.data.services.relationalobjectmodel.ivsdatacolumn.nativedatatype.aspx).
There seems to be one more issue there as well. Apparently we may create an invalid SSDL (presumably due to the empty name of the column returned from the stored proc) but we don't seem to show errors exception in the error pane. To see what's really happening you could try seeing if there are any errors being reported in the EdmxModelHelper.UpdateStorageModels() (https://entityframework.codeplex.com/SourceControl/latest#src/EFTools/EntityDesign/VisualStudio/ModelWizard/engine/EdmxHelper.cs). I will file a work item to see if it can be reproduced.

moozzyk wrote Nov 12, 2013 at 7:40 PM

Here is the work item for the other issue.

aviswana wrote Nov 12, 2013 at 8:13 PM

Please use the new file. We are using VS 2012. In the edmx we added the stored procedure from the database. We did not do function import. Please let me know if this is what you are expecting.

moozzyk wrote Nov 13, 2013 at 1:40 AM

@aviswana - I looked at the new edmx you attached and the store definition seems to be correct:
        <Function Name="EPSP0189" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="EPDRW">
          <Parameter Name="I_MERCHANT_BUSAREA" Type="char" Mode="In" />
          <Parameter Name="I_REQ_BASE_TERM_ID" Type="char" Mode="In" />
          <Parameter Name="I_REQ_TRAN_CODE" Type="char" Mode="In" />
          <Parameter Name="I_TRX_FROM_TSTAMP" Type="timestamp" Mode="In" />
          <Parameter Name="I_TRX_TO_TSTAMP" Type="timestamp" Mode="In" />
          <Parameter Name="SP_STATUS" Type="integer" Mode="Out" />
        </Function>
With regards to the NotSupportedException - this seems to be an issue with DDEX provider for DB2 (I actually remember folks working on the DB2 EF provider asking me about this and this was after VS2012 was shipped so I assume it did not work in VS2012). What you can try is to create the function import manually by editing the edmx (a trick to make it easier could be to create a fake stored procedure in Sql Server (express or localdb), import it, copy corresponding fragments to the target edmx and update types). Note however that the columns with empty names cannot be mapped and I think all column names have to be unique.

aviswana wrote Nov 13, 2013 at 2:49 PM

Hi moozzyk,
Our organization standard is to return column numbers instead of names. So when I do the function import it is coming with empty column names and I am getting  NotSupportedException. All the other ORM's provide me with the capability to integrate with DB2 even when it returns column numbers. Why is Entity frame work an exception? Can this be expanded to look for column numbers also in addition to column names. More over if this capability is provided I can stay using native dlls instead of using 3rd part dlls. 

moozzyk wrote Nov 13, 2013 at 11:50 PM

EF does not have the capability to map result columns to properties of a type by using the ordinal number of the column.
As stated above - I am don't think that in this particular case the NotSupportedException is thrown by Entity Framework or the EF Designer itself. For me it seems like an exception from the BB2 DDEX provider that surfaces in the function import window. This could be worked around by manually mapping a store function to function import - here is the documentation for FunctionImport element in the CSDL and how to map it in MSL. In case of incorrect mapping EF will throw an exception from validation (which won't be a NotSupportedException) that will include line and column where the error exists along with a detailed error message explaining what was wrong.
Finally, I don't think there is a way of using EF with native dlls. EF runtime itself works in a database agnostic way. Similarly to the DDEX provider there needs to be an EF provider used at runtime that translates database agnostic query trees generated by EF to the SQL dialect specific to the store the provider is written for. Since the EF provider for a given store/DBMS has to be a ADO.NET provider and needs to have an implementation of a number of EF abstract classes it cannot be native.

aviswana wrote Nov 14, 2013 at 7:31 PM

When we did the changes like the way suggested by you, every property in the result set has the same value as the first column value from the database. I have more than 1 column getting returned and mutliple rows in the result set. But the complex object that's getting created has the same value for all the properties that are defined on the complex object. Please suggest us what needs to be changed to acheive what we are looking. The results that we are seeing is attached as a jpeg.
<!-- SSDL content --> <Function Name="EPSP0968" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="EPDRW">
      <Parameter Name="I_ORDER_NUMBER" Type="char" Mode="In" />
</Function>
<!-- CSDL content --> <FunctionImport Name="EPSP0968" ReturnType="Collection(DB2DModel.EPSP)">
        <Parameter Name="I_ORDER_NUMBER" Mode="In" Type="String" />
</FunctionImport>
<!-- C-S mapping content --> <FunctionImportMapping FunctionImportName="EPSP0968" FunctionName="DB2DModel.Store.EPSP0968" >
        <ResultMapping >
          <ComplexTypeMapping TypeName="DB2DModel.EPSP">
            <ScalarProperty Name="orderNum"  ColumnName="" />
            <ScalarProperty Name="seqNum"    ColumnName=""/>
            <ScalarProperty Name="TranType"  ColumnName=""/>
            <ScalarProperty Name="BankNum"   ColumnName=""/>
            <ScalarProperty Name="BaseTerm"  ColumnName=""/>
            <ScalarProperty Name="app_class" ColumnName=""/>
            <ScalarProperty Name="appMethod" ColumnName=""/>
          </ComplexTypeMapping>
        </ResultMapping>
</FunctionImportMapping>

moozzyk wrote Nov 22, 2013 at 5:58 PM

Unfortunately the above mapping cannot work. ColumnName attribute binds to the Function ReturnType RowType in the SSDL that describes columns returned by the store function (e.g. CompanyName in the sample below:

```<Function Name="fx_SuppliersWithinRange" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<Parameter Name="miles" Type="int" Mode="In" />
<Parameter Name="location" Type="geography" Mode="In" />
<ReturnType>
<CollectionType>
  <RowType>
    <Property Name="SupplierID" Type="int" Nullable="false" />
    <Property Name="CompanyName" Type="nvarchar" MaxLength="40" Nullable="false" />
    <Property Name="Location" Type="geography" />
  </RowType>
</CollectionType>
</ReturnType>
</Function>``` ).

However the PropertyName cannot be the empty string and results in the "Error 17: Name is not valid.". As I said before EF operates on names and not on column ordinal numbers and changing this would be a fundamental change to how EF works.