Best practice to store large string in SQL Server using Entity Framework Core

entity-framework-core sql-server

Question

I'm looking for best practice to store list of string into database using Entity Framework Core.

My data is a set of roles maybe about 100 roles or more, so I have two options:

  1. Use another table and make some relationship.
  2. Use one column type nvarchar(max) and separate roles by ;

I need to know which way is better and why?

For now my code using the option 2 to store data and Entity Framework value converter to convert from list to string with ; separator and vice versa.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<User>(c =>
    {
        c.Property(f => f.Roles).HasConversion
        (
            v => String.Join(";", v),
            v => v == null ? new List<string>() : v.Split(';', StringSplitOptions.None).ToList()
        );
    });

}
1
0
8/28/2018 5:08:15 AM

Accepted Answer

From a purely data modelling point of view, absolutely need to split it up into multiple rows in a separate table. There are so many types of queries this will allow that a single string will not

This is regardless of whether you use Entity Framework or anything else.

1
8/28/2018 1:09:30 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow