C# Database ORM and FluentMigrator

We provide a data model generated using linq2DB, allowing us to access our database via linq. A migration tool based on fluent migrator is used for stable and reversible database migrations.

Data Model

linq2db generates the code that eventually creates the data model by compiling a design time text template. The main file for the text compilation is the /DataModel/CsDatabase.tt file. In that file, the database connection information should be provided. Follow the instructions in that file for working with the datamodel.

The data model will be generated on every build process for the project guaranteering an up-to-date state of the data model with respect to migrations. The builer in this case is MSBuild, which does not know relative paths used by linqToDB. That means, if, for instance, some library changes require changes to the text compilation process from the text templates, you need to register the path variables used by linqToDB by unloading and editing the CsDatabase.proj!

Using the data model in your project

Other than including this project, you need to set the configuration string for linqToDb to establish a connection to your database. You must call the constructor for the data model object with the server provider name and the connection string. The connection string has the format: "Data Source={dbDataSource}; Database={dbDatabase}; User Id={dbUserId}; Password={dbPassword};". The provider name is "SqlServer.2008" for our projects. A list of valid alternative names can be found here: https://linq2db.github.io/api/LinqToDB.ProviderName.html For an example look at the unit test in CsDatabase.Tests/ModelTest.cs

Changing the target of the data model

If you want to change the database for which the data model should be created, you need to make a few adjustments:

• The namespace should be adjusted in
  • cs-database/DataModel/CsDatabase.tt
  • cs-database/DataModelConstructor.ttinclude
• The name of the partial class in cs-database/DataModelConstructor.ttinclude
• The configuration string for the database connection will, by default, be fetched from consul. If needed, change the consul query keys in:
  • cs-database/DatabaseConnection.ttinclude
  • Migrator/Migrator.cs
• If you are connecting a database other than msserver, you also must swap the It is recommended to include the cs-database/DataModelConstructor.ttinclude so that the database connection can be established with a programmatically obtained connection string (e.g. using consul) rather than having to include a App.config file to your projects.
• Furhtermore you need to adapt the right _providerName in:
  • CsDatabase.Tests/ModelTest.cs
  • your projects

Fluent Migrator

The fluent migrator provides a command line tool for handling database migrations. Currently, it provides an intermediary interface for utilizing the fluentMigrator up migration (down migration is currently not supported by us) as well as its rollback function, using the command line arguments:

--migrate_up
--roll_back <number of roll back steps>

FluentMigrator will look for migrations in the Migrations directory. While it is, in principle, possible to call the our migrator from other projects as well, it is recommended to have all migrations in this project for greater cohesion and in order to make sure that the data model stays up-to-date. Migrations should be identified by a unique id which corresponds, roughly, to the date and time of its implementation. The format is:

YYYYMMDDhhmm

FluentMigrator uses this identifier to manage its migration versioning. For an example migration, check out the migrations in the test project for the database.