Entity Framework - Stored Procedure with Multiple results sets - no rows return

Question

so, i was trying to implement a stored procedure with multiple results sets in entity framework. it all seemed to be too easy. but, i am not getting any rows for my results sets (even though, it seems i do get the result sets themselves back).

What i have done:

1. created the stored procedure which returns 3 result sets
2. Created the complex type that represents the return values
3. manually edited the edmx file as per Stored Procedures with Multiple Result Sets
4. Failed with 3 and tried the code version from the same page, still no rows back.
5. Reverted the code back to 3.

my edmx file (related content only):

  <FunctionImport Name="getGlobalReport2">
    <ReturnType Type="Collection(MTModel.GlobalReport2)"/>
    <ReturnType Type="Collection(MTModel.GlobalReport2)"/>
    <ReturnType Type="Collection(MTModel.GlobalReport2)"/>
    <Parameter Name="regions" Mode="In" Type="String" />
    <Parameter Name="countries" Mode="In" Type="String" />
    <Parameter Name="companySizes" Mode="In" Type="String" />
    <Parameter Name="products" Mode="In" Type="String" />
  </FunctionImport>

  <FunctionImportMapping FunctionImportName="getGlobalReport2" FunctionName="MTModel.Store.getGlobalReport2" >
    <ResultMapping>
      <ComplexTypeMapping TypeName="MTModel.GlobalReport2" />
    </ResultMapping>
    <ResultMapping>
      <ComplexTypeMapping TypeName="MTModel.GlobalReport2" />
    </ResultMapping>
    <ResultMapping>
      <ComplexTypeMapping TypeName="MTModel.GlobalReport2" />
    </ResultMapping>
  </FunctionImportMapping>

my code:

var x = mtEntities.getGlobalReport2(regions, countries, companySizes, products);
Response.Write(x.Count());

var y = x.GetNextResult<GlobalReport2>();
Response.Write(y.Count());

var z = x.GetNextResult<GlobalReport2>();

What i have allready checked:

1. Checked that the server receives the request as per How can I view live MySQL queries?
2. Run the query i grabbed from the server and made sure it returns result sets and rows
3. Debug the app to see there are no Exceptions i missed on the way

There seems to be no issue with the call, or the app, except that no rows are returned. Any suggestions?

EDIT: as per your comments about the edmx being overwritten, that would happen only if i regenerate the model from the database, not if i update it. i wouldn't expect anything else, since its regenerating the model.

Answer 1

Dont you think you should have some property defined for your complex types you have created ? For example:

<FunctionImportMapping FunctionImportName="GetGrades" 
                       FunctionName="SchoolModel.Store.GetGrades" >
  <ResultMapping>
    <ComplexTypeMapping TypeName="SchoolModel.GradeInfo">
      <ScalarProperty Name="EnrollmentID" ColumnName="enroll_id"/>
      <ScalarProperty Name="CourseID" ColumnName="course_id"/>
      <ScalarProperty Name="StudentID" ColumnName="student_id"/>
      <ScalarProperty Name="Grade" ColumnName="grade"/>
    </ComplexTypeMapping>
  </ResultMapping>
</FunctionImportMapping>

Check this too

Also as rightly stated by Mike in the comment to your question, if you in future update your edmx file, if it's regenerated you're going to lose to customizations.