Mapping List<KeyValuePair<string,int>> to custom object in C#

Question

I have a method that returns List<KeyValuePair<string,int>>. These are column data from different tables in a database depending of the parameters of the call and I would like map then to custom objects depending on the table data returned.

As an example is the return list contain data from table 1 with column , (C1, C2, C3, C4)

The possible returned values are

KeyValPair<C1, ValueOfC1>
KeyValPair<C2, ValueofC2>
etc....

This list should be mapped to a list of CustomC1 where CustomC1 is defined as follow:

public class CustumC1 { int c1, int c2, int c3, int c4 }

I need to transform the list of the KeyValuePair to the List of CustomC1.

So I currently have a method definition which is

List<KeyValuePair<string,int>> MyMethod(int param)

and I want

List<TResult> MyMethod<TResult> (int param)

below is my original method that needs to be changed to return a supplied object:

 private async Task<List<List<KeyValuePair<string, string>>>> GetData(SqlConnection conn)
        {
            List<string> columnNames = new List<string>();
            string sql = CustomQueryString;
            List<List<KeyValuePair<string, string>>> result = new List<List<KeyValuePair<string, string>>>();
            var reader = await new SqlCommand(sql, conn).ExecuteReaderAsync();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                columnNames.Add(reader.GetName(i));
            }

            while (reader.Read())
            {
                List<KeyValuePair<string, string>> resultRecord = new List<KeyValuePair<string, string>>();
                foreach (var columnName in columnNames)
        {
            resultRecord.Add(new KeyValuePair<string, string>(key: columnName, value: reader[columnName].ToString()));
        }
        result.Add(resultRecord);
    }
    return result;
}

Any help will be highly appreciated.

Answer 1

What are you using to read from the database? And how do you map to a List<KeyValuePair<string,int>> ?, As far as I understand you are mapping to a keyValuePair to later map it to a generic object. I do not think this is a good approach as you are opening yourself to a lot more complexity with scalability and errors. Better to have a concrete method that return a concret object from the database, as mention in the comments using dapper would be a good solution. Otherwise you can do something like this:

    var modelProperties = modelType.GetProperties();// modelType is your generic object type
    var instance = Activator.CreateInstance(modelType); 

foreach (var property in modelProperties)
{
  var propertyInfo = instance.GetType().GetProperty(property.Name);
  var propertyValue = yourList.FirstOrDefault(x=>x.Key==property.Name);
  propertyInfo.SetValue(instance, ChangeType(propertyValue, propertyInfo.PropertyType), null);       
}

UPDATE

For example this code is clear and expressive and tells me what the other developer is doing and I can understand the context of it, it is using Dapper to query and made in CQRS principles:

 public class GetHousesHandler : IRequestHandler<GetHouses, IEnumerable<House>>
    {
        private readonly IDbConnection _connection;

        public GetHousesHandler(IDbConnection connection)
        {
            _connection = connection ?? throw new ArgumentNullException(nameof(connection));
        }

        public async Task<IEnumerable<House>> Handle(GetHouses request, CancellationToken cancellationToken)
        {
            var sql = $@"
                SELECT StreetName AS {nameof(House.StreetName)}
                  , Number AS {nameof(House.Number)}
                FROM dbo.HouseView
                WHERE Number = @Number;";

            var param = new
            {
                request.Number,
            };

            return await _connection.QueryAsync<House>(
                sql: sql,
                param: param
                );
        }
    }