CODEWITHSUNDEEP
c#.netentity-framework
Paul
Paul

Reputation: 2007

Entity Framework - Code First - Can't Store List<String>

I wrote such class:

class Test
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required]
    public List<String> Strings { get; set; }

    public Test()
    {
        Strings = new List<string>
        {
            "test",
            "test2",
            "test3",
            "test4"
        };
    }
}

and

internal class DataContext : DbContext
{
    public DbSet<Test> Tests { get; set; }
}

After run code:

var db = new DataContext();
db.Tests.Add(new Test());
db.SaveChanges();

my data is getting saved but just the Id. I don't have any tables nor relationships applying to Strings list.

What am I doing wrong? I tried also to make Strings virtual but it didn't change anything.

Thank you for your help.

Upvotes: 160

Views: 183515

Answers (12)

Whit Waldo
Whit Waldo

Reputation: 5207

Slightly tweaking @Mathieu Viales's answer, here's a .NET Standard compatible snippet using the new System.Text.Json serializer thus eliminating the dependency on Newtonsoft.Json.

using System.Text.Json;

builder.Entity<YourEntity>().Property(p => p.Strings)
    .HasConversion(
        v => JsonSerializer.Serialize(v, default),
        v => JsonSerializer.Deserialize<List<string>>(v, default));

Note that while the second argument in both Serialize() and Deserialize() is typically optional, you'll get an error:

An expression tree may not contain a call or invocation that uses optional arguments

Explicitly setting that to the default (null) for each clears that up.

Edit for .NET 6 and later:

Thanks to @Dang-gunRoleeyas for the comment pointing this out!

Per the documentation, a breaking change was introduced in .NET 6 with the source-generator methods that resulted in the error message because of the additional overloads:

The call is ambiguous between the following methods or properties: 'JsonSerializer.Serialize(TValue, JsonSerializerOptions?)' and 'JsonSerializer.Serialize(TValue, JsonTypeInfo)

Rather, use the following instead:

using System.Text.Json;

builder.Entity<YourEntity>().Property(e => e.Strings)
    .HasConversion(
        v => JsonSerializer.Serialize(v, (JsonSerializerOptions)null), 
        v => JsonSerializer.Deserialize<List<string>>(v, (JsonSerializerOptions)null));

By replacing the default value with a typed null value, it'll match the intended overload and work as expected once again.

Upvotes: 33

Adam Tal
Adam Tal

Reputation: 5961

Just to simplify -

Entity framework doesn't support primitives. You either create a class to wrap it or add another property to format the list as a string:

[NotMapped]
public ICollection<string> List { get; set; }
public string ListString
{
    get { return string.Join(",", List); }
    set { List = value.Split(',').ToList(); }
}

Upvotes: 12

morrisonbrett
morrisonbrett

Reputation: 151

@Pawel-m's solution works. I managed to get it working for a complex data type, not just for a string.

public class AddressModel
{
    public string City { get; set; }
    public string State { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public ICollection<AddressModel> Addresses { get; set; } = new List<AddressModel>();
}

It's too bad that the serialization / deserialization expression needs to happen at the modelBuilder layer scope. I would have preferred just to put it in the model definition like OnModelBinding() (if there was such an option). I put it at the startup level scope in DbContext->OnModelCreating().

modelBuilder.Entity<AddressModel>().Property(p => p.Addresses )
    .HasConversion(
    v => JsonSerializer.Serialize(v, (JsonSerializerOptions) null),
    v => JsonSerializer.Deserialize<List<AddressModel>>(v, (JsonSerializerOptions) null),
    new ValueComparer<ICollection<AddressModel>>(
        (c1, c2) => c1.SequenceEqual(c2),
        c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
        c => c.ToList()));

Upvotes: 1

Paweł M
Paweł M

Reputation: 81

Link to the documentation

Example from the documentation:

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Contents { get; set; }

    public ICollection<string> Tags { get; set; }
}

Using System.Text.Json:

modelBuilder.Entity<Post>()
    .Property(e => e.Tags)
    .HasConversion(
        v => JsonSerializer.Serialize(v, (JsonSerializerOptions)null),
        v => JsonSerializer.Deserialize<List<string>>(v, (JsonSerializerOptions)null),
        new ValueComparer<ICollection<string>>(
            (c1, c2) => c1.SequenceEqual(c2),
            c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
            c => (ICollection<string>)c.ToList()));

Upvotes: 8

Sasan
Sasan

Reputation: 4220

EF Core 2.1+ :

Property:

public string[] Strings { get; set; }

OnModelCreating:

modelBuilder.Entity<YourEntity>()
            .Property(e => e.Strings)
            .HasConversion(
                v => string.Join(',', v),
                v => v.Split(',', StringSplitOptions.RemoveEmptyEntries));

Update (2021-02-14)

The PostgreSQL has an array data type and the Npgsql EF Core provider does support that. So it will map your C# arrays and lists to the PostgreSQL array data type automatically and no extra config is required. Also you can operate on the array and the operation will be translated to SQL.

More information on this page.

Upvotes: 196

Mathieu VIALES
Mathieu VIALES

Reputation: 4772

This answer is based on the ones provided by @Sasan and @CAD bloke.

If you wish to use this in .NET Standard 2 or don't want Newtonsoft, see Xaniff's answer below

Works only with EF Core 2.1+ (not .NET Standard compatible)(Newtonsoft JsonConvert)

builder.Entity<YourEntity>().Property(p => p.Strings)
    .HasConversion(
        v => JsonConvert.SerializeObject(v),
        v => JsonConvert.DeserializeObject<List<string>>(v));

Using the EF Core fluent configuration we serialize/deserialize the List to/from JSON.

Why this code is the perfect mix of everything you could strive for:

  • The problem with Sasn's original answer is that it will turn into a big mess if the strings in the list contains commas (or any character chosen as the delimiter) because it will turn a single entry into multiple entries but it is the easiest to read and most concise.
  • The problem with CAD bloke's answer is that it is ugly and requires the model to be altered which is a bad design practice (see Marcell Toth's comment on Sasan's answer). But it is the only answer that is data-safe.

Upvotes: 114

Deleroy
Deleroy

Reputation: 358

I want to add that when using Npgsql (data provider for PostgreSQL), arrays and lists of primitive types are actually supported:

https://www.npgsql.org/efcore/mapping/array.html

Upvotes: 4

Shimmy Weitzhandler
Shimmy Weitzhandler

Reputation: 104731

You can use this ScalarCollection container that confines an array and provides some manipulation options (Gist):

Usage:

public class Person
{
    public int Id { get; set; }
    //will be stored in database as single string.
    public SaclarStringCollection Phones { get; set; } = new ScalarStringCollection();
}

Code:

using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;

namespace System.Collections.Specialized
{
#if NET462
  [ComplexType]
#endif
  public abstract class ScalarCollectionBase<T> :
#if NET462
    Collection<T>,
#else
    ObservableCollection<T>
#endif
  {
    public virtual string Separator { get; } = "\n";
    public virtual string ReplacementChar { get; } = " ";
    public ScalarCollectionBase(params T[] values)
    {
      if (values != null)
        foreach (var item in Items)
          Items.Add(item);
    }

#if NET462
    [Browsable(false)]
#endif
    [EditorBrowsable(EditorBrowsableState.Never)]
    [Obsolete("Not to be used directly by user, use Items property instead.")]
    public string Data
    {
      get
      {
        var data = Items.Select(item => Serialize(item)
          .Replace(Separator, ReplacementChar.ToString()));
        return string.Join(Separator, data.Where(s => s?.Length > 0));
      }
      set
      {
        Items.Clear();
        if (string.IsNullOrWhiteSpace(value))
          return;

        foreach (var item in value
            .Split(new[] { Separator }, 
              StringSplitOptions.RemoveEmptyEntries).Select(item => Deserialize(item)))
          Items.Add(item);
      }
    }

    public void AddRange(params T[] items)
    {
      if (items != null)
        foreach (var item in items)
          Add(item);
    }

    protected abstract string Serialize(T item);
    protected abstract T Deserialize(string item);
  }

  public class ScalarStringCollection : ScalarCollectionBase<string>
  {
    protected override string Deserialize(string item) => item;
    protected override string Serialize(string item) => item;
  }

  public class ScalarCollection<T> : ScalarCollectionBase<T>
    where T : IConvertible
  {
    protected override T Deserialize(string item) =>
      (T)Convert.ChangeType(item, typeof(T));
    protected override string Serialize(T item) => Convert.ToString(item);
  }
}

Upvotes: 1

Torsten Kolb
Torsten Kolb

Reputation: 218

Of course Pawel has given the right answer. But I found in this post that since EF 6+ it is possible to save private properties. So I would prefer this code, because you are not able to save the Strings in a wrong way.

public class Test
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column]
    [Required]
    private String StringsAsStrings { get; set; }

    public List<String> Strings
    {
        get { return StringsAsStrings.Split(',').ToList(); }
        set
        {
            StringsAsStrings = String.Join(",", value);
        }
    }
    public Test()
    {
        Strings = new List<string>
        {
            "test",
            "test2",
            "test3",
            "test4"
        };
    }
}

Upvotes: 5

CAD bloke
CAD bloke

Reputation: 8798

JSON.NET to the rescue.

You serialize it to JSON to persist in the Database and Deserialize it to reconstitute the .NET collection. This seems to perform better than I expected it to with Entity Framework 6 & SQLite. I know you asked for List<string> but here's an example of an even more complex collection that works just fine.

I tagged the persisted property with [Obsolete] so it would be very obvious to me that "this is not the property you are looking for" in the normal course of coding. The "real" property is tagged with [NotMapped] so Entity framework ignores it.

(unrelated tangent): You could do the same with more complex types but you need to ask yourself did you just make querying that object's properties too hard for yourself? (yes, in my case).

using Newtonsoft.Json;
....
[NotMapped]
public Dictionary<string, string> MetaData { get; set; } = new Dictionary<string, string>();

/// <summary> <see cref="MetaData"/> for database persistence. </summary>
[Obsolete("Only for Persistence by EntityFramework")]
public string MetaDataJsonForDb
{
    get
    {
        return MetaData == null || !MetaData.Any()
                   ? null
                   : JsonConvert.SerializeObject(MetaData);
    }

    set
    {
        if (string.IsNullOrWhiteSpace(value))
           MetaData.Clear();
        else
           MetaData = JsonConvert.DeserializeObject<Dictionary<string, string>>(value);
    }
}

Upvotes: 32

randoms
randoms

Reputation: 2783

I Know this is a old question, and Pawel has given the correct answer, I just wanted to show a code example of how to do some string processing, and avoid an extra class for the list of a primitive type.

public class Test
{
    public Test()
    {
        _strings = new List<string>
        {
            "test",
            "test2",
            "test3",
            "test4"
        };
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    private List<String> _strings { get; set; }

    public List<string> Strings
    {
        get { return _strings; }
        set { _strings = value; }
    }

    [Required]
    public string StringsAsString
    {
        get { return String.Join(',', _strings); }
        set { _strings = value.Split(',').ToList(); }
    }
}

Upvotes: 43

Pawel
Pawel

Reputation: 31610

Entity Framework does not support collections of primitive types. You can either create an entity (which will be saved to a different table) or do some string processing to save your list as a string and populate the list after the entity is materialized.

Upvotes: 189

Related Questions