Entity Framework Connection Strings

In recent months at 3Metas, we've developed several projects using Entity Framework as our data access model. I particularly enjoy the Code First approach, where theoretically I can forget about the database engine and focus on the object model. While not always seamless (sometimes requiring a deep dive into LINQ documentation), the overall experience has been excellent.

How Entity Framework Finds Your Database

One of the most interesting aspects of Entity Framework Code First is its ability to create databases that reflect your model and store data. This is almost magical for developers, but in production environments and corporate applications, more control is needed. That's why it's better to define your own connection string.

This can be confusing initially because you wonder: how does EF know where to connect? Server, user, database name, etc. Looking at the Entity Framework documentation for the DBContext object (from which the database controller inherits), we find this in the constructor:

Further in the class documentation:

Convention-Based Naming

Basically, the database will be named after the class (including namespace) that inherited from DBContext. For example, if my class is called MyNamespace.MyComponent.MyClass, that will be the new database name in a SQL instance called SQLExpress on the machine running the DLL.

If you want to define the database name, you can invoke the DBContext constructor like this:

public MTRDB() : base("MyDB") { }

Where MTRDB is the class name inheriting from DBContext and MyDB is the name for the database in the local SQLExpress instance.

Using Connection Strings

To prevent EF from creating the database or to use an existing database on another server, with a different name, with SQL security, or any combination of these, you must create a ConnectionString key in the config file (web.config or app.config) of the solution referencing the class that inherited from DBContext.

This key must have the same name as the class (if you didn't use the constructor) or the same name used in the constructor. For example, following the previous example, if the app finds a configuration key called MyDB, it will use that connection string information to connect to the database where it expects to find the object models.

Important: MultipleActiveResultSets

An important detail: when using connection strings, include the parameter MultipleActiveResultSets=True. Without it, the DBContext can only handle one result at a time and won't allow executing other commands until the previous process closes.

Code like this:

using (WFEngineDb wfEngineDb = new WFEngineDb())
{
    var fullfilledTransitions = wfEngineDb.Database
        .SqlQuery<FullfiledTransition>(sb.ToString(), activityInstanceId);

    foreach (var fullfilledTransition in fullfilledTransitions)
    {
        if ((fullfilledTransition.Requested - fullfilledTransition.Fullfilled) == 0)
        {
            var transition = wfEngineDb.transitions.Find(fullfilledTransition.Transition_id);
            transitions.Add(transition);
        }
    }
}

Would generate an error because Find cannot execute within the database query operation. The error would be: "There is already an open DataReader associated with this Command which must be closed first."

Real-World Experience

EF's ability to distinguish between development and production based on the presence or absence of the connection string in the solution's config file has proven not only very useful but has become part of our development process, allowing us to quickly and easily test components against multiple environments.

In one of our projects, for example, we had a production database with over 70 million records supporting a website with peaks of up to one million daily visitors, and EF performed wonderfully (after creating indexes and thorough database tuning). We believe it's a mature and robust technology for quickly putting important applications into production.

Note: I'm not an NHibernate expert, but I like that EF Code First requires no configuration files beyond my class to define the model, and when something particular is needed, DataAnnotations does the job very well.