lundi 5 février 2018

How should I model static reference data with Entity Framework?

I have a simulation model that uses a database to store both input and output data, using Entity Framework and the Database First approach. The database is queried through a data access layer more-or-less as described here: https://blog.magnusmontin.net/2013/05/30/generic-dal-using-entity-framework/

However, some of the static input data used is not stored in the database, but is instead hard-coded into the application as fields. This data is genuinely static and will not change while the application is running. For example:

public class Currency
{
    public string Symbol { get; private set; }
    public string Name { get; private set; }

    private Currency()
    {
    }

    // Fields like this store reference data
    public static readonly Currency USD = new Currency
    {
        Symbol = "USD",
        Name = "US Dollar"
    };

    public static readonly Currency EUR = new Currency
    {
        Symbol = "EUR",
        Name = "Euro"
    };
}

This has the advantage that referring to the reference data is as easy as using e.g. Currency.USD throughout the model, without having to go through the data access layer. The disadvantage with how this is implemented is that the data model is clumsy and not really relational anymore (in the sense that relations are enforced through foreign keys); a model object that uses the above reference data like e.g.

public class Transaction
{
    public int Id { get; set; }
    public Currency Currency { get; set; }
    public double Price { get; set; }
}

has a backing table in the DB that looks like this:

create table Transaction
(
    Id int not null primary key,
    Currency nvarchar(3) not null , -- Currency symbol, not a foreign key
    Price float not null 
);

The currency attribute is converted back and forth between a string and an object when reading and writing through the business layer.

I would like to rewrite this with the following goals:

  • Storing static reference data in the DB along with all other data to keep the data model clean.
  • Not having to query the data access layer every time the static reference data is needed (i.e. to get as close to a hard-coded Currency.USD as possible). Throughout a simulation run, the reference data might be read once at startup and then queried 1,000,000,000 times.

Is some sort of caching mechanism what I'm looking for here? Is that likely to be performant enough? What would be an elegant way to solve this in general and for Entity Framework in particular?

Thanks.

Aucun commentaire:

Enregistrer un commentaire