How do I map property with of type 'Dictionary' with Entity Framework?

Question

using Microsoft.EntityFrameworkCore;

namespace NameSpaceName
{
    public class WordContext : DbContext
    {
        public DbSet<VmWord> Words { get; set; }
        public DbSet<VmWordLocalization> WordLocalization { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=NameSpaceName.db");
        }
    }
}

namespace NameSpaceName
{
    public class VmWord
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Dictionary<string, VmWordLocalization> Localization { get; set; }
    }
}

namespace NameSpaceName
{
    public class VmWordLocalization
    {
        public int Id { get; set; }
        public string En { get; set; }
        public string Pl { get; set; }
    }
}

I have VmWord table and I need to add VmWordLocalization as dictionary to be able to get localization variable value by key;

When I try to execute:

dotnet ef migrations add Add_Localization_Table --context WordContext

I get:

The property 'VmWord.Localization' could not be mapped, because it is of type 'Dictionary' 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'.

How do I map property with of type 'Dictionary'?

Answer 1

The reason that you want an object with a Dictionary property, is because you want a fast lookup: Give me the VmWord with the Localization with this Key. In other words: given a key, you want a fast lookup to the VnWordLocalization object containing this key.

In Entity Framework a DbSet<...> represents a table in a relational database. Relational databases don't know the concept of Dictionary.

Luckily, a database knows the concept of fast-lookup using a key. Usually this is the primary key, but you can add other indexes that can be used as key.

Alas you forgot to tell us which string you want to use as fast lookup for your VmWordLocalizations. Is it En? or Pl? or is it a completely new key?

If it is a completely new Key, then apparently every VmWordLocalication has a property, let's name it Key, that is unique in combination with the foreign key VmWordId. If it is either En or Pl, use that as the Key.

class VmWord
{
    public int Id { get; set; }

    // every VmWord has zero or more Localizations (one-to-many)
    public virtual ICollection<VmWordLocalization> VmWordLocalizations { get; set; }

    ... // other properties
}

class VmWordLocalization
{
    public int Id { get; set; }

    // every VmWordLocalization belongs to exactly one VmWord using foreign key
    public int VmWordId {get; set;}
    public virtual VmWord VmWord {get; set;}

    // every VmWordLocalization has a property Key, which is unique in combination
    // with VmWordId
    public string Key {get; set;}
}

Note: if Key is overall unique, consider using it as a primary Key.

Note 2: in entity framework the non-virtual properties represent the columns in your tables, the relations between the tables are represented by the virtual properties

Note 3: Consider sticking to the entity framework code-first conventions. It eliminates the need of attributes or fluent API. Only deviate from these conventions if you have good reasons. Having to type less characters is not a good reason.

If Key can't be your primary key, you'll have to add an extra fast-lookup: an index. This is done in DbContext.OnModelCreating

protected overrid void OnModelCreating(...)
{
     // Every VmWordLocalization has a property Key, which is unique per VmWordId
     // create an index with (VmWordId, Key) as index key
     var vmWordLocalizationEntity = modelBuilder.Entity<VmWordLocalization>();

     // first index annotation: VmWordId:
     vmWordLocalizationEntity.Property(entity => entity.VmWordId)
         .IsRequired()
         .HasColumnAnnotatin(IndexAnnotation.AnnotationName, 
              new IndexAnnotation(new IndexAttribute("index_VmWordId", 0)));

     // 2nd index annotation: Key:
     vmWordLocalizationEntity.Property(entity => entity.Key)
         .IsRequired()
         .HasColumnAnnotatin(IndexAnnotation.AnnotationName, 
              new IndexAnnotation(new IndexAttribute("index_Key", 1)));
}

The identifiers index_VmWord and index_Key, can be anything. They are only identifiers to name the columns of the index.

The numbers 0 and 1 give the order in which the index is sorted: first byindex_VmWordId then by index_Key.

Now if you have a VmWord with Id == 10, it is possible to add a VmWordLocation with a VmWordId == 10 and Key == "Hello". But if you try to add a second VmWordLocation with these values You'll get an exception, just like you would have gotten if you'd added two object with the same key to a dictionary.

Now to get the requested VmWords with its localizations in a Dictionary:

var vmWordsWithLocalizations = myDbContext.VmWords.Select(vmWord => new
{
      // select only the properties you plan to use:
      Id = vmWord.Id,
      Name = vmWord.Name,

      Localizations.VmWord.VmwordLocalizations
          .Where(vmWordLocalization => ...) // only if you do't want all vmWordLocalizations
          .ToDictionary(vmWordLocalization => vmWordLocalization.Key,  // Dictionary Key
              new                                                      // Dictionary value
              {   // again: select only the properties you plan to use
                  Id = vmWordLocalization.Id,
                  En = vmWordLocalization.Em,
                  Pl = vmWordLocalization.Pl,

                  // no need: you know it equals vmWord.Id
                  // VmWordId = vmWordLocalization.VmWordId

              }),

});

If you'll have to do this often, consider creating an extension function:

IEnumerable<VmWordWithDictionary> ToVmWordWithDictionary(this IQueryable<VmWord> vmWords)
{
     return vmWords.Select(vmWord => new VmWordEx()
     {
         Id = vmWord.Id,
         Name = vmWord.Name,
         Localizations = vmWord.VmWordLocalizatons
             .Where(vmWordLocalization => ...)
             .ToDictionary(
                 vmWordLocalization => vmWordLocalization.Key        // Key
                 vmWordLocalization => new VmWordLocalizationEx()    // Value
                 {
                      Id = vmWordLocalization.Id,
                      En = vmWordLocalization.Em,
                      Pl = vmWordLocalization.Pl,
                 }),
});

Note: For efficiency reasons I don't want to transport more data then needed, so I created two extra classes, containing only the data I need.

Note: because of the Dictionary the return has to be an IEnumerable. You can't do IQueryable LINQ statements after this anymore

Answer 2

The question is, what is the dictionary and how do you see this mapping to a physical database table (are you looking to have a table with a list of words and then another table with the list of words repeated for each translation)? I'm 'spit balling' here as I am not sure what it is you are trying to achieve but rather than having a base word with a link of translations you may wish to just have a table which has a word id and a language id which makes up a composite primary key, then you would just apply a where wordid = x and language = y to get the correct version of the word. Alternatively if you are set on your structure then you would need to implement a child table with a foreign key and define it something like this on the VmWord class:

public List<VmWordLocalisation> {get;set;}

And then on the child table you would define the foreign key like so

[ForeignKey("Id")]
public VmWord Parent {get;set;}

You will also need to set up a composite primary key on the child table to identify the Id and the language

Hope this helps!

Answer 3

It's ont possible to have Dictionary property in EF. The solution is to replace Dictionary with ICollection:

namespace NameSpaceName
{
    public class VmWord
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public ICollection<VmWordLocalization> Localizations { get; set; }
    }

    public class VmWordLocalization
    {
        public int Id { get; set; }

        public string Key { get; set; }  // key from your previous dictionary

        public string En { get; set; }

        public string Pl { get; set; }
    }
}