title: Generic method to execute stored procedures and map result to a DTO class
body: <p>I'm starting a web application that is gonna work mostly with stored procedures(SPs).</p>
  <p>I would like to highlight this question:
  <strong>Which is the best approach to execute SPs and map the result to a custom class (DTO)?
  Is reflection a bad approach for mapping?</strong></p>
  <p>I'm using EF core 3.1.3 database first, a DB with all my tables and SPs. I tried the next approach but I read that it's not good for the application performance due to I'm using <em>reflection</em> to map the DataReader to a class.</p>
  <pre><code>public List&lt;T&gt; ExecuteStoredProcedure&lt;T&gt;(string storedProcedure, List&lt;SqlParameter&gt; parameters) where T : new()
  {
          using (var command = context.Database.GetDbConnection().CreateCommand())
          {
              command.CommandText = storedProcedure;
              command.CommandType = CommandType.StoredProcedure;
  
              // set some parameters of the stored procedure
              foreach (var parameter in parameters)
              {
                  parameter.Value = parameter.Value ?? DBNull.Value;
                  command.Parameters.Add(parameter);
              }
  
              if (command.Connection.State != ConnectionState.Open)
                  command.Connection.Open();
  
              using (var dataReader = command.ExecuteReader())
              {
                  var entity = DataReaderMapToList&lt;T&gt;(dataReader);
                  return entity;
              }
          }
  }
  
  private List&lt;T&gt; DataReaderMapToList&lt;T&gt;(DbDataReader dr)
  {
              List&lt;T&gt; list = new List&lt;T&gt;();
  
              if (dr.HasRows)
              {
                  while (dr.Read())
                  {
                      var obj = Activator.CreateInstance&lt;T&gt;();
                      foreach (PropertyInfo prop in obj.GetType().GetProperties())
                      {
                          if (dr.HasColumn(prop.Name) &amp;&amp; !Equals(dr[prop.Name], DBNull.Value))
                          {
                              prop.SetValue(obj, dr[prop.Name], null);
                          }
                      }
                      list.Add(obj);
                  }
                  return list;
              }
              return new List&lt;T&gt;();
  }
  </code></pre>
  <p>On the other hand, I'm using EF Core for the tables and simple queries.</p>
  <p>I've been thinking of using EF to execute the SPs, but I couldn't find a good solution to make it generic and easy to map.</p>
  <p>I'm open to different ideas. Thanks!</p>