title: How to map set of results from stored procedure in postresql where I return multiple cursors using Entity Framework Core
body: <p>I was working on stored procedure to get some statistics from database, but I do not want to do it in code side because there is lots of data to process and there are some tables I would need to call after execution, well lots of stuff that will use resources and can be made cheaply by database query. So I came up with stored procedure that looks like:</p>
  <pre><code>CREATE OR REPLACE FUNCTION PUBLIC.get_statistics_info(severityref refcursor, eventsref refcursor) 
      RETURNS SETOF refcursor 
      LANGUAGE 'plpgsql'
  AS $BODY$ 
                                  
  BEGIN
      OPEN severityref FOR 
          SELECT 
              si.&quot;VarId&quot;, si.&quot;Date&quot;, si.&quot;SystemId&quot;, si.&quot;SystemName&quot;, 
              SUM(ss.&quot;InfoCounter&quot;) AS info, SUM(ss.&quot;WarningCounter&quot;) AS warn, SUM(ss.&quot;NonFatalCounter&quot;) AS nfatal, SUM(ss.&quot;FatalCounter&quot;) AS fatal,
              SUM(ssm.&quot;InfoCounter&quot;) AS minfo, SUM(ssm.&quot;WarningCounter&quot;) AS mwarn, SUM(ssm.&quot;NonFatalCounter&quot;) AS nfatal, SUM(ssm.&quot;FatalCounter&quot;) AS mfatal
          FROM &quot;StatisticsInfo&quot; AS si
          left JOIN &quot;StatisticsSeverity&quot; AS ss ON si.&quot;Id&quot; = ss.&quot;StatisticsInfoId&quot;
          left JOIN &quot;StatisticsSeverityMaintenance&quot; AS ssm ON si.&quot;Id&quot; = ssm.&quot;StatisticsInfoId&quot;
          GROUP BY si.&quot;VarId&quot;, si.&quot;Date&quot;, si.&quot;SystemId&quot;, si.&quot;SystemName&quot;; 
      RETURN NEXT severityref;                                                                            
  
      OPEN eventsref for 
          SELECT 
              si.&quot;VarId&quot;, si.&quot;SystemId&quot; ,
              se.&quot;EventId&quot; AS eventid, se.&quot;Counter&quot; AS eventcounter, sed.&quot;Symbol&quot; AS eventsymbol,
              sem.&quot;EventId&quot; AS mseventid, sem.&quot;Counter&quot; AS mseventcounter, msed.&quot;Symbol&quot; AS meventsymbol
          FROM &quot;StatisticsInfo&quot; AS si
          left JOIN &quot;StatisticsEvent&quot; AS se ON si.&quot;Id&quot; = se.&quot;StatisticsInfoId&quot;
          LEFT JOIN &quot;EventDefinitions&quot; AS sed ON se.&quot;EventId&quot; = sed.&quot;Decimal&quot;
          left JOIN &quot;StatisticsEventMaintenance&quot; AS sem ON si.&quot;Id&quot; = sem.&quot;StatisticsInfoId&quot;
          LEFT JOIN &quot;EventDefinitions&quot; AS msed ON sem.&quot;EventId&quot; = msed.&quot;Decimal&quot;;  
      RETURN NEXT eventsref;   
  END
  $BODY$;
  </code></pre>
  <p>It sums up statistics using 2 cursors, so I should get 2 sets of data as a result. But now I have a problem how to recevie it. I am using entity framework core for my db operations, so I made something like this.</p>
  <p>Service Call:</p>
  <pre><code>    public async ValueTask&lt;IEnumerable&lt;object&gt;&gt; GetCombinedStatisticsInfo()
      {
          try
          {
              using var unitOfWork = UnitOfWorkFactory.CreateGeneralContext();
  
              //I MADE THIS MAPPING AS TEST TO CHECK WHAT WILL BE RETURNED
              var map = new Func&lt;DbDataReader, object&gt;(reader =&gt; new
              {
                  first = reader[0]
              });
  
              const string procedureCall = &quot;BEGIN; SELECT get_statistics_info('severity', 'events'); FETCH ALL IN \&quot;severity\&quot;; FETCH ALL IN \&quot;events\&quot;; COMMIT;&quot;;
              var result = await unitOfWork.GeneralRepository.ExecuteSqQuery(procedureCall, map);
              unitOfWork.Dispose();
              return result;
          }
          catch (Exception ex)
          {
              logger.Error(ex);
              throw;
          }
      }
  </code></pre>
  <p>Repository:</p>
  <pre><code>    public async ValueTask&lt;IEnumerable&lt;TResponse&gt;&gt; ExecuteSqlProcedure&lt;TResponse&gt;(string procedureCall, Func&lt;DbDataReader, TResponse&gt; map)
      {
          var transaction = await this.Context.Database.BeginTransactionAsync();
          var result = await ExecuteSql(procedureCall, map, CommandType.StoredProcedure);
          await transaction.CommitAsync();
          return result;
      }
  
      private async ValueTask&lt;IEnumerable&lt;TResponse&gt;&gt; ExecuteSql&lt;TResponse&gt;(string query, Func&lt;DbDataReader, TResponse&gt; map, CommandType commandType)
      {
          await using var command = this.Context.Database.GetDbConnection().CreateCommand();
          command.CommandText = query;
          command.CommandType = commandType;
  
          await this.Context.Database.OpenConnectionAsync();
          await using var result = await command.ExecuteReaderAsync();
          var entities = new List&lt;TResponse&gt;();
  
          while (await result.ReadAsync())
          {
              entities.Add(map(result));
          }
  
          return entities;
      }
  </code></pre>
  <p>Well basically it executes procedure but as a result I am getting cursor names like: {first: &quot;severity&quot;; first: &quot;events&quot;}, so I checked execution on my database and it works i get 2 sets of data using that transaction with fetch. So my question is what am I doing wrong here?</p>