Store Dictionary as List> in database using EF Core

Question

I have an entity of type Dictionary<string, string> and I want to store it in a separate table as List<KetValuePair<string, string>>. This is what I am doing now

public class MyHttpLog
{
    public Guid Id
    public HttpRequestLog Request { get; set; }
}

public class HttpRequestLog
{
    public string Path { get; set; }
    public Dictionary<string, string> Headers { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyHttpLog>()
            .OwnsOne(b => b.Request, 
            request =>
            {
                request.WithOwner();
                request.Property(x => x.Headers).HasConversion(
                v => v.ToList(),
                v => JsonConvert.DeserializeObject<Dictionary<string, string>>(JsonConvert.SerializeObject(v)) ?? new Dictionary<string, string>()
             );
    });
}

Since the object type is Dictionary, the relation in the model will be one to one but since we want to store it in the database as List, the relation will be one to many. How can I handle this? I don't want to convert the dictionary to serialized json. The error I am getting is

The property 'HttpRequestLog.Headers' is of type 'List<KeyValuePair<string, string>>' which is not supported by the current database provider. Either change the property CLR type, or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

The error is telling you that the database doesn't know how to store the type you have defined on your entity. Familiarize yourself with the data types supported on your database, here is SQL: https://www.w3schools.com/sql/sql_datatypes.asp As for options: If you plan on querying this data efficiently you may want to store each value in a column, so will have to flatten it. IF you absolutely need to persist this data and only need to access it programmatically, you could serialize it to a varchar column and call it good. Storing data like this will most likely give your DBA ulcers. — Anthony G., Sep 21 '22 at 15:42

score 0 · Answer 1 · answered Sep 21 '22 at 15:59

try this :

public class MyHttpLog
{
    public Guid Id { get; set; }
    public HttpRequestLog Request { get; set; }
}

public class HttpRequestLog
{
    public int Id { get; set; }
    public string Path { get; set; }
    public Dictionary<string, string> Headers { get; set; }
}

public DbSet<MyHttpLog> MyHttpLog { get; set; }

public DbSet<HttpRequestLog> HttpRequestLog { get; set; }



protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<HttpRequestLog>()
      .Property(b => b.Headers)
      .HasConversion(
       c => JsonConvert.SerializeObject(c),
       c => JsonConvert.DeserializeObject<Dictionary<string, string>>(c));
}

Thank you for the answer. But in this case, you won't be able to run queries based on the headers. — Mohammad Taherian, Sep 21 '22 at 16:01

score -1 · Answer 2 · answered Sep 21 '22 at 16:14

-1

You have two choices:

Store your Key-Value pairs in completely separate table and reference it with a foreign key:

public class MyKeyValuePairs
{
    public string Key { get; set; }
    public string Value { get; set; }
    public int ForeignKey {get; set;}
}

Store your data in JSON format. Look this and this

answered Sep 21 '22 at 16:14

Arian

12,793
66
176
300

Thank you for the answer. Storing the Key-Value pairs is what exactly I want to do. But the main type is Dictionary and how should we set it in OnModelCreating? – Mohammad Taherian Sep 21 '22 at 17:45

Store Dictionary as List> in database using EF Core

2 Answers2