Save Array In Entity Framework Core

Dec 22nd, 2017 - written by Kimserey with .

EntityFramework is a ORM where R stands for Relational (database). Relational databases operate with tables, rows and columns. If we have an object containing an array of objects, by default, it would translate to two tables and a JOIN between the two tables. This behaviour is great if our initial model has links to other objects but not so great when the array inside of model is only composed by primitive type values, like strings. Today we will see how we can store array of values as property of our model and prevent having to create two tables with a JOIN.

Install EntityFramework Core Sqlite
Save array of primitive type values

All the code can be found on my GitHub.

1. Install EntityFramework Core Sqlite

In this example I will be using Sqlite.

We start by creating two projects, a .NET Core/ASP.NET Core project and a .NET Standard library project. In the library we install Microsoft.EntityFrameworkCore and in the web project we install Microsoft.EntityFrameworkCore.Sqlite and Microsoft.EntityFrameworkCore.Design.

- ASP.NET Core
  - Microsoft.EFCore.Sqlite
  - Microsoft.EFCore.Design

- .NET Standard Library
  - Microsoft.EFCore

After installing the lib we enable the EF tools on the dotnet CLI by adding the following reference in the project file:

<ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
</ItemGroup>

Next we create our model and our db context in the library project. Here I am creating a dumb model as example.

public class Value
{
    [Key]
    public int Key { get; set; }
    public string Data { get; set; }
    public string[] Tags { get; set; }
}

public class ValueDbContext: DbContext
{
    public DbSet<Value> Values { get; set; }

    public ValueDbContext(DbContextOptions<ValueDbContext> options): base(options) { }
}

Note that the Dbcontext must have a constructor taking in DbContextOptions<TDbContext>.

Next in our web project we register the DbContext and specify that we will be using sqlite provider and giving in the database connection string.

public void ConfigureServices(IServiceCollection services)
{
    ... some other configs

    services.AddDbContext<ValueDbContext>(options =>
    {
        options.UseSqlite(
            "Data source={Db name}.db", 
            opts => opts.MigrationsAssembly("{Name of the web project assembly}"));
    });
}

Because our web project will be the one holding the migration files, we need to specify that the migrations library is the web project. By default the migrations library is the one containing the DbContext.

opts => opts.MigrationsAssembly("{Name of the web project assembly}")

Now when we try to create the migration we should hit an error indicating that string[] can’t be translated.

The property 'Value.Tags' could not be mapped, because it is of type 'String[]' which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

2. Save array of primitive type values

What we want is to be able to store the array in a single cell of our table but handling it as a array when manipulating the object in the code.

To achieve that we need to start by indicating to EF that we do not want the Tags property to me mapped.

[NotMapped]
public string[] Tags { get; set; }

Once we have specified that, EF will no longer save our property.

Next we need to indicate to EF that we want to save a backing field _tags. We start by creating the property.

public class Value
{
    [Key]
    public int Key { get; set; }

    public string Data { get; set; }

    private string _tags;
    [NotMapped]
    public string[] Tags { get; set; }
}

Next in the db context we specify that we wish to save the backing field together with the name of the column which will created on the table.

public class ValueDbContext: DbContext
{
    public DbSet<Value> Values { get; set; }

    public ValueDbContext(DbContextOptions<ValueDbContext> options): base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Value>()
            .Property<string>("TagCollection")
            .HasField("_tags");
    }
}

Lastly we can modify the getter and setter of the property to modify the backing field:

public class Value
{
    private static readonly char delimiter = ';';
    private string _tags;

    [Key]
    public int Key { get; set; }

    public string Data { get; set; }

    [NotMapped]
    public string[] Tags
    {
        get { return _tags.Split(delimiter); }
        set
        {
            _tags = string.Join($"{delimiter}", value);
        }
    }
}

The backing field will save the array of string as delimited string a;b;c;d and when taken out of the database, it will be broken and converted back to an array of string.

Conclusion

Today we saw how to install and use EF Core with Sqlite. We also saw how we could place the DbContext into a separate library while holding the migrations into the project where the configuration sits. Lastly we saw how we could save array of primitive type values with a small change on our model. Hope you like this post as much as I like writing it. See you next time!