How to persist a list of strings with Entity Framework Core?

Question

Let us suppose that we have one class which looks like the following:

public class Entity
{
    public IList<string> SomeListOfValues { get; set; }

    // Other code
}

Now, suppose we want to persist this using EF Core Code First and that we are using a RDMBS like SQL Server.

One possible approach is obviously to create a wraper class Wraper which wraps the string:

public class Wraper
{
    public int Id { get; set; }

    public string Value { get; set; }
}

And to refactor the class so that it now depends on a list of Wraper objects. In that case EF would generate a table for Entity, a table for Wraper and stablish a "one-to-many" relation: for each entity there is a bunch of wrapers.

Although this works, I don't quite like the approach because we are changing a very simple model because of persistence concerns. Indeed, thinking just about the domain model, and the code, without the persistence, the Wraper class is quite meaningless there.

Is there any other way persist one entity with a list of strings to a RDBMS using EF Core Code First other than creating a wraper class? Of course, in the end the same thing must be done: another table must be created to hold the strings and a "one-to-many" relationship must be in place. I just want to do this with EF Core without needing to code the wraper class in the domain model.

Answer 1

This can be achieved in a much more simple way starting with Entity Framework Core 2.1. EF now supports Value Conversions to specifically address scenarios like this where a property needs to be mapped to a different type for storage.

To persist a collection of strings, you could setup your DbContext in the following way:

protected override void OnModelCreating(ModelBuilder builder)
{
    var splitStringConverter = new ValueConverter<IEnumerable<string>, string>(v => string.Join(";", v), v => v.Split(new[] { ';' }));
    builder.Entity<Entity>()
           .Property(nameof(Entity.SomeListOfValues))
           .HasConversion(splitStringConverter);
} 

Note that this solution does not litter your business class with DB concerns.

Needless to say that this solution, one would have to make sure that the strings cannot contains the delimiter. But of course, any custom logic could be used to make the conversion (e.g. conversion from/to JSON).

Another interesting fact is that null values are not passed into the conversion routine but rather handled by the framework itself. So one does not need to worry about null checks inside the conversion routine. However, the whole property becomes null if the database contains a NULL value.

What about Value Comparers?

Creating a migration using this converter leads to the following warning:

The property 'Entity.SomeListOfValues' is a collection or enumeration type with a value converter but with no value comparer. Set a value comparer to ensure the collection/enumeration elements are compared correctly.

Setting the correct comparer for the suggested converter depends on the semantics of your list. For example, if you do not care about the order of its elements, you can use the following comparer:

new ValueComparer<IEnumerable<string>>(
    (c1, c2) => new HashSet<string>(c1!).SetEquals(new HashSet<string>(c2!)),
    c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
    c => c.ToList()
);

Using this comparer, a reordered list with the same elements would not be detected as changed an thus a roundtrip to the database can be avoided. For more information on the topic of Value Comparers, consider the docs.

UPDATE EF CORE 6.0

In order to benefit from Entity Framework Core 6.0 Compiled Models, we can use the generic overload of HasConversion. So the full picture becomes:

builder.Entity<Foo>()
    .Property(nameof(Foo.Bar))
    .HasConversion<SemicolonSplitStringConverter, SplitStringComparer>();

...

public class SplitStringComparer : ValueComparer<IEnumerable<string>>
{
    public SplitStringComparer() : base(
        (c1, c2) => new HashSet<string>(c1!).SetEquals(new HashSet<string>(c2!)),
        c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())))
    {
    }
}

public abstract class SplitStringConverter : ValueConverter<IEnumerable<string>, string>
{
    protected SplitStringConverter(char delimiter) : base(
        v => string.Join(delimiter.ToString(), v),
        v => v.Split(new[] { delimiter }, StringSplitOptions.RemoveEmptyEntries))
    {
    }
}

public class SemicolonSplitStringConverter : SplitStringConverter
{
    public SemicolonSplitStringConverter() : base(';')
    {
    }
}

Answer 2

You could use the ever useful AutoMapper in your repository to achieve this while keeping things neat.

Something like:

MyEntity.cs

public class MyEntity
{
    public int Id { get; set; }
    public string SerializedListOfStrings { get; set; }
}

MyEntityDto.cs

public class MyEntityDto
{
    public int Id { get; set; }
    public IList<string> ListOfStrings { get; set; }
}

Set up the AutoMapper mapping configuration in your Startup.cs:

Mapper.Initialize(cfg => cfg.CreateMap<MyEntity, MyEntityDto>()
  .ForMember(x => x.ListOfStrings, opt => opt.MapFrom(src => src.SerializedListOfStrings.Split(';'))));
Mapper.Initialize(cfg => cfg.CreateMap<MyEntityDto, MyEntity>()
  .ForMember(x => x.SerializedListOfStrings, opt => opt.MapFrom(src => string.Join(";", src.ListOfStrings))));

Finally, use the mapping in MyEntityRepository.cs so that your business logic doesnt have to know or care about how the List is handled for persistence:

public class MyEntityRepository
{
    private readonly AppDbContext dbContext;
    public MyEntityRepository(AppDbContext context)
    {
        dbContext = context;
    }

    public MyEntityDto Create()
    {
        var newEntity = new MyEntity();
        dbContext.MyEntities.Add(newEntity);

        var newEntityDto = Mapper.Map<MyEntityDto>(newEntity);

        return newEntityDto;
    }

    public MyEntityDto Find(int id)
    {
        var myEntity = dbContext.MyEntities.Find(id);

        if (myEntity == null)
            return null;

        var myEntityDto = Mapper.Map<MyEntityDto>(myEntity);

        return myEntityDto;
    }

    public MyEntityDto Save(MyEntityDto myEntityDto)
    {
        var myEntity = Mapper.Map<MyEntity>(myEntityDto);

        dbContext.MyEntities.Save(myEntity);

        return Mapper.Map<MyEntityDto>(myEntity);
    }
}

Answer 3

You are right, you do not want to litter your domain model with persistence concerns. The truth is, if you use your same model for your domain and persistence, you will not be able to avoid the issue. Especially using Entity Framework.

The solution is, build your domain model without thinking about the database at all. Then build a separate layer which is responsible for the translation. Something along the lines of the 'Repository' pattern.

Of course, now you have twice the work. So it is up to you to find the right balance between keeping your model clean and doing the extra work. Hint: The extra work is worth it in bigger applications.

Answer 4

This might be late, but you can never tell who it might help. See my solution based on the previous answer

First, you are going to need this reference using System.Collections.ObjectModel;

Then extend the ObservableCollection<T> and add an implicit operator overload for a standard list

 public class ListObservableCollection<T> : ObservableCollection<T>
{
    public ListObservableCollection() : base()
    {

    }


    public ListObservableCollection(IEnumerable<T> collection) : base(collection)
    {

    }


    public ListObservableCollection(List<T> list) : base(list)
    {

    }
    public static implicit operator ListObservableCollection<T>(List<T> val)
    {
        return new ListObservableCollection<T>(val);
    }
}

Then create an abstract EntityString class (This is where the good stuff happens)

public abstract class EntityString
{
    [NotMapped]
    Dictionary<string, ListObservableCollection<string>> loc = new Dictionary<string, ListObservableCollection<string>>();
    protected ListObservableCollection<string> Getter(ref string backingFeild, [CallerMemberName] string propertyName = null)
    {


        var file = backingFeild;
        if ((!loc.ContainsKey(propertyName)) && (!string.IsNullOrEmpty(file)))
        {
            loc[propertyName] = GetValue(file);
            loc[propertyName].CollectionChanged += (a, e) => SetValue(file, loc[propertyName]);
        }
        return loc[propertyName];
    }

    protected void Setter(ref string backingFeild, ref ListObservableCollection<string> value, [CallerMemberName] string propertyName = null)
    {

        var file = backingFeild;
        loc[propertyName] = value;
        SetValue(file, value);
        loc[propertyName].CollectionChanged += (a, e) => SetValue(file, loc[propertyName]);
    }

    private List<string> GetValue(string data)
    {
        if (string.IsNullOrEmpty(data)) return new List<string>();
        return data.Split(';').ToList();
    }

    private string SetValue(string backingStore, ICollection<string> value)
    {

        return string.Join(";", value);
    }

}

Then use it like so

public class Categorey : EntityString
{

    public string Id { get; set; }
    public string Name { get; set; }


   private string descriptions = string.Empty;

    public ListObservableCollection<string> AllowedDescriptions
    {
        get
        {
            return Getter(ref descriptions);
        }
        set
        {
            Setter(ref descriptions, ref value);
        }
    }


    public DateTime Date { get; set; }
}

Answer 5

Extending on the already accepted answer of adding a ValueConverter within the OnModelCreating; you can have this map out for all entities rather than just explicit ones, and you can support storing delimiting characters:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var property in entity.ClrType.GetProperties())
        {
            if (property.PropertyType == typeof(List<string>))
            {
                modelBuilder.Entity(entity.Name)
                    .Property(property.Name)
                    .HasConversion(new ValueConverter<List<string>, string>(v => JsonConvert.SerializeObject(v), v => JsonConvert.DeserializeObject<List<string>>(v)));
            }
        }
    }
}

So the end result is a serialized array of strings in the database. This approach can also work on other serializable types as well (Dictionary<string, string>, simple DTO or POCO objects...

There is a purist deep down somewhere in me that is mad about persisting seralized data into a database, but I have grown to ignore it every once and a while.

Answer 6

I implemented a possible solution by creating a new StringBackedList class, where the actual list content is backed by a string. It works by updating the backing string whenever the list is modified, using Newtonsoft.Json as the serializer (because I already use that in my project, but any would work).

You use the list like this:

public class Entity
{
    // that's what stored in the DB, and shouldn't be accessed directly
    public string SomeListOfValuesStr { get; set; }

    [NotMapped]
    public StringBackedList<string> SomeListOfValues 
    {
        get
        {
            // this can't be created in the ctor, because the DB isn't read yet
            if (_someListOfValues == null)
            {
                 // the backing property is passed 'by reference'
                _someListOfValues = new StringBackedList<string>(() => this.SomeListOfValuesStr);
            }
            return _someListOfValues;
        }
    }
    private StringBackedList<string> _someListOfValues;
}

Here's the implementation of the StringBackedList class. For ease of use, the backing property is passed by reference, using this solution.

using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Reflection;

namespace Model
{
    public class StringBackedList<T> : IList<T>
    {
        private readonly Accessor<string> _backingStringAccessor;
        private readonly IList<T> _backingList;

        public StringBackedList(Expression<Func<string>> expr)
        {
            _backingStringAccessor = new Accessor<string>(expr);

            var initialValue = _backingStringAccessor.Get();
            if (initialValue == null)
                _backingList = new List<T>();
            else
                _backingList = JsonConvert.DeserializeObject<IList<T>>(initialValue);
        }

        public T this[int index] {
            get => _backingList[index];
            set { _backingList[index] = value; Store(); }
        }

        public int Count => _backingList.Count;

        public bool IsReadOnly => _backingList.IsReadOnly;

        public void Add(T item)
        {
            _backingList.Add(item);
            Store();
        }

        public void Clear()
        {
            _backingList.Clear();
            Store();
        }

        public bool Contains(T item)
        {
            return _backingList.Contains(item);
        }

        public void CopyTo(T[] array, int arrayIndex)
        {
            _backingList.CopyTo(array, arrayIndex);
        }

        public IEnumerator<T> GetEnumerator()
        {
            return _backingList.GetEnumerator();
        }

        public int IndexOf(T item)
        {
            return _backingList.IndexOf(item);
        }

        public void Insert(int index, T item)
        {
            _backingList.Insert(index, item);
            Store();
        }

        public bool Remove(T item)
        {
            var res = _backingList.Remove(item);
            if (res)
                Store();
            return res;
        }

        public void RemoveAt(int index)
        {
            _backingList.RemoveAt(index);
            Store();
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return _backingList.GetEnumerator();
        }

        public void Store()
        {
            _backingStringAccessor.Set(JsonConvert.SerializeObject(_backingList));
        }
    }

    // this class comes from https://stackoverflow.com/a/43498938/2698119
    public class Accessor<T>
    {
        private Action<T> Setter;
        private Func<T> Getter;

        public Accessor(Expression<Func<T>> expr)
        {
            var memberExpression = (MemberExpression)expr.Body;
            var instanceExpression = memberExpression.Expression;
            var parameter = Expression.Parameter(typeof(T));
            if (memberExpression.Member is PropertyInfo propertyInfo)
            {
                Setter = Expression.Lambda<Action<T>>(Expression.Call(instanceExpression, propertyInfo.GetSetMethod(), parameter), parameter).Compile();
                Getter = Expression.Lambda<Func<T>>(Expression.Call(instanceExpression, propertyInfo.GetGetMethod())).Compile();
            }
            else if (memberExpression.Member is FieldInfo fieldInfo)
            {
                Setter = Expression.Lambda<Action<T>>(Expression.Assign(memberExpression, parameter), parameter).Compile();
                Getter = Expression.Lambda<Func<T>>(Expression.Field(instanceExpression, fieldInfo)).Compile();
            }

        }

        public void Set(T value) => Setter(value);
        public T Get() => Getter();
    }
}

Caveats: the backing string is only updated when the list itself is modified. Updating a list element via direct access (e.g. via the list indexer) requires a manual call to the Store() method.

Answer 7

I have found a trick and I think this is a very usefull workaround to solve this kind of the problem:

public class User
{
  public long UserId { get; set; }

  public string Name { get; set; }

  private string _stringArrayCore = string.Empty;

  // Warnning: do not use this in Bussines Model
  public string StringArrayCore
  {
    get
    {
      return _stringArrayCore;
    }

    set
    {
      _stringArrayCore = value;
    }
  }

  [NotMapped]
  public ICollection<string> StringArray
  {
    get
    {
      var splitString = _stringArrayCore.Split(';');
      var stringArray = new Collection<string>();

      foreach (var s in splitString)
      {
        stringArray.Add(s);
      }
      return stringArray;
    }
    set
    {
      _stringArrayCore = string.Join(";", value);
    }
  }
}

---

How to use:

  // Write user
  using (var userDbContext = new UserSystemDbContext())
  {
    var user = new User { Name = "User", StringArray = new Collection<string>() { "Bassam1", "Bassam2" } };
    userDbContext.Users.Add(user);
    userDbContext.SaveChanges();
  }

  // Read User 
  using (var userDbContext = new UserSystemDbContext())
  {
    var user = userDbContext.Users.ToList().Last();

    foreach (var userArray in user.StringArray)
    {
      Console.WriteLine(userArray);
    }
  }

in the database

Table Users:

UserId  | Name | StringArrayCore
1       | User | Bassam1;Bassam2