Monthly Archives: September 2013

Code-First Entity Framework’s Fluent API

By Tim Daniels
This is a follow-up to my post on the Code-First Development Workflow. In that post I described how the Code-First workflow automatically generated an object model and a SQL Server database, by implementing the concept of convention over configuration. With this approach, Code-First assumes that the POCO classes defined in the Entities class library, follow the conventions of the schema that Entity Framework uses for a conceptual model. If this is the case, then Entity Framework is able to work out the details of what needs to be done, to form the object model and the relational database. However, if the conventions established in the POCO classes do not produce the desired result, you still have the ability to add configurations to your classes. As we look more closely at this approach, you will see that sometimes the best solution, is a compromise between two opposing techniques and technologies.

When I defined the domain entities for the Action Manager app, I created a one-to-many relationship between the customer object and the project object. In other words, a customer can have one-to-many projects. Here is the code where this is accomplished.

In the customer class, there is a virtual property called Projects, which is defined as a generic list of Project objects. This is known as a navigation property. The project class also has a navigation property of type customer. In this case, the navigation properties establish the convention that a customer can have one to many projects. With this bit of information, Entity Framework created the Customers table and the Projects table in the SQL Server database as illustrated here;

Fluent API Customers table and Projects table

Fluent API Cutomers table and Projects table


As you can see from the code snippet above, I chose not to clutter up the project class with a property for customer ID. However, this caused Entity Framework to create a foreign key relationship between projects and customers that allows the foreign key to be null. Obviously, this was not my intention.

With the code-first fluent API, I can configure the contextual model to generate the foreign key relationship between project and customer ID as not nullable. Here is the code where this is accomplished.

The DbContext class has an overridable method called OnModelCreating. This is where I can tweak the conventions of the entity classes, with specific configurations, to produce the desired result. In this case, the HasRequired method of the modelBuilder class, configures the Customer property of the Project entity, to be required for input. This configuration causes the foreign key relationship between project and customer ID to be defined as not nullable, as illustrated here.

Fluent API Configuration of Customers table and Projects table

Fluent API Configuration of Customers table and Projects table


Here is an outstanding article by Julie Lerman on Code First Relationships Fluent API. Julie covers this subject in more detail with several different examples of configuration through the fluent API.

Managing Database Connections

By Tim Daniels

Before an application can access information from a database, a proper connection to the server hosting the database, and the database itself, must be established. Managing database connections to optimize application performance, and application maintainability, is relatively easy to accomplish on the Microsoft .NET platform. To demonstrate this, I’ve developed a C#.NET class, which utilizes three key components for managing database connections;

Configuration File

First, lets discuss the configuration file. A configuration file in .NET, is simply an XML file with parent/child nodes. Each node can contain a set of both system defined, and programmer defined, key/value pairs. These key/value pairs contain application settings to control specific application and program level functions. The configuration file can be accessed by program code throughout an application. Many of the project templates in Visual Studio will automatically generate a configuration file for the project. However, a configuration file can be manually added to a project simply by right clicking on the project name in the Solution Explorer, taking the add new item menu option, and then selecting Application Configuration File, as illustrated here;

Adding an application config file

Adding an application configuration file to a project in Visual Studio 2012

This will add an XML file called App.config to the Visual Studio project. There is one subtle difference when adding a configuration file to a website project. The configuration file is called Web Configuration File, and adds an XML file called Web.config to the project, as illustrated here;

Adding a web configuration file

Adding a web configuration file to a website project in Visual Studio 2012

The beauty of the configuration file, is that it’s deployed along with the application. Configuration files can be changed as needed. Developers can use configuration files to change settings without recompiling the application. Administrators can use configuration files to set policies that affect how applications run on their computers.

Here is the application configuration file I created for an application I’m currently developing.

As you can see there are several XML nodes dealing with Entity Framework, which is another component I’m using in this application. For this discussion we are only concerned with the “configuration” node and its child node “connectionStrings”.

Now lets look at an example of using the configuration file to manage database connections in an application. Below is a C# class I wrote called “ConnectionsStrings”. Here is how the class is implemented in an application that access a SQL Server database.

Database Connection Management Solution
The objectives for developing a database connection management solution are as follows;

  • Develop a reusable class with flexibility to retrieve a connection string from the App.config file, or the Web.config file, and return the string to the consumer
  • Implementation of the ConfigurationManager class, to take advantage of having the database connection string coded in one place, the App.config file. Thus, eliminating the need to have it hard-coded throughout the application. This improves overall maintainability
  • Implementation of the SqlConnectionStringBuilder class, to supply additional values to the connection string, such as user name and password

Configuration Manager
The ConnectionStrings class below, has a method called BuildConnectionString. You will notice that the method is also overloaded with a version that will accept the following parameters;

  • dataSource
  • userName
  • userPassword

The BuildConnectionString method implements two classes from the System.Configuration name space.

The ConfigurationManager class has a property called ConnectionStrings. This property gets the connection strings section data from the current application’s default configuration file. It returns a connection string settings collection object. This collection object holds the contents of the connection strings section of the configuration file. Refer back to the configuration file example above to see the contents of the connection strings section. Remember how I described the contents of the configuration file as key/value pairs. In this case, the key is name=”DbConnection”. The value associated with this key is connectionString=”Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True”.

The following line of code retrieves the value associated with “DbConnection” and assigns it to variable called settings which is a type of ConnectionStringSettings.

Don’t forget to add a reference to the System.Configuration name space in your project and put the using System.Configuration statement at the top of the class.

Connection String Builder
Now lets take a look a the SqlConnectionStringBuilder class. This class comes in handy when additional information needs to be added to the connection string in the configuration file. In cases where it may not be practical to store the connection string in a configuration file, it can be used to build the entire connection string from a set of parameters. The SQLConnectionStringBuilder class has numerous properties for configuring all kinds of settings related to database connectivity. In my ConnectionStings class, I overloaded the BuildConnectionString method to accept data source, user name and password as parameters, that supply additional information for the connection string retrieved from the configuration file.

Final Implementation
In order to make the ConnectionStrings class as reusable as possible, I’m hosting it within a separate class library called “Connections”. This class library is referenced by a console application project I developed for unit testing purposes. This is illustrated in the solution explorer of Visual Studio 2012.

Solution Explorer with Connections class library

Solution Explorer with Connections class library


Finally, here is one implementation of the ConnectionStrings class, where it is used to populate the connection string property of a database instance of a database context class.