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.

Are you looking to map just those 4 columns to a collection, or does your real use case have many more columns to deal with? — John H, Oct 04 '20 at 14:38

Marc Gravell · Accepted Answer · 2020-10-04T15:01:14.587

5

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
}
```

edited Oct 04 '20 at 15:01

answered Oct 04 '20 at 14:56

Marc Gravell

1,026,079
266
2,566
2,900

Thank you very much for your help. This is almost exactly what I've been looking for. – user4134476 Oct 24 '20 at 05:34

score 0 · Answer 2 · answered Oct 04 '20 at 19:00

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);
}

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

2 Answers2