5

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.

Community
  • 1
  • 1
Rafael Herscovici
  • 16,558
  • 19
  • 65
  • 93
  • 1
    Do you have to use the EDMX file? If it's regenerated you're going to lose to customizations. – Mike Cole Jun 20 '14 at 21:34
  • i don't HAVE to use it, but that still does not solve the issue at hand. – Rafael Herscovici Jun 25 '14 at 13:05
  • I just realized this was MySQL, not MSSQL, but I recently made this work with MSSQL using the "Accessing Multiple Result Sets with Code" section at http://msdn.microsoft.com/en-us/data/jj691402.aspx. – Mike Cole Jun 25 '14 at 14:05

1 Answers1

4

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.

Yasser Shaikh
  • 46,934
  • 46
  • 204
  • 281
  • this is interesting, in the article i have added, they say nothing about this and since i do have access to those properties it seemed like they already mapped mapped since the complex type already exist – Rafael Herscovici Jun 25 '14 at 13:11