Comma seperated in list of strings c# and dapper

Question

I have a class which has this property among others:

public class ADUser
{
    public IList<string> Relations { get; set; }
}

In my database table, Relations is a comma-separated string column.

I am using Dapper to get data.

This is my code:

public ADUser[] GetWithGroupsAndRelations()
{
        using (var connection = ConnectionFactory.CreateConnection())
        {
            int numberOfUsersProcessed = 0;
            _log.LogInfo("Getting users from database...");

            var users = connection.GetList<ADUser>().ToArray();
            //var users = connection.GetList<ADUser>().Take(1000).OrderBy(x => x.Id).ToArray();

            _log.LogInfo($"Done getting {users.Count()} users from database...");

            return users;
        }
}

How can I map the comma-separated Relations column to a list of strings while reading the data?

Thanks. :)

this was added by an old developer. This is an old database we are talking about. — mohsinali1317, Sep 13 '19 at 13:06
probably in the ADUser object -- part of the constructor maybe -- if you have to update or save you would need special code for that too. drapper is not really designed to do these transformations but you can do it. a lot of the design depends on the rest of your program, how you use the list, how often etc. — Hogan, Sep 13 '19 at 13:12

score 1 · Answer 1 · answered Sep 13 '19 at 13:27

You should be able to do this without using any extensions (untested!):

ADUser[] users = connection.Query("SELECT Relations FROM AdUser")
    .Select(x => new ADUser() { Relations = ((string)x.Relations)?.Split(new char[1] { ',' })?.ToList() })
    .ToArray();

Comma seperated in list of strings c# and dapper

1 Answers1