23

I have a dictionary like this:

/// <summary>
/// Gets the leave entitlement details.
/// </summary>
/// <value>The leave entitlement details.</value>
public Dictionary<string, EmployeeLeaveEntitlement> LeaveEntitlementDetails { get; set; }  

And I want to map it to the database. Is it possible to use a protected or private List<> for that? such as:

/// <summary>
/// Gets the leave entitlement details.
/// </summary>
/// <value>The leave entitlement details.</value>
public Dictionary<string, EmployeeLeaveEntitlement> LeaveEntitlementDetails { get; set; } 

public List<EmployeeLeaveEntitlement> LeveEntitlementStore
{
    get
    {
        List<EmployeeLeaveEntitlement> leaveEntitlements = new List<EmployeeLeaveEntitlement>();

        foreach (KeyValuePair<string, EmployeeLeaveEntitlement> leaveType in LeaveEntitlementDetails)
        {
            leaveEntitlements.Add(leaveType.Value);
        }

        return leaveEntitlements;
    }
    set
    {
        foreach (EmployeeLeaveEntitlement item in value)
        {
            this.LeaveEntitlementDetails.Add(item.LeaveType, item);
        }
    }
}

Can anyone help me?

Hannele
  • 7,820
  • 5
  • 46
  • 64
iJay
  • 3,925
  • 5
  • 31
  • 62

5 Answers5

22

Entity Framework does not presently support mapping a Dictionary natively.

See the following for more information and work-arounds:

Entity Framework 4 POCO with Dictionary

EF Code First - Map Dictionary or custom type as an nvarchar

http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/a51ba903-2b8b-448e-8677-d140a0b43e89/

Community
  • 1
  • 1
Eric J.
  • 139,555
  • 58
  • 313
  • 529
15

EF Core 2.1 introduced a new feature called value conversion:

Value converters allow property values to be converted when reading from or writing to the database.

This feature highly simplifies the serialization approach mentioned in previous answers, which means, the introduction of on an additional "helper" property and the marking of your dictionary property as [NotMapped] becomes unnecessary.

Here are some lines of code tailored to your case (note, I am using Json.NET, but feel free to use your serializer of choice):

using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;

namespace My.Name.Space
{
    public class MyEntity
    {
        public int Id { get; set; }
        public Dictionary<string, EmployeeLeaveEntitlement> LeaveEntitlementDetails { get; set; } 
    }

    public class MyEntityConfiguration : IEntityTypeConfiguration<MyEntity>
    {
        public void Configure(EntityTypeBuilder<MyEntity> builder)
        {
            builder.ToTable("MyEntity");
            builder.HasKey(e => e.Id);

            builder
            .Property(e => e.LeaveEntitlementDetails)
            .IsRequired()
            .HasConversion(
                v => JsonConvert.SerializeObject(v),
                v => v == null
                    ? new Dictionary<string, EmployeeLeaveEntitlement>() // fallback
                    : JsonConvert.DeserializeObject<Dictionary<string, EmployeeLeaveEntitlement>>(v)
            );
        }
    }
}
B12Toaster
  • 8,944
  • 5
  • 48
  • 48
  • 1
    So you store the dictionary values in a single column as a json serialised key-value pair? I like it, although doesn't this make the database a little bit harder to query directly (i.e. using SQL via SSMS for example)? – mark_h Oct 10 '19 at 10:52
  • 1
    @mark_h good point. When skipping the EF Core layer and executing raw SQL directly, the serialized column will, as you mentioned it, not be nicely queriable with sqlserver since it is encoded. If you are using postgres, you actually have some NoSQL query options at hand and can access parts of the json fields in your SQL query. When using EF Core, you will be able to use normal "LINQ-to-Entity" queries/syntax but the downsite is that EF Core may fail to translate the query to RAW SQL and execute it in memory instead if this encoded column is part of the `Where` expression of your query. – B12Toaster Oct 10 '19 at 13:23
  • thanks, this helped me a lot. To register it, I had to add this configuration to the OnModelCreating(ModelBuilder builder) override in the DBContext declaration with `builder.ApplyConfiguration(new MyEntityConfiguration());` similar to what @Sid mentioned in his answer – Niels Apr 27 '20 at 18:19
  • Doing this on EF Core 3, I get the following warning: « The property '{property}' on entity type '{entityType}' is a collection or enumeration type with a value converter but with no value comparer. Set a value comparer to ensure the collection/enumeration elements are compared correctly. ». Seems like a ValueComparer is required for this to work properly. See: https://github.com/dotnet/efcore/issues/17471 – Xavier Poinas May 31 '20 at 08:48
9

Using a XML Column in DB

So today I came across the same problem, and after thinking about it I found a cool solution which I would like to share with the community even if I am late. Basically I've made a wrapping system which saves the data in the Dictionary to the Database as XML Column, so later I can also query the XML from the DB if I want.

Pro of this approach

  • Easy to use
  • Fast implementation
  • You can use the dictionary
  • You can query the XML column

First of all here's the bone of all my models:

public abstract class BaseEntity 
{
    /// <summary>
    /// ID of the model
    /// </summary>
    public int ID { get; set; }
}

Suppose I have a model which contain a Dictionary<string,string> and a String property which contains the logic to Serialize and Deserialize the dictionary in XML, like the following snippet:

public class MyCoolModel : Base.BaseEntity
{
    /// <summary>
    /// Contains XML data of the attributes
    /// </summary>
    public string AttributesData
    {
        get
        {
            var xElem = new XElement(
                "items",
                Attributes.Select(x => new XElement("item", new XAttribute("key", x.Key), new XAttribute("value", x.Value)))
             );
            return xElem.ToString();
        }
        set
        {
            var xElem = XElement.Parse(value);
            var dict = xElem.Descendants("item")
                                .ToDictionary(
                                    x => (string)x.Attribute("key"), 
                                    x => (string)x.Attribute("value"));
            Attributes = dict;
        }
    }

    //Some other stuff

    /// <summary>
    /// Some cool description
    /// </summary>
    [NotMapped]
    public Dictionary<string, string> Attributes { get; set; }
}

Then I've implemented a BaseMapping class which ineherits from EntityTypeConfiguration<T>

class BaseMapping<TEntity> : EntityTypeConfiguration<TEntity>
    where TEntity : Model.Base.BaseEntity
{
    public BaseMapping()
    {
        //Some basic mapping logic which I want to implement to all my models 
    }
}

And after a Custom Mapping for MyCoolModel

class MyCoolModelMapping
    : BaseMapping<Model.MyCoolModel>
{        
    public MyCoolModelMapping() 
    {
        Property(r => r.AttributesData).HasColumnType("xml");
    }
}

Now notice that when AttributesData value is requested by EntityFramework it just serialize the dictionary and the same happens when I retrive data from the DB and EntityFramework sets the data to the field, which then deserializes the object and sets it to the dict.

And finally I have override the OnModelCreating of my DbContext

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

        modelBuilder.Configurations.Add(new Mappings.BaseMapping<SomeOtherModel>());
        modelBuilder.Configurations.Add(new Mappings.MyCoolModelMapping());
        //Other logic

    }

And that's it! Now I can use the dictionary from my business logic and this "wrapping" handles all the stuff need to save the data to the DB and retrive the data from it.

Sid
  • 11,198
  • 6
  • 35
  • 45
  • 1
    That's an interesting alternative. One limitation is that the underlying database must support XML columns as written (though you could modify the code slightly to not check for that column type). I have started using a similar approach but storing JSON for cases when the columns store data that is used by a JavaScript-based UI and the individual attributes are never queried on the back end. – Eric J. Nov 25 '16 at 19:40
  • @EricJ. Actually yes if there's no need to query on the backend no need to use the column as XML, therefore we can choose to not to use a XML column allowing compatibility with all DBs. Thanks for the feedback :) – Sid Nov 25 '16 at 19:45
2

I had a similar problem with EF were I wanted to convert a query returned list, into a class property's dictionary equivalent. Very similar to how you want to have LeaveEntitlementDetails wrapped by LeveEntitlementStore For example:

class A
{   
    [NotMapped()]
    public Dictionary<int, DataType> Data {get; set}

    //refers to Data.Values
    public ICollection<DataType> DataAsList {get; set}        

}

Where I wanted DataAsList to essentially wrap Data.Values

After a lot of trial and error, I discovered that EF, for collections (maybe more) alters through the getter's returned value (rather than the setter). I.e. when initializing from my db:

var pollquery=From bb In DBM.Dbi.DataTable.Includes("DataAsList")
              Where bb.Id = id
              Select bb;

ClassA objInstance = pollquery.First();

ClassA.DataAsList's setter was never being called, but the getter was during EF's internal construction of my object.... Conclusion: EF is using a reference retrieved from the getter of property ClassA.DataAsList, and adding objects to it.

So I wrapped my getter's return value for DataAsList in an ObservableCollection and added a handler for CollectionChanged args and sure enough, my handler for CollectionChanged was picking up .Add calls.

So heres my hackaround-workaround:

class A : INotifyPropertyChanged
{
    //So we can let EF know a complex property has changed
    public event PropertyChangedEventHandler INotifyPropertyChanged.PropertyChanged;

    //here's our actual data, rather than an auto property, we use an explicit member definition so we can call PropertyChanged when Data is changed
    private Dictionary<int, DataType> m_data = new Dictionary<int, DataType>();
    //not mapped property as it's not mapped to a column in EF DB
    [NotMapped()]
    public Dictionary<int, DataType> Data {
        get { return m_data; }
        set {
            m_data = value;
            //now call PropertyChanged for our Front (so EF will know it's been changed)
            if (PropertyChanged != null) {
                PropertyChanged(this, new PropertyChangedEventArgs("DataAsList"));
            }
        }
    }

    //this is our front for the data, that we use in EF to map data to
    [DebuggerHidden()]
    public ICollection<DataType> DataAsList {
        get {
            ObservableCollection<DataType> ob = new ObservableCollection<DataType>(Data.Values());
            ob.CollectionChanged += Handles_entryListChanged;
            return ob;
        }
        set {
            //clear any existing data, as EF is trying to set the collections value
            Data.Clear();
            //this is how, in my circumstance, i converted my object into the dictionary from an internal obj.Id property'
            foreach (DataType entry in value) {
                entryions.Add(entry.id, entry);
            }
        }
    }
    //This will now catch wind of any changes EF tries to make to our DataAsList property
    public void Handles_entryListChanged(object sender, NotifyCollectionChangedEventArgs e)
    {
        //Debugger.Break()
        switch (e.Action) {
            case NotifyCollectionChangedAction.Add:
                foreach (DataType entry in e.NewItems) {
                    m_data.Add(entry.Id, entry);
                }

                break;
            default:
                Debugger.Break();
                break;
        }
    }
}

Note the Magic is the:

public ICollection<DataType> DataAsList {
    get {
        ObservableCollection<DataType> ob = new ObservableCollection<DataType>(Data.Values());
        ob.CollectionChanged += Handles_entryListChanged;
        return ob;
    }

where we subscribe to any changes made to the returned list and Handles_entryListChanged where we handle and essentially replicate any changes made.

Bunni H
  • 21
  • 1
2

As mentioned in here, One important thing after object serialization, is that when updating the entity and changing items in the dictionary, the EF change tracking does not pick up on the fact that the dictionary was updated, so you will need to explicitly call the Update method on the DbSet<> to set the entity to modify in the change tracker.

there is also another good sample here

Alexander Higgins
  • 6,101
  • 1
  • 16
  • 35
afshar
  • 392
  • 1
  • 12