Return stored procedure data as table set

Question

I am using .net with entity framework database first approeach. I was wondering if it's possible for stored procedure to return result as table set instead of procedure result set.

Suppose I have a table:

table MyTable(
id int, 
value nvarchar(max))

And stored procedure:

procedure GetMyTableSet
AS
  Select *
  From MyTable A
  Where A.Value = 'test'

Now the type that the procedure returns in .net will be GetMyTableSet_Result, but is it possible for it to be MyTable because it has the same data structure? Can I cast it in procedure somehow or do something else?

EDIT: In .net my procedure is generated like this:

public virtual ObjectResult<GetMyTableSet_Result> GetMyTableSet(string accessRight, Nullable<int> companyId, string accessParam, Nullable<bool> multipleCompanies)
        {
            var accessRightParameter = accessRight != null ?
                new ObjectParameter("accessRight", accessRight) :
                new ObjectParameter("accessRight", typeof(string));

            var companyIdParameter = companyId.HasValue ?
                new ObjectParameter("companyId", companyId) :
                new ObjectParameter("companyId", typeof(int));

            var accessParamParameter = accessParam != null ?
                new ObjectParameter("accessParam", accessParam) :
                new ObjectParameter("accessParam", typeof(string));

            var multipleCompaniesParameter = multipleCompanies.HasValue ?
                new ObjectParameter("multipleCompanies", multipleCompanies) :
                new ObjectParameter("multipleCompanies", typeof(bool));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<GetMyTableSet_Result>("GetMyTableSet", accessRightParameter, companyIdParameter, accessParamParameter, multipleCompaniesParameter);
        }

What I wanted to know if it's possible for it to be like ObjectResult<MyTable> instead of ObjectResult<GetMyTableSet_Result> as the procedure returns the same data structure as it is for MyTable table.

Peter Smith · Answer 1 · 2015-12-29T11:05:45.000

0

You are returning an entity which can be stored in an object having any name you like. You could also use a VIEW rather than a stored procedure.

In code terms and using EF contexts:

MyEntity dbEntity = new MyEntity();
var MyTable = dbEntity.GetMyTableSet;

EF will do the mapping of data types so the returned object will mirror the types returned by the stored procedure (unless you use CAST or CONVERT to change the column type.

edited Dec 29 '15 at 11:05

answered Dec 29 '15 at 10:45

Peter Smith

5,528
8
51
77

What I gave as an example was just to show what I need to do, which is - use stored procedure. The real procedure is more complex and I could not use view there. And I am not talking about entity name, but type. I hope I made myself clear. – Jānis Dec 29 '15 at 11:01
@John I've modified the answer, I'm unclear as to what you're actual problem is. Do you have sample C#, EF code showing the problem? – Peter Smith Dec 29 '15 at 11:07
I edited my post, hope it's more clearer now. But the more I think about it the more it seems like I am asking for something impossible / incorrect. – Jānis Dec 29 '15 at 11:19

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

You can take a look here to understand how to call procedures from Entity Framework. EF will do all the mappings between result set column values and properties based on column name, but you must take care of data type compatibility.

Also, I think you can control the mappings by specifying that some properties should not be mapped.

edited May 23 '17 at 12:31

Community

1
1

answered Dec 29 '15 at 11:13

Alexei - check Codidact

22,016
16
145
164

Return stored procedure data as table set

2 Answers2