Entity Framework Code First
Difference between code first and DB first
In code first approach we have
- entity to tables mapping in the C# code instead of .edmx XML file
- use a general connection string. We don't need to specify configuration files in the connection string
- We create all entities and mapping manually instead of code generation in DB first
- EF can update DB with using our mapping
Entities and mapping
We create entities as general C# objects. Most of them are the same as
Mapping will bind entities to the DB tables. We can apply mapping in two ways:
- Use code annotations attributes
- Use fluent API with DbModelBuilder
- We can combine these two approaches in the same context. (we apply this approach in the Utopia)
- Use separately classes with mapping for each entity. (This approach we used in workflows.)
mapping example:
Mapping by attributes
public partial class DbNode
{
[Key]
public long Id { get; set; }
[Required]
[StringLength(255)]
public string Name { get; set; }
Mapping by modelBuilder
public partial class UtopiaDB : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// map foreign key.One to many
modelBuilder.Entity<DbNode>()
.HasMany(e => e.DbEmailQueueAttachmentNodes)
.WithRequired(e => e.DbNode)
.HasForeignKey(e => e.NodeID)
.WillCascadeOnDelete(false);
// map foreign key. many to many
modelBuilder.Entity<DbSearchCriteria>()
.HasMany(e => e.DbTriggers)
.WithMany(e => e.DbSearchCriterias)
.Map(m => m.ToTable("DbTriggerToSearchCriteria")
.MapLeftKey("SearchCriteriaId")
.MapRightKey("TriggerId"));
// map complex primary key
modelBuilder.Entity<DbTriggerInfoNodes>()
.HasKey(e => new {e.TriggerID, e.NodeInfoType});
}
}
Mapping by mapping class (used in WF)
public class DbWorkflowMap : EntityTypeConfiguration<Workflow>
{
public DbWorkflowMap(string tablePrefix)
{
ToTable(tablePrefix + "DbWorkflows");
HasKey(t => t.Id);
Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
HasMany(t => t.Owners).WithMany().Map(a => a.MapLeftKey("WorkflowID")
.MapRightKey("UserID")
.ToTable(tablePrefix + "DbWorkflowOwners"));
Migration
To update the DB structure we use migration.
- Migration is a C# class that contains updating instruction.
- EF can generate migrations automatically
How to change structure
- Add changes to entities and mapping if it needed
- Create a migration for this changes. To make it automatically create a migration, run this command in the Package Manager Console:
Add-Migration "MigrationNameHere" -ProjectName DataAccess -ConnectionString "data source=(localdb)\MSSQLLocalDB;initial catalog=UtopiaDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" -ConnectionProviderName System.Data.SqlClient -ConfigurationTypeName UtopiaDBMigrationConfiguration
- After this command EF will generate C# class for this migration. Please check it
public partial class InitialMigration : DbMigration
{
public override void Up()
{
//Some instructions
}
public override void Down()
{
}
}
- To apply migration to the DB you can run next command:
Update DB to the last state
update-database -verbose -ProjectName DataAccess -ConnectionString "data source=(localdb)\MSSQLLocalDB;initial catalog=UtopiaDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" -ConfigurationTypeName UtopiaDbMigrationConfiguration -ConnectionProviderName System.Data.SqlClient
Update/Downgrade db to a specific migration.
update-database -TargetMigration:<Insert migration name> -verbose -ProjectName DataAccess -ConnectionString "data source=(localdb)\MSSQLLocalDB;initial catalog=UtopiaDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" -ConfigurationTypeName UtopiaDbMigrationConfiguration -ConnectionProviderName System.Data.SqlClient
Update for updating remote DB's/connections that require authentication:
update-database -verbose -ProjectName DataAccess -ConnectionString "data source={databaseserveraddress};User={databaseUsername};password={password};initial catalog={schemaName};integrated security=False;MultipleActiveResultSets=True;App=EntityFramework" -ConfigurationTypeName UtopiaDbMigrationConfiguration -ConnectionProviderName System.Data.SqlClient
- Applied migrations are stored in
__MigrationHistory
table
PostgreSQL process
There are several things that are different during PostgreSQL Code-First Migration process:
- Update DB to the last state
update-database -verbose -ProjectName DataAccess -ConnectionString "Host=localhost;Database=UtopiaDB;Integrated Security=False;Username=postgres;Password=pass@word1;Port=5432" -ConfigurationTypeName UtopiaDbMigrationConfiguration -ConnectionProviderName Npgsql
-
If Package Manager Console cannot update database, DbMigratorEF project can be used. Pay attention to constants and main method before running it, main methods are UpdateDatabase(config) and ScaffoldNewMigration(config)
-
During master merging into feature/OnPremiseMaster new migrations should be deleted and rescafolded. If "dbo" scheme appears after migration, that means one or more migrations are not deleted.
Generate SQL for migration:
Update-Database -Script -SourceMigration: [CurrentAppliedMigrationName] -TargetMigration: [MigrationToUpdateToName]
Update for updating remote DB's/connections that require authentication:
update-database -verbose -ProjectName DataAccess -ConnectionString "data source={databaseserveraddress};User={databaseUsername};password={password};initial catalog={schemaName};integrated security=False;MultipleActiveResultSets=True;App=EntityFramework" -ConfigurationTypeName UtopiaDbMigrationConfiguration -ConnectionProviderName System.Data.SqlClient
Update DB to the last state - NO VERBOSE
update-database -ProjectName DataAccess -ConnectionString "data source=(localdb)\MSSQLLocalDB;initial catalog=UtopiaDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" -ConfigurationTypeName UtopiaDbMigrationConfiguration -ConnectionProviderName System.Data.SqlClient
This message comes through when I went and changed DbNodeClosures ID, needing to drop the restraint and change the Primary Key name. We were forcing users to Manual to change these, but we can use this script (or a variant of it) to do the rename automatically.
From Roman Alekhin
@BryanC this script searching constraint name in sys.key_constraints which contains constrains names for all tables. We just specify table name and constraint type (PK or UK) and receive for example primary key name for specific DB. As result we will receive correct name for any person's DB.
This will find PK or UK by table name in sys.key_constraints and generate SQL script for dropping it
This just replaces <constraitType> macro to PK
And this drops PK for all tables in list: