Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.


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.


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.

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(); }
    }
}

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);
    }
}

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.