Map columns to arrays in Dapper

Question

I have a database table with a lot of columns forming something like array:

Create table MyTable
(
  Id int,
  SomeColumn varchar,
  OtherColumn varchar,
  x1 double,
  x2 double,
  x3 double,
  x4 double,
  ...
  x50 double
)

Now, I have c# class that represent this data:

class MyClass
{
  public int Id;
  public string SomeColumn;
  public string OtherColumn;
  public double[] x;
}

And my question is: is there a way to use Dapper to fetch such data from database? I like to use Dapper, as it able to automaticaly map columns to fields based on their name, but can it handle data forming arrays?

And no, I cannot change the database schema. It is legacy software and db must remain unchanged.

But your data `x` isn't an array? It's a series of columns called x1, x2...etc — Liam, Jul 16 '20 at 10:39
Is there anything stopping you using brute force. Yeah, it's a bit more typing, but you only need to do it once, right? — Neil, Jul 16 '20 at 10:48
The simple answer is no - Dapper cannot map multiple columns to a single property of a class. If you really want to have it as an array property you could write method that adds each of the properties to an array for convenient use later. — Matthew Winfield, Jul 16 '20 at 10:51
Hi nowylepszyszatan, maybe this example could serve you https://dapper-tutorial.net/knowledge-base/32511044/return-array-from-db- — Goodies, Jul 16 '20 at 10:57
No, this example is using two db tables with relation. I won't work here, as I cannot do anything on database. — nowylepszyszatan, Jul 16 '20 at 11:04

score 2 · Answer 1 · answered Jul 23 '20 at 14:30

Yep, you can do this by defining how the object is mapped.

You'd need to make a slight change to your model to store the x values in a list rather than an array:

public class MyClass
{
    public int Id;
    public string SomeColumn;
    public string OtherColumn;
    public List<double> x = new List<double>();
}

Then when you're getting the data, you can map it:

List<MyClass> results = connection
    .Query("SELECT * FROM MyTable", commandType: CommandType.Text)
    .Select(x =>
    {
        var result = new MyClass 
        { 
            Id = x.Id,
            OtherColumn = x.OtherColumn, 
            SomeColumn = x.SomeColumn
        };

        foreach (var element in x)
        {
            if (element.Key.StartsWith("x"))
            {
                result.x.Add((double)element.Value);
            }
        }
        return result;
    }).ToList();

You should end up with a list of items, where all the x values are in that list.

Map columns to arrays in Dapper

1 Answers1