Entity Framework on MySQL with nHydrate

Introduction

Entity Framework works very well with SQL Server. There are many constructs and conveniences that make using Entity Framework with SQL Server very nice. nHydrate has built for years a layer on top of EF to add an additional modeling layer with more functionality. Now it has moved into multi-database support. MySQL is a popular database platform with a large install base. There is a plugin for EF that allows you to use the default designer with a MySQL database. This has the normal limitations of Entity Framework. Now you can use the nHydrate modeler to create and manage your MySQL implementation. 

Additional Resources 

• MySQL Sample Site
• MySQL Sample Source
• nHydrate CodePlex Home

Features 

For the same reasons as using nHydrate with SQL Server, you can now use MySQL as well. Listed below are some of the reasons to use the nHydrate modeller.

• Refactor Rules 
• Distributed Model
• Enumeration tables (typed entities)
• Static data 
• Incremental database upgrades
• Database versioning
• Row-level audit tracking
• Table-level auditing
• Modular development
• Model validation rules
• Generated CRUD layer
• Robust database installer 
• Model/Metadata based prompting and validation

Now you can use the same nHydrate modeller to create an interface-based, Entity Framework layer with mocks and a database installer that you use to manage your SQL Server installation.

Database Upgrades

A big pain point when using MySQL is database upgrades. MySQL does not support logic (control flow) nearly as well as SQL Server. In SQL Server you can write an “if exists” statement and check for fields, indexes, tables, etc very easily in an upgrade script. While you can do most of this in MySQL it is more of a challenge as you have to wrap this control flow in routines (functions). This makes upgrading a database more cumbersome. Putting this aside even in SQL Server, the main issue is writing and managing upgrade scripts in the first place. This is the big win when using nHydrate.

As you change a model, the differences are tracked and a difference (upgrade) script is emitted into a single place: the database installer project. Used in conjunction with the database versioning feature, upgrades of databases are trivial. Each database is versioned by the installer each time it is run. The next time the installer is run, the database version is checked and all necessary upgrade scripts are executed to bring the database up to the latest version.

To learn more about database upgrades read the following CodeProject article: http://www.codeproject.com/Articles/160388/Track-database-changes-with-nHydrate.

Modular Development

Modules are an advanced feature useful for large, multi-developer projects. The concept allows you to group model objects into logical modules that will be created as completely separate projects and assemblies in Visual Studio. A Module is a logical grouping that will manifest itself as a separate API/assembly. This allows you to have a common model and share it amongst development teams. This is quite useful for teams that work on products that have a common database but are doing parallel development and should emit different APIs/application parts/assemblies.

To learn more about modular development see the following CodeProject article: http://www.codeproject.com/KB/codegen/417753.aspx.

Auditing

Many times you want to track changes to database rows. Often this involves added a created by and date field as well as a modified by and date field. These fields must be manually set in code to track who is making changes. This is a very manual process and should be automated. nHydrate allow you simply mark a database table as audited and all this information is tracked for you. When an Entity Framework context is declared the modifier is passed in. From that point onward, when an entity (database table) is created or modified, its modified and associated date and time are managed in the database without any code needing to be written or managed.

Another great feature is concurrency management. A problem in a multi-user environment is concurrency. You do not want users to loose updates. MySQL has no built in concurrency tracking. Using the nHydrate platform, simply mark an entity as AllowTimestamp true and concurrency is handled. Also note that timestamps are not based on the MySQL timestamp data type. The implementation uses a model based on SQL Server’s binary field row version. This is not used for last modified time. There is a modified audit to track change time. The timestamp is a real concurrency mechanism that is sorely missing from MySQL.

Distributed Model

A big issue with team development is model editing. Entity Framework by default has a single file where all model information is housed, even storing the diagram. This is fine for a single developer; however when many people are working on a team, this becomes a major pain point. When using a version control system like subversion, the file differences cause a lot of conflicts even for very minor edits. nHydrate allows a model to be broken up into many disk files. When updating a model only small edits are made to files and so version management becomes much easier. Model conflicts will be very few using this feature. A conflict could still potentially arise if two users are editing the exact same model element like a table column; however barring very close edits like this, conflicts will be mostly eliminated.

Documentation Website

Another nice feature is the ability to generate a documentation website. Almost all model objects have documentation. You may add text that is used for Intellisense inside of Visual Studio as well as creating a website from it. The documentation website is a fully hyperlinked page set for all tables, views, stored procedures, and functions. The table pages define all relations and each is hyperlinked to its related tables. The website describes the structure of your database so all stakeholders can get an overview the data structure without using any development tools.

Extensibility

All code is generated from generation templates based on common interfaces. This allows you to write your own generators and drop them into the system if desired. If your company has a custom project or file that is based on the model, simply create a new generator library and drop it in the installation folder to use it. It will be picked up by the framework. The next time you generate, the library will be available to choose from in the list of generators.

Dual database development

Another advantage is the ability to manage two databases from the same model. A single model can generate and manage both a MySQL and a SQL Server implementation. If you have this requirement then using the generation platform makes management much easier.