Entity Framework: Executing a stored procedure that returns different columns and column data types

Question

I am trying to execute a stored procedure this way:

var filterValues= context.Database.SqlQuery<FilterProcedureDTO>( 
 "[dbo].[sp_GetFilterValues] @FieldID", new SqlParameter("FieldID", filterID))
 .ToList();

the issue is the filter values that come up have diffrent column name with each call as the user changes the filter on the view,though all come up as objects with an int column and string column,it seems they are bound to the specified model ie FilterProcedureDTO..which looks like

  public class FilterProcedureDTO
    {
        public FilterProcedureDTO() {
        //empty constructor
        }
        public int production_lineID { get; set; }
        public string production_line_desc { get; set; }
    }

so if the call produces taskID and task_desc this wont work anymore. Another thing is some IDs are int32 and some are int16 so the code is not executing perfectly because of periodic exceptions

How can I get the stored procedure to return generic objects,just recognising the datatypes and not variable names too?

A stored procedure that returns varying column names is untenable. You'll need to alter it so that there's at least some congruity between various executions. — Chris Pratt, Mar 18 '15 at 16:18

score 1 · Accepted Answer · edited May 23 '17 at 10:10

1

The SQLQuery method always attempts the column-to-property matching based on property name.

So you need to change your stored procedure so that it always returns the same name and datatype. You should be able to do that using aliases and casting in the sql.

Reference:

https://stackoverflow.com/a/9987939/150342

edited May 23 '17 at 10:10

Community

1
1

answered Mar 18 '15 at 15:54

Colin

22,328
17
103
197

Thanks Colin,It makes sense,and looks viable too, let me implement it and will get back to you – Allen Mar 19 '15 at 07:24
I did implement it and it worked,now I can just use the DTO to receive the data a stored procedure returning the columns **id** and **description** always 'public class FilterProcedureDTO { public FilterProcedureDTO() { } public int id { get; set; } public string description { get; set; } }'
– Allen Mar 19 '15 at 13:06

Entity Framework: Executing a stored procedure that returns different columns and column data types

1 Answers1