There is an unsaved comment in progress. You will lose your changes if you continue. Are you sure you want to reopen the work item?

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

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.

@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.

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.

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.

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.

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">

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:

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.