Entity Framework : Create a model from Dictionary<TKey,TValue> to be mapped to a database table

Question

Earlier I had a table named ApplicationConfiguration which simply had [Key],[Value] columns to store some config data. This was queried straight away using SQL queries.

Now I intend to make use of Entity Framework (EF) Code First approach to query this table. The specialty of this table is that the table will have only a fixed number of rows in its lifetime. Only the Value column can be updated.

So as per the code first approach, we have to first write our POCO classes with its properties that will be mapped to columns in the underlying table. However, I wish to have a Dictionary<> structure to represent these configuration KV pairs. My concern is, will EF be able to fire update queries against any updation to the the value of a particular pair.

Also since I am using Code First approach, I would want some seed data(i.e the fixed number of rows and its initial content) to the added after the table itself is created on the fly when the application is first executed.

If Dictionary<> cannot be used, please suggest some alternative. Thanks in advance.

Answer 1

Coded this way:

public class ApplicationConfiguration
{
    public int Id { get; set; }
    public string Key { get; set; }
    public int Value { get; set; } // should be string, but I'm lazy
}

class Context : DbContext
{
    internal class ContextInitializer : DropCreateDatabaseIfModelChanges<Context>
    {
        protected override void Seed(Context context)
        {
            var defaults = new List<ApplicationConfiguration>
            {
                new ApplicationConfiguration {Key = "Top", Value = 5},
                new ApplicationConfiguration {Key = "Bottom", Value = 7},
                new ApplicationConfiguration {Key = "Left", Value = 1},
                new ApplicationConfiguration {Key = "Right", Value = 3}
            };

//            foreach (var c in defaults)
//                context.ConfigurationMap.Add(c.Key, c); // by design, no IReadOnlyDictionary.Add

            foreach (var c in defaults)
                context.ApplicationConfigurations.Add(c);

            base.Seed(context);
        }
    }

    public Context()
    {
        Database.SetInitializer(new ContextInitializer());
    }

    private IDbSet<ApplicationConfiguration> ApplicationConfigurations
    {
        get { return Set<ApplicationConfiguration>(); }
    }

    public IReadOnlyDictionary<string, ApplicationConfiguration> ConfigurationMap
    {
        get { return ApplicationConfigurations.ToDictionary(kvp => kvp.Key, kvp => kvp); }
    }
}

Used this way:

using (var context = new Context())
{
    ReadConfigurationOnly(context.ConfigurationMap);
}

using (var context = new Context())
{
    ModifyConfiguration(context.ConfigurationMap);
    context.SaveChanges();
}

static void ReadConfigurationOnly(IReadOnlyDictionary<string, ApplicationConfiguration> configuration)
{
    foreach (var k in configuration.Keys)
        Console.WriteLine("{0} = {1}", k, configuration[k].Value);
}

static void ModifyConfiguration(IReadOnlyDictionary<string, ApplicationConfiguration> configuration)
{
    foreach (var k in configuration.Keys)
        configuration[k].Value++; // this is why I was lazy, using an int for a string
}

So, I wrote it up this way — using an int Value property rather than a string — just so I could run the "Used this way" code over and over, and see the database update each time, without having to come up with some other way to change Value in an interesting way.

It's not quite as nifty here to use a IReadOnlyDictionary<string, ApplicatonConfiguration> instead of a IReadOnlyDictionary<string, string>, the way we'd really like, but that's more than made up for by the fact that we can easily modify our collection values without resorting to a clumsier Set method taking a dictionary as input. The drawback, of course, is that we have to settle for configuration[key].Value = "new value" rather than configuration[key] = "new value", but — as I say — I think it's worth it.

EDIT

Dang! I wrote this code up specifically to answer this question, but I think I like it so much, I'm going to add it to my bag of tricks ... this would fit in really well when my company goes from local databases to Azure instances in the cloud, and the current app.config has to go into the database.

Now all I need is a ContextInitializer taking a System.Configuration.ConfigurationManager as a ctor parameter in order to seed a new database from an existing app.config ...

Answer 2

I don't think you can map a table directly to a Dictionary; you will probably have to write your own wrapper to fill a dictionary from the table and update it back to the DB. Entities are each a row of a given table... Something like this (untested):

public Dictionary<string, string> GetDictionary()
{
Dictionary<string, string> dic = new Dictionary<string, string>();
using (var db = new Context())
{
    var configs = db.ApplicationConfiguration.Select();

    foreach (var entry in configs)
    {
       dic.Add(config.Key, config.Value);
    }
}
    return dic;
}

public void SaveConfig(Dictionary<string, string> dic)
{
    using (var db = new Context())
    {
        foreach (KeyValuePair kvp in dic)
        {
            if (!db.ApplicationConfiguration.First(a => a.Key == kvp.Key).Value == kvp.Value)
            {
                var ac = new ApplicationConfiguration();
                ac.Key = kvp.Key;
                ac.Value = kvp.Value;
                db.Entry(ac).State = EntityState.Modified;
            }
        }
        db.SaveChanges();
    }
}

For your second question, you want to use the Seed() method to add initial values to the database. See here for an example implementation.