Are EF code models designed to fully describe the structure of a database?

Question

I got a little confused about the data model in Entity Framework code - first. Since EF will automatically generate the database from scratch for you, if it does not already exist, using nothing more than a data model (including data annotations and Fluent API DbContext.OnModelCreating in DbContext.OnModelCreating ), I assumed that the data model should fully describe your database structures, and you would not need to change anything fundamental after that.

However, I ran into this Codeplex issue , in which one of the members of the EF Triage Group team adds that custom indexes are added in data migrations, but not as annotations to your data model or Fluent API fields.

But does this not mean that anyone who generates the database from scratch will not get these custom indexes added to their database? The assumption is that as soon as you start using data migration, you will no longer create the database from scratch. What if you work in a team and a new team member comes with a new installation of SQL Server? Do you expect to copy the database from another team member? What if you want to start using a new DBMS, for example Postgres? I thought that one of the most interesting things about EF is that it is independent of the DBMS, but if you can no longer create a database from scratch, you can no longer do something independent of the DBMS.

For the reasons stated above, would adding a custom index in a data transfer, but not in a data model, be a bad idea? In this case, not adding any changes to the database structure in the migration, but not in the data model, is a bad idea?

Answer 1

Can models with EF code fully describe the structure of the database?

No , they do not fully describe the database structure or schema. In addition, there are methods to fully describe the database using EF. They look like this:

You can use the new CTP5s ExecuteSqlCommand method for the database class, which allows you to execute raw SQL commands in the database.

The best place to call the SqlCommand method for this purpose is inside the Seed method, which has been overridden in the custom Initializer class. For instance:

 protected override void Seed(EntityMappingContext context) { context.Database.ExecuteSqlCommand("CREATE INDEX IX_NAME ON ..."); } 

You can even add unique constraints this way. This is not a workaround, but will be applied since the database will be generated.

OR

If you badly need an attribute, then here it goes

 [AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)] public class IndexAttribute : Attribute { public IndexAttribute(string name, bool unique = false) { this.Name = name; this.IsUnique = unique; } public string Name { get; private set; } public bool IsUnique { get; private set; } } 

After that, you will have an initializer that you call in your OnModelCreating method, as shown below:

 public class IndexInitializer<T> : IDatabaseInitializer<T> where T : DbContext { private const string CreateIndexQueryTemplate = "CREATE {unique} INDEX {indexName} ON {tableName} ({columnName});"; public void InitializeDatabase(T context) { const BindingFlags PublicInstance = BindingFlags.Public | BindingFlags.Instance; Dictionary<IndexAttribute, List<string>> indexes = new Dictionary<IndexAttribute, List<string>>(); string query = string.Empty; foreach (var dataSetProperty in typeof(T).GetProperties(PublicInstance).Where(p => p.PropertyType.Name == typeof(DbSet<>).Name)) { var entityType = dataSetProperty.PropertyType.GetGenericArguments().Single(); TableAttribute[] tableAttributes = (TableAttribute[])entityType.GetCustomAttributes(typeof(TableAttribute), false); indexes.Clear(); string tableName = tableAttributes.Length != 0 ? tableAttributes[0].Name : dataSetProperty.Name; foreach (PropertyInfo property in entityType.GetProperties(PublicInstance)) { IndexAttribute[] indexAttributes = (IndexAttribute[])property.GetCustomAttributes(typeof(IndexAttribute), false); NotMappedAttribute[] notMappedAttributes = (NotMappedAttribute[])property.GetCustomAttributes(typeof(NotMappedAttribute), false); if (indexAttributes.Length > 0 && notMappedAttributes.Length == 0) { ColumnAttribute[] columnAttributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), false); foreach (IndexAttribute indexAttribute in indexAttributes) { if (!indexes.ContainsKey(indexAttribute)) { indexes.Add(indexAttribute, new List<string>()); } if (property.PropertyType.IsValueType || property.PropertyType == typeof(string)) { string columnName = columnAttributes.Length != 0 ? columnAttributes[0].Name : property.Name; indexes[indexAttribute].Add(columnName); } else { indexes[indexAttribute].Add(property.PropertyType.Name + "_" + GetKeyName(property.PropertyType)); } } } } foreach (IndexAttribute indexAttribute in indexes.Keys) { query += CreateIndexQueryTemplate.Replace("{indexName}", indexAttribute.Name) .Replace("{tableName}", tableName) .Replace("{columnName}", string.Join(", ", indexes[indexAttribute].ToArray())) .Replace("{unique}", indexAttribute.IsUnique ? "UNIQUE" : string.Empty); } } if (context.Database.CreateIfNotExists()) { context.Database.ExecuteSqlCommand(query); } } private string GetKeyName(Type type) { PropertyInfo[] propertyInfos = type.GetProperties(BindingFlags.FlattenHierarchy | BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo propertyInfo in propertyInfos) { if (propertyInfo.GetCustomAttribute(typeof(KeyAttribute), true) != null) return propertyInfo.Name; } throw new Exception("No property was found with the attribute Key"); } } 

Then reload OnModelCreating in your DbContext

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { Database.SetInitializer(new IndexInitializer<MyContext>()); base.OnModelCreating(modelBuilder); } 

To apply an index attribute to an Entity type, with this solution you can have several fields in the same index, simply using the same name and unique.

OR

You can migrate later.

Note: I took this code from here .

Answer 2

It seems that the question arises whether there is a value associated with adding migration in the middle of the stream, or if this will cause problems for future database initializations on different machines.

The initial migration that is created also contains the entire data model because it exists, so by adding migrations ( enable-migrations in the package manager console), you actually create an in-built mechanism for your database that is properly created for other developers.

If you do this, I recommend changing the database initialization strategy to start all existing migrations so that EF does not start and the next developer database cannot synchronize.

Something like this will work:

 Database.SetInitializer(new MigrateDatabaseToLatestVersion<YourNamespace.YourDataContext, Migrations.Configuration>()); 

So no, this will not inherently introduce problems for future work / developers. Remember that the migrations have just turned into a valid SQL that runs against the database ... you can even use the script mode to output the TSQL needed to modify the database based on something in the migrations you created.

Greetings.