CODEWITHSUNDEEP
sql-serverentity-framework-core
Ahmed Aljaff
Ahmed Aljaff

Reputation: 199

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

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

}

Upvotes: 0

Views: 954

Answers (1)

TomC
TomC

Reputation: 2814

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.

Upvotes: 1

Related Questions