Transposing Entity Framework EDMX to Code First

Not so long ago we’ve been turning our Entity Framework usage from the “database project + edmx” approach to the “Code First” way. Why did we do that? Because of the pain at deployments when not having “Entity Framework Migrations”. I’ll just list up some of the problems we had, and our solutions to them.

One of the most important points here: when trying to do the same thing, use a schema comparison tool, with which you can ignore column orders :) like SQL Examiner. I’ve found it particularly useful and the trial period is certainly enough for the time of the transposition. It’s a great tool and I’ll probably buy it when I need it again.

Another thing what you probably wanna do is to reverse engineer the database where you have your entities stored with the Entity Framework Power Tools. This is going to create some starting point for your EntityTypeConfiguration maps for the separate entities and contexts.
After doing so you may recognize the following things:

  • The precision is to be fixed for almost every decimal fields
  • Indices were not always correctly created – you have to create them correctly in the EntityTypeConfiguration classes.
  • Foreign keys are not always correctly created – you’ll sometimes need the HasForeignKey extender method
  • Default and check Constraints should be created for example through migrations – if you really need them
  • Stored procedures can be created also by migrations, however the usage of them will be interesting for example as a method on the DbContext class as follows:
            public virtual string RunSomeSp(int id)
            {
                using (var connection = new SqlConnection(Database.Connection.ConnectionString))
                {
                    connection.Open();
                    var command = new SqlCommand("SomeSp", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@Id", id));
                    command.Parameters.Add(new SqlParameter("@SomeOutputParam", SqlDbType.NVarChar, 255, ParameterDirection.Output, false, 0, 0, "SomeOutputParam", DataRowVersion.Default, null));
                    command.ExecuteNonQuery();
                    return (string)command.Parameters["@SomeOutputParam"].Value;
                }
            }
    

    You need it in this way to communicate with the same connection string as the context. And yes it is sadly ADO.NET as the EDMX generated code won’t work with code first.

  • Initial data creation could be solved by using some macro functionality of a text editor
  • Initial int seed values can be configured also in a migration like: Sql(“dbcc checkident(YourTableName, reseed, 500000)”);
  • Database options are a bit more tricky as they cannot be set in multipart transactions (in which migrations run) so we decided to do them manually

In a following post I’m going to talk about how we have been using, configuring and unit testing the Entity Framework Code First Migrations.

Advertisements

About Tamas Nemeth

Husband and proud father of two daughters in Nürnberg. I'm working as a Senior Software Developer and an enthusiastic Clean-Coder. I spend most of my free time with my family (playing, hiking, etc...). I also play table-tennis and badminton sometimes...
This entry was posted in Technical Interest and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s