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

user_128695 0
Viewed 33250

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?

mapping list entity-framework orm
3 Answers

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-

user_10221790

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

user_80587090
Related