How to get values from multiple columns as IEnumerable using SqlKata (Dapper)

Question

I'm using SQL and I have a table shown below in my DB.

Id   Remark1    Remark2    Remark3    Remark4    
------------------------------------------------
1    AAA        BBB        CCC        DDD      
2    EEE        FFF        GGG        HHH   

How can I get all the values from Remark* columns as IEnumerable<string> using the following DTO?

class MyDTO
{
   public int ID { get; }
   public IEnumerable<string> Remarks { get; }
}

NOTE: I'm using SqlKata (Dapper), which you can also use when answering.

Answer 1

If you're using Dapper, maybe just use the non-generic Query API. This returns each row as dynamic, but this can also be cast to IDictionary<string,object>, which allows you to access each named column (for example, via foreach).

foreach (IDictionary<string, object> row in conn.Query(sql, args))
{
    var obj = new MyDto();
    var vals = new List<string>();
    obj.Remarks = list;
    foreach ((var key, var value) in row)
    {
        if (key == nameof(obj.Id))
            obj.Id = (int)value;
        else
            vals.Add((string)value);
    }
    // do something with obj
}
```

Answer 2

Another option would be to create an ugly looking - but working - UNION ALL query:

SELECT Id, Remark1 as Remark FROM Table
UNION ALL
SELECT Id, Remark2 as Remark FROM Table
UNION ALL
SELECT Id, Remark... as Remark FROM Table

That's can be done in a for..loop using SqlKata, calling the Union inside the loop

https://sqlkata.com/docs/combine#union-except-intersect

Non-Tested code sample:

var q = new Query("Table").Select("Id", "Remark1 as Remark");
for(int r=2;r<=50;i++)
{
  var u = new Query("Table").Select("Id", $"Remark{r} as Remark");
  q = q.Union(u);
}