Entity Framework options to map list of strings or list of int (List<string>)

Question

I want to store an object that contains a List of primitives using EF.

public class MyObject {
    public int Id {get;set;}
    public virtual IList<int> Numbers {get;set;}
}

I know that EF cannot store this, but I'd like to know possible solutions to solve this problem.

The 2 Solutions I can think of are:

1.Create a Dummy object that has an Id and the Integervalue, e.g.

public class MyObject {
    public int Id {get;set;}
    public virtual IList<MyInt> Numbers {get;set;}
}

public class MyInt {
    public int Id {get;set;}
    public int Number {get;set;}
}

2.Store the list values as a blob, e.g.

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

    /// use NumbersValue to persist/load the list values
    public string NumbersValue {get;set;}

    [NotMapped]
    public virtual IList<int> Numbers {
         get {
              return NumbersValue.split(',');
         }
         set {
             NumbersValue = value.ToArray().Join(",");
         }
    }
}

The Problem with the 2. approach is, that I have to create a Custom IList implementation to keep track if someone modifies the returned collection.

Is there a better solution for this?

Answer 1

The simple solution would be in your DataContext (a class that implements DbContext) add this override:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<MyObject>()
        .Property(p => p.Numbers)
        .HasConversion(
            toDb => string.Join(",", toDb), 
            fromDb => fromDb.Split(',').Select(Int32.Parse).ToList() ?? new List<int>());
}

Answer 2

Bernhard's answer is brilliant. I just couldn't help but refine it a little. Here's my two cents:

[ComplexType]
public abstract class EFPrimitiveCollection<T> : IList<T>
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int Id { get; set; }

    const string DefaultValueSeperator = "|";
    readonly string[] DefaultValueSeperators = new string[] { DefaultValueSeperator };

    [NotMapped]
    private List<T> _data;

    [NotMapped]
    private string _value;

    [NotMapped]
    private bool _loaded;

    protected virtual string ValueSeparator => DefaultValueSeperator;
    protected virtual string[] ValueSeperators => DefaultValueSeperators;

    [ShadowColumn, MaxLength]
    protected virtual string Value // Change this to public if you prefer not to use the ShadowColumnAttribute
    {
        get => _value;
        set
        {
            _data.Clear();
            _value = value;

            if (string.IsNullOrWhiteSpace(_value))
                return;

            _data = _value.Split(ValueSeperators, StringSplitOptions.None)
                        .Select(x => ConvertFromString(x)).ToList();

            if (!_loaded) _loaded = true;
        }
    }

    public EFPrimitiveCollection()
    {
        _data = new List<T>();
    }

    void UpdateValue()
    {
        _value = string.Join(ValueSeparator.ToString(),
                _data.Select(x => ConvertToString(x))
                .ToArray());
    }

    public abstract T ConvertFromString(string value);
    public abstract string ConvertToString(T value);

    #region IList Implementation
    public int Count
    {
        get
        {
            EnsureData();
            return _data.Count;
        }
    }

    public T this[int index]
    {
        get
        {
            EnsureData();
            return _data[index];
        }
        set
        {
            EnsureData();
            _data[index] = value;
        }
    }

    public bool IsReadOnly => false;

    void EnsureData()
    {
        if (_loaded)
            return;

        if (string.IsNullOrWhiteSpace(_value))
            return;

        if (_data.Count > 0) return;


        if (!_loaded) _loaded = true;
        _data = _value.Split(ValueSeperators, StringSplitOptions.None)
                        .Select(x => ConvertFromString(x)).ToList();
    }

    public void Add(T item)
    {
        EnsureData();

        _data.Add(item);
        UpdateValue();
    }

    public bool Remove(T item)
    {
        EnsureData();

        bool res = _data.Remove(item);
        UpdateValue();

        return res;
    }

    public void Clear()
    {
        _data.Clear();
        UpdateValue();
    }

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

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

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

    public void Insert(int index, T item)
    {
        EnsureData();
        _data.Insert(index, item);
        UpdateValue();
    }

    public void RemoveAt(int index)
    {
        EnsureData();
        _data.RemoveAt(index);
        UpdateValue();
    }

    public void AddRange(IEnumerable<T> collection)
    {
        EnsureData();
        _data.AddRange(collection);
        UpdateValue();
    }

    public IEnumerator<T> GetEnumerator()
    {
        EnsureData();
        return _data.GetEnumerator();
    }
    IEnumerator IEnumerable.GetEnumerator()
    {
        EnsureData();
        return _data.GetEnumerator();
    }
    #endregion
}

With that base class you can have as many derivations as you like:

[ComplexType]
public class EFIntCollection : EFPrimitiveCollection<int>
{
    public override int ConvertFromString(string value) => int.Parse(value);
    public override string ConvertToString(int value) => value.ToString();
}

[ComplexType]
public class EFInt64Collection : EFPrimitiveCollection<long>
{
    public override long ConvertFromString(string value) => long.Parse(value);
    public override string ConvertToString(long value) => value.ToString();
}

[ComplexType]
public class EFStringCollection : EFPrimitiveCollection<string>
{
    string _separator;
    protected override string ValueSeparator => _separator ?? base.ValueSeparator;

    public override string ConvertFromString(string value) => value;
    public override string ConvertToString(string value) => value;

    public EFStringCollection()
    {
    }
    public EFStringCollection(string separator)
    {
        _separator = separator;
    }
}

EFPrimitiveCollection works just like a list, so you shouldn't have any issues using it like a normal List. Also the data is loaded on demand. Here's an example:

if (store.AcceptedZipCodes == null)
    store.AcceptedZipCodes = new EFStringCollection();

store.AcceptedZipCodes.Clear();
store.AcceptedZipCodes.AddRange(codes.Select(x => x.Code));

Shadow Column

This attribute is being used to abstract away the Value property. If you do not see the need to do this, simply remove it and make the Value property public.

More information can be found on the ShadowColumnAttribute in my answer here

Answer 3

I'm using EF Core and had a similar problem but solved it in a simpler way.

The idea is to store the list of integers as a comma separated string in the database. I do that by specifying a ValueConverter in my entity type builder.

public class MyObjectBuilder : IEntityTypeConfiguration<MyObject>
{
    public void Configure(EntityTypeBuilder<MyObject> builder)
    {
        var intArrayValueConverter = new ValueConverter<int[], string>(
            i => string.Join(",", i),
            s => string.IsNullOrWhiteSpace(s) ? new int[0] : s.Split(new[] { ',' }).Select(v => int.Parse(v)).ToArray());

        builder.Property(x => x.Numbers).HasConversion(intArrayValueConverter);
    }
}

More information can be found here: https://entityframeworkcore.com/knowledge-base/37370476/how-to-persist-a-list-of-strings-with-entity-framework-core-

Answer 4

Although I do not like to answer my own question, but here is what solved my problem:

After I found this link about Complex Types I tried several implementations, and after some headache I ended up with this.

The List values get stored as a string on the table directly, so it's not required to perform several joins in order to get the list entries. Implementors only have to implement the conversation for each list entry to a persistable string (see the Code example).

Most of the code is handled in the Baseclass (PersistableScalarCollection). You only have to derive from it per datatype (int, string, etc) and implement the method to serialize/deserialize the value.

It's important to note, that you cannot use the the generic baseclass directly (when you remove the abstract). It seems that EF cannot work with that. You also have to make sure to annotate the derived class with the [ComplexType] attribute.

Also note that it seems not to be possible to implement a ComplexType for IList<T> because EF complains about the Indexer (therefore I went on with ICollection).

It's also important to note, that since everything is stored within one column, you cannot search for values in the Collection (at least on the database). In this case you may skip this implementation or denormalize the data for searching.

Example for a Collection of integers:

    /// <summary>
    /// ALlows persisting of a simple integer collection.
    /// </summary>
    [ComplexType]
    public class PersistableIntCollection : PersistableScalarCollection<int> {
        protected override int ConvertSingleValueToRuntime(string rawValue) {
            return int.Parse(rawValue);
        }

        protected override string ConvertSingleValueToPersistable(int value) {
            return value.ToString();
        }
    }

Usage example:

public class MyObject {
    public int Id {get;set;}
    public virtual PersistableIntCollection Numbers {get;set;}
}

This is the baseclass that handles the persistence aspect by storing the list entries within a string:

    /// <summary>
    /// Baseclass that allows persisting of scalar values as a collection (which is not supported by EF 4.3)
    /// </summary>
    /// <typeparam name="T">Type of the single collection entry that should be persisted.</typeparam>
    [ComplexType]
    public abstract class PersistableScalarCollection<T> : ICollection<T> {

        // use a character that will not occur in the collection.
        // this can be overriden using the given abstract methods (e.g. for list of strings).
        const string DefaultValueSeperator = "|"; 

        readonly string[] DefaultValueSeperators = new string[] { DefaultValueSeperator };

        /// <summary>
        /// The internal data container for the list data.
        /// </summary>
        private List<T> Data { get; set; }

        public PersistableScalarCollection() {
            Data = new List<T>();
        }

        /// <summary>
        /// Implementors have to convert the given value raw value to the correct runtime-type.
        /// </summary>
        /// <param name="rawValue">the already seperated raw value from the database</param>
        /// <returns></returns>
        protected abstract T ConvertSingleValueToRuntime(string rawValue);

        /// <summary>
        /// Implementors should convert the given runtime value to a persistable form.
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        protected abstract string ConvertSingleValueToPersistable(T value);

        /// <summary>
        /// Deriving classes can override the string that is used to seperate single values
        /// </summary>        
        protected virtual string ValueSeperator {
            get {
                return DefaultValueSeperator;
            }
        }

        /// <summary>
        /// Deriving classes can override the string that is used to seperate single values
        /// </summary>        
        protected virtual string[] ValueSeperators {
            get {
                return DefaultValueSeperators;
            }
        }

        /// <summary>
        /// DO NOT Modeify manually! This is only used to store/load the data.
        /// </summary>        
        public string SerializedValue {
            get {
                var serializedValue = string.Join(ValueSeperator.ToString(),
                    Data.Select(x => ConvertSingleValueToPersistable(x))
                    .ToArray());
                return serializedValue;
            }
            set {
                Data.Clear();

                if (string.IsNullOrEmpty(value)) {
                    return;
                }

                Data = new List<T>(value.Split(ValueSeperators, StringSplitOptions.None)
                    .Select(x => ConvertSingleValueToRuntime(x)));
            }
        }

        #region ICollection<T> Members

        public void Add(T item) {
            Data.Add(item);
        }

        public void Clear() {
            Data.Clear();
        }

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

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

        public int Count {
            get { return Data.Count; }
        }

        public bool IsReadOnly {
            get { return false; }
        }

        public bool Remove(T item) {
            return Data.Remove(item);
        }

        #endregion

        #region IEnumerable<T> Members

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

        #endregion

        #region IEnumerable Members

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

        #endregion
    }