How can I map an array property to a delimited string db field using EF?

Question

I have an object with an array property that I want to persist in the database as a delimited string. How to I Map that property to the field in the database and vice versus?

public class User() {
  public int Id { get; set; }
  public string[] Roles { get; set; }
}

Incomplete Config Class:

public class UserConfig : EntityTypeConfiguration<User> {
  public UserConfig() {
    this.Property(u => u.Roles).__???__
    this.Map(u => u.Properties<string[]>(r => r.Roles).__???__))
      .HasColumnName("roles");
  }
}

For this example the "Roles" property would be converted to "roleA,roleB,roleC" when going to the database and then transformed back to an array when read from the database. Is there an on data mapping event somewhere?

Answer 1

You need an additional property that wraps and converts the String into a String[].

public class User() {
  public int Id { get; set; }
  public string Roles { get; set; }
  public string[] RolesArray 
  { 
    get
    {
      return Roles.Split(',').ToArray();
    }
    set
    {
      Roles = String.Join(',', value);
    }
  }
}

The preferred solution would of course be to add a new table to your database called Role, and have a one-many relationship so that a User has many Roles. This will allow EF to manage everything for you, and means your data is stored coherently and accessibly. Comma delimited strings are not particularly pleasant to work with and shouldn't be stored in databases.

Answer 2

You have to provide additional property which will implement conversion logic between string and array. You will map that property and ignore Roles property.