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

Question

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.

Tenatenable

How do you expect the List<sting> is stored into the db? That won't work. Change it to string. – Finalist

If you have a list, it has to point to some entity. For EF to store the list, it needs a second table. In the second table it will put everything from your list, and use a foreign key to point back to your Test entity. So make a new entity with Id property and MyString property, then make a list of that. – Simplehearted

Right...It can't be stored in the db directly but I hoped Entity Framework create new entity to do that by itself. Thank you for your comments. – Tenatenable

Answer 1

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.

Alonso

what if an entity contains a List of entities? how will the mapping be saved? – Tyrontyrone

Depends - most likely to a separate table. – Alonso

can try to serialize and then compress and save the json formatted text, or encrypt and save it if its needed. either way you cant have the framework do the complex type table mapping for you. – Centrosymmetric

Answer 2

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.

Bentlee

Great solution for EF Core. Although it seems to have an issue whit char to string conversion. I had to implement it like such: .HasConversion( v => string.Join(";", v), v => v.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries)); – Glassine

This is the only really correct answer IMHO. All the others require you to change your model, and that violates the principle that domain models should be persistence ignorant. (It is fine if you are using separate persistence and domain models, but few people actually do that.) – Viceroy

You should accepted my edit request because you cannot use char as the first argument of string.Join and you have to provide a char[] as the first argument of string.Split if you also want to provide StringSplitOptions. – Hannie

In .NET Core you can. I'm using this exact piece of code in one of my projects. – Bentlee

Then maybe it has changed because I am also developing in .NET Core (more precisely a netstandard2.0 class library) and I cannot use it exactly like this. – Hannie

Not available in .NET Standard – Bentlee

@Bentlee is there a way to generalize that conversion so I don't have to repeat the whole thing for all my lists, and instead just apply something like HasConversion(List)? – Timeserver

See my answer for a fix to the one-entry to many-entries problem that can be caused by using an arbitrary non-reserved char as the delimiter – Ribosome

What to do for int[]? public int[] CategoryIds { get; set; } – Monogenetic

@MarcellToth "All the others require you to change your model" yeah, that is the database first normal rule – Eviaevict

Works fine, but I get this validation warning "The property 'Strings' on entity type 'YourEntity' 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." when I wish to create DB for such entities – Derris

@AlexeyKorsakov More info can be found here: learn.microsoft.com/en-us/ef/core/modeling/… – Viviennevivify

Please note that it's not a good idea to use something like List<string> as your type because EF Core has no chance of picking up changes to the collection. Using an array will force you to do a reassignment for each modification. – Costate

If you need use this conversion for more than one field, consider creating an extension method to save duplication. – Cellulose

This breaks if any of my strings contains a "," – Inevitable

@Inevitable Choose a different character or go with Mathieu VIALES's answer. – Bentlee

This method is really hacky. No matter which character you choose to join with, there is a risk that a string containing it will be added to the array at some point, which will lead to data corruption. I suggest not using this method without a very good reason to do so. – Guyton

A proper solution would be a many-to-many relationship with a separate table, because you won't be able to efficiently use this column in queries. But if you're serializing, then it's better to use actual JSON serialization like in this answer to prevent issues with commas. – Preestablish

@Groo - Originally I needed to store list of a company's phone numbers and it wasn't worth a table and it wasn't going to be used for querying and I was sure it doesn't have commas in it. So I let the EF handle the string concatenation and splitting for me and I just worked with my array in C#. For such a scenario this approach works well. EF Core 6 will have JSON column support for SQL Server and that would be better than serializing by ourselves and storing it in a string column. (PostgreSQL provider already supports JSON columns) – Bentlee

@Sasan: no problem, I just noticed that nobody in the thread mentioned a many-to-many solution, and I actually wanted to be able to efficiently include these values in queries so just mentioned. – Preestablish

Don't forget to also add a value comparer, so entity framework can track changes: .... new ValueComparer<List<string>>((c1, c2) => c1.SequenceEqual(c2), c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())), c => c.ToList())); – Jewell

Answer 3

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.

Ribosome

I wish this worked in .NET Framework & EF 6, it’s a really elegant solution. – Illsuited

Are you capable of querying on that field? My attempts have failed miserably: var result = await context.MyTable.Where(x => x.Strings.Contains("findme")).ToListAsync(); does not find anything. – Wives

To answer my own question, quoting the docs: "Use of value conversions may impact the ability of EF Core to translate expressions to SQL. A warning will be logged for such cases. Removal of these limitations is being considered for a future release." - Would still be nice though. – Wives

Don't you end up with the string containing json? Which is clunky in a SQL database and horrible in a NoSQL database. – Varsity

@Varsity You're right, it'll store a JSON in the column. I agree that, in order to have a "pure" relational model there should be a second table. Now in some cases this can quickly become very clumsy, having multiple two-column tables. A JSON to store a mere list of string doesn't seem like such a poor solution, assuming it's only a list of strings and no complex objects. Horrible in a NoSQL database, yes, I agree. But I hope the OP is using a relational DB considering it's Entity Framework. – Ribosome

@Varsity additionally, since the accepted answer already mentioned the use of "proper" relational SQL I though giving a clean serialization alternative would add value to this thread – Ribosome

Answer 4

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

Agglutinogen

Why not static methods instead of using public properties? (Or am I showing my procedural programming bias?) – Kalila

@Agglutinogen why is it necessary to define 2 lists? one as a property and one as the actual list? I would appreciate if you can also explain how the binding here works, because this solution is not working well for me, and I can't figure out the binding here. Thanks – Ominous

there is one private list, which has two public properties associated, Strings, which you will use in your application to add and remove strings, and StringsAsString which is the value that will be saved to the db, as a comma separated list. I'm not really sure what you are asking though, the binding is the private list _strings, which connects the two public properties together. – Agglutinogen

Please keep in mind that this answer does not escape , (comma) in strings. If a string in the list contains one or more , (comma) the string is splitted to multiple strings. – Psychognosis

In string.Join the comma should be surrounded by double quotes (for a string), not single quotes (for a char). See msdn.microsoft.com/en-us/library/57a79xd0(v=vs.110).aspx – Mantooth

How would this approach reflect on queries against value(s) stored in strings? – Corrientes

Answer 5

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

Illsuited

I find this solution quite ugly, but it's actually the only sane one. All options offering to join the list using whatever character and then split it back might turn into a wild mess if the splitting character is included in the strings. Json should be much more sane. – Ribosome

I ended up making an answer that is a "merge" of this one and an other one to fix each answer problem (ugliness/data-safety) using the other one's strong points. – Ribosome

Answer 6

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(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.

Horizon

I was thinking of updating my answer to add the new serialization alternative. I really like the use of default for you optional params trick. Makes for very clean code. – Ribosome

This is a perfect solution and should be added to the accepted answer which correctly provides the reason and background to the issue, but does not offer a solution. – Endospore

in .NET 6, you will get this error: the call is ambiguous between the following methods or properties: 'JsonSerializer.Serialize(TValue, JsonSerializerOptions?)' and 'JsonSerializer.Serialize(TValue, JsonTypeInfo) so you'll need to cast default to JsonSerializerOptions like this: (JsonSerializerOptions)default – Estremadura

I'm having an error, 'Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type', how do i fix this please? – Appurtenant

@MbaGozpel Ensure you've specified a (valid) return type in the angle brackets (I use YourEntity in the example above) since the compiler isn't able to infer what you're aiming to use. – Horizon

@Estremadura MS Learn According to ms, it says to fix it with '(JsonSerializerOptions)null' – Moe

Answer 7

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

Deafening

This is in case a list item cannot contain a string. Otherwise, you'll need to escape it. Or to serialize/deserialize the list for more complex situations. – Deafening

Also, don't forget to use [NotMapped] on the ICollection property – Mythologize

Answer 8

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

Triolet

Answer 9

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

Swen

What if string contains a comma? – Camarata

I wouldn't recommend doing it this way. StringsAsStrings will only be updated when the Strings reference is changed, and the only time in your example that happens is at assignment. Adding or removing items from your Strings list after assignment will not update the StringsAsStrings backing variable. The proper way to implement this would be to expose StringsAsStrings as a view of the Strings list, instead of the other way around. Join the values together in the get accessor of the StringsAsStrings property, and split them in the set accessor. – Medial

To avoid adding private properties (which isn't side effect free) make the setter of the serialized property private. jduncanator is of course right: if you don't catch the list manipulations (use a ObservableCollection?), the changes won't be noticed by EF. – Cahn

As @Medial mentioned this solution does not work when a modification to the List is made (binding in MVVM for example) – Intracranial

Answer 10

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

Hovey

Answer 11

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

Phyllome

looks a bit over engineered?! – Ton

@FalcoAlexander I've updated my post... Maybe a bit verbose but does the job. Make sure you replace NET462 with the appropriate environment or add it to it. – Phyllome

+1 for the effort of putting this together. The solution is a little bit overkill for storing an array of strings :) – Humeral

this is a bit much – Pithy

Answer 12

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

Cayman