Lance Larsen - .NET Architect Madison Wisconsin

Entity Framework “Code First + Existing Database” 102

Last we saw our intrepid developers they were creating their first “Code First + Migrations” 101 project – today we join them facing the dreaded “we want to do code first, but already have an existing database!!!”.  Can they survive? Absolutely, and here’s how… :)

imageFirst we need to add “Entity Framework Power Tools” – click on “Tools” –> “Extensions and Updates…” –> select “Online” and then search for “Entity Framework Power Tools” –> “Install” and we have added some great new EF Power Tools features!

Now that we have EF Power Tools installed – if we right-click on our project we should see “Entity Framework” as one of the options – and off of that we have “Reverse Engineer Code First” and “Customize Reverse Engineer Templates”.

 

imageClick on “Reverse Engineer Code First” –> brings up our old friend “Connection Properties” – specify your Server and Database.

image

We can see that the following items have been created:

  • “Models” folder – in which we see…
  • “Widget.cs” file that is our “Code First” model file.
  • “MyNewDatabaseContext.cs” file that is our EF DbContext connection to the database.
  • “Mappings” folder in which we see our“WidgetMap.cs” which sets up our mapping using the fluent API. As the documentation states “Items such as column name will always be configured, even when they would be correctly inferred by conventions. This allows you to refactor property/class names etc. without needing to manually update the mapping.”
  • And in our “App.config” we will find our EF connection string to the database.

Let’s first test that what we’ve added works – in our “Program.cs” add essentially the same code that we used in our previous Entity Framework “Code First + Migration” 101 blog post.
 

   1:  using System;
   2:  using CodeFirstConsole102.Models;
   3:   
   4:  namespace CodeFirstConsole102
   5:  {
   6:      class Program
   7:      {
   8:          static void Main(string[] args)
   9:          {
  10:              using (var db = new MyNewDatbaseContext())
  11:              {
  12:                  //----------------------------------------------------
  13:                  // Add a new Widget
  14:                  //----------------------------------------------------
  15:                  db.Widgets.Add(new Widget { Name = "My New Widget!" });
  16:                  db.SaveChanges();
  17:   
  18:                  //----------------------------------------------------
  19:                  // Loop through the widgets from the Database
  20:                  //----------------------------------------------------
  21:                  foreach (var widget in db.Widgets)
  22:                  {
  23:                      Console.WriteLine(widget.Name);
  24:                  }
  25:              }
  26:   
  27:              Console.WriteLine("Press Any Key...");
  28:              Console.ReadKey();
  29:          }
  30:      }
  31:  }

imageRun it!  Awesome – we’re up and running!

Now lets look at the code that was generated more in details.

 

 

So as expected, we see our “Code First” model file “Widget.cs” as follows…
 

   1:  namespace CodeFirstConsole102.Models
   2:  {
   3:      public partial class Widget
   4:      {
   5:          public int Id { get; set; }
   6:          public string Name { get; set; }
   7:          public string Color { get; set; }
   8:      }
   9:  }

The connection string was added to our App.config – same as before…
  

   1:    <connectionStrings>
   2:      <add name="MyNewDatbaseContext" connectionString="Data Source=K9;Initial
   3:  Catalog=MyNewDatbase;Integrated Security=True;MultipleActiveResultSets=True"
   4:        providerName="System.Data.SqlClient" />
   5:    </connectionStrings>

So in the “MyNewDatabaseContext.cs” file that is our EF DbContext connection to the database – it looks almost the same as when we created it manually for our previous Code First approach – but added in the static constructor and the override for OnModelCreating.
  

   1:  using System.Data.Entity;
   2:  using CodeFirstConsole102.Models.Mapping;
   3:   
   4:  namespace CodeFirstConsole102.Models
   5:  {
   6:      public partial class MyNewDatbaseContext : DbContext
   7:      {
   8:          static MyNewDatbaseContext()
   9:          {
  10:              Database.SetInitializer<MyNewDatbaseContext>(null);
  11:          }
  12:   
  13:          public MyNewDatbaseContext()
  14:              : base("Name=MyNewDatbaseContext")
  15:          {
  16:          }
  17:   
  18:          public DbSet<Widget> Widgets { get; set; }
  19:   
  20:          /// <summary>
  21:          /// This method is called when the model for a derived context has 
  22:          /// been initialized, but before the model has been locked down and 
  23:          /// used to initialize the context.  The default implementation of 
  24:          /// this method does nothing, but it can be overridden in a derived 
  25:          /// class such that the model can be further configured before it is 
  26:          /// locked down.
  27:          /// </summary>
  28:          protected override void OnModelCreating(DbModelBuilder modelBuilder)
  29:          {
  30:              modelBuilder.Configurations.Add(new WidgetMap());
  31:          }
  32:      }
  33:  }

The “WidgetMap.cs”, in the “Mappings” folder, is the auto-generated mapping that uses the fluent API technique – it included mappings for each column, even though they can be inferred by conventional naming – i.e. model name matches the database field name.  This is a nice approach if we don’t want them to match – but if they do, feel free to comment these out as well.

If we adhere to the DRY Principle ( Don’t Repeat Yourself ) – I encourage you to remove anything we don’t need – as it again can be inferred by convention for EF.  Comment it out – test your application – if it works, remove it. :)
 

   1:  using System.Data.Entity.ModelConfiguration;
   2:   
   3:  namespace CodeFirstConsole102.Models.Mapping
   4:  {
   5:      public class WidgetMap : EntityTypeConfiguration<Widget>
   6:      {
   7:          public WidgetMap()
   8:          {
   9:              // Primary Key
  10:              this.HasKey(t => t.Id);
  11:   
  12:              // Properties
  13:              // Table & Column Mappings
  14:              this.ToTable("Widgets");
  15:              this.Property(t => t.Id).HasColumnName("Id");
  16:              this.Property(t => t.Name).HasColumnName("Name");
  17:              this.Property(t => t.Color).HasColumnName("Color");
  18:          }
  19:      }
  20:  }

Note – just like in our previous Entity Framework “Code First + Migration” 101 blog post – if you add a property to the “Widget.cs” file – such as:
 

   1:  namespace CodeFirstConsole102.Models
   2:  {
   3:      public partial class Widget
   4:      {
   5:          public int Id { get; set; }
   6:          public string Name { get; set; }
   7:          public string Color { get; set; }
   8:          // New Property!
   9:          public string Size { get; set; }
  10:      }
  11:  }

We then need to click “View” –> “Other Windows” –> “Package Manager Console” – this opens the panel on the bottom of Visual Studio – at the “PM>” prompt, enter the following commands…

PM> Enable-Migrations
  Checking if the context targets an existing database...
  Code First Migrations enabled for project CodeFirstConsole102.
PM> Add-Migration UpgradeWidgetForSize
  Scaffolding migration 'UpgradeWidgetForSize'.
  The Designer Code for this migration file includes a snapshot of your current Code 
First model. This snapshot is used to calculate the changes to your model when
you scaffold the next migration. If you make additional changes to your model
that you want to include in this migration, then you can re-scaffold it by
running 'Add-Migration 201304211944158_UpgradeWidgetForSize' again. PM> Update-Database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying code-based migrations: [201304211944158_UpgradeWidgetForSize]. Applying code-based migration: 201304211944158_UpgradeWidgetForSize. Running Seed method.

We’ve now successfully reverse engineered from an existing database, modified the “Code First” classes and redeployed updates!  Pretty sweet!

 
Comments

Clean simple and a saviour for us enterprise devs with large legacy db.

Now we can version our db and merge it easily without merging strange edm files.

:-)

Leave a Reply