Entity Framework CTP 4 – Inizializzazione del database personalizzato First Code

Vorrei implementare una strategia di inizializzazione del database personalizzata in modo che possa generare lo schema del database e applicarlo a un database SQL VUOTO ESISTENTE utilizzando un ID utente e una password forniti.

Sfortunatamente le strategie integrate non forniscono quello che sto cercando:

// The default strategy creates the DB only if it doesn't exist - but it does // exist so this does nothing Database.SetInitializer(new CreateDatabaseOnlyIfNotExists()); // Drops and re-creates the database but then this breaks my security mapping and // only works if using a “Trusted" connection Database.SetInitializer(new RecreateDatabaseIfModelChanges()); // Strategy for always recreating the DB every time the app is run. – no good for // what I want Database.SetInitializer(new AlwaysRecreateDatabase()); 

Ho elaborato quanto segue ma ciò non crea il ModelHash, quindi non sono in grado di utilizzare “context.Database.ModelMatchesDatabase ()” per verificare che lo schema del database sia stato creato e prevenire più inizializzazioni:

 public class Initializer : IDatabaseInitializer { Public void InitializeDatabase(DataContext context) { // this generates the SQL script from my POCO Classes var sql = context.ObjectContext.CreateDatabaseScript(); // As expected - when run the second time it bombs out here with "there is already an // object named xxxxx in the database" context.ObjectContext.ExecuteStoreCommand(sql); this.seed(context) context.SaveChanges(); } } 

Domande:

Qualcuno sa come posso ottenere / creare l’hash del modello? (che è un’ quadro EdmMetadata)

-O-

C’è un modo migliore per farlo in generale usando il Code First CTP?

Ho incontrato lo stesso problema. Non l’ho davvero risolto, ma sono riuscito a ottenere un po ‘di brutto trucco, quindi posso installare la mia soluzione su AppHarbor;)

È un’implementazione di IDatabaseInitializer, che non elimina il db, ma semplicemente usa tutte le regole e i vincoli e quindi usa il metodo ObjectContext.CreateDatabaseScript () per generare lo sql, e quindi lo eseguo come storecommand. Molto simile all’implementazione di cui sopra nella domanda.

Ma ho anche aggiunto funzionalità per creare un hash dal modello e salvarlo in db, e quando viene eseguito di nuovo controlla se l’hash del modello attuale corrisponde a quello di i db. Proprio come la vera implementazione del codice.

Non riuscivo a farlo funzionare con la build in context.Database.CompatibleWithModel (true) – ma dovrebbe funzionare altrettanto bene, e visto che è una soluzione temporanea dovrebbe andare bene.

 using System; using System.Data.Entity; using System.Data.Entity.Database; using System.Data.Entity.Design; using System.Data.Entity.Infrastructure; using System.Data.Metadata.Edm; using System.Data.Objects; using System.Globalization; using System.Security.Cryptography; using System.Text; using System.Xml; using System.Linq; namespace Devtalk { public class DontDropDbJustCreateTablesIfModelChanged : IDatabaseInitializer where T : DbContext { private EdmMetadata _edmMetaData; public void InitializeDatabase(T context) { ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; string modelHash = GetModelHash(objectContext); if (CompatibleWithModel(modelHash, context, objectContext)) return; DeleteExistingTables(objectContext); CreateTables(objectContext); SaveModelHashToDatabase(context, modelHash, objectContext); } private void SaveModelHashToDatabase(T context, string modelHash, ObjectContext objectContext) { if (_edmMetaData != null) objectContext.Detach(_edmMetaData); _edmMetaData = new EdmMetadata(); context.Set().Add(_edmMetaData); _edmMetaData.ModelHash = modelHash; context.SaveChanges(); } private void CreateTables(ObjectContext objectContext) { string dataBaseCreateScript = objectContext.CreateDatabaseScript(); objectContext.ExecuteStoreCommand(dataBaseCreateScript); } private void DeleteExistingTables(ObjectContext objectContext) { objectContext.ExecuteStoreCommand(Dropallconstraintsscript); objectContext.ExecuteStoreCommand(Deletealltablesscript); } private string GetModelHash(ObjectContext context) { var csdlXmlString = GetCsdlXmlString(context).ToString(); return ComputeSha256Hash(csdlXmlString); } private bool CompatibleWithModel(string modelHash, DbContext context, ObjectContext objectContext) { var isEdmMetaDataInStore = objectContext.ExecuteStoreQuery(LookupEdmMetaDataTable).FirstOrDefault(); if (isEdmMetaDataInStore == 1) { _edmMetaData = context.Set().FirstOrDefault(); if (_edmMetaData != null) { return modelHash == _edmMetaData.ModelHash; } } return false; } private string GetCsdlXmlString(ObjectContext context) { if (context != null) { var entityContainerList = context.MetadataWorkspace.GetItems(DataSpace.SSpace); if (entityContainerList != null) { EntityContainer entityContainer = entityContainerList.FirstOrDefault(); var generator = new EntityModelSchemaGenerator(entityContainer); var stringBuilder = new StringBuilder(); var xmlWRiter = XmlWriter.Create(stringBuilder); generator.GenerateMetadata(); generator.WriteModelSchema(xmlWRiter); xmlWRiter.Flush(); return stringBuilder.ToString(); } } return string.Empty; } private static string ComputeSha256Hash(string input) { byte[] buffer = new SHA256Managed().ComputeHash(Encoding.ASCII.GetBytes(input)); var builder = new StringBuilder(buffer.Length * 2); foreach (byte num in buffer) { builder.Append(num.ToString("X2", CultureInfo.InvariantCulture)); } return builder.ToString(); } private const string Dropallconstraintsscript = @"select 'ALTER TABLE ' + so.table_name + ' DROP CONSTRAINT ' + so.constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so"; private const string Deletealltablesscript = @"declare @cmd varchar(4000) declare cmds cursor for Select 'drop table [' + Table_Name + ']' From INFORMATION_SCHEMA.TABLES open cmds while 1=1 begin fetch cmds into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds deallocate cmds"; private const string LookupEdmMetaDataTable = @"Select COUNT(*) FROM INFORMATION_SCHEMA.TABLES T Where T.TABLE_NAME = 'EdmMetaData'"; } } 

Questo è il modo più semplice per ottenere il codice EF per la prima volta su AppHarbor !

Utilizzando la funzione EdmMetadata.TryGetModelHash(context) per verificare quando il modello non corrisponde al database e mostrando un errore con il nuovo codice che deve essere utilizzato dopo aver eseguito gli script di alterazione.

PopulateOnly: crea oggetti solo quando il database è vuoto

Ho pensato di pubblicare la mia versione di Initializer che sto attualmente utilizzando su appharbor per popolare un database esistente . Proverà anche a fare una creazione se il database non esiste e getta se viene rilevata una modifica (mi spiace nessun aggiornamento automatico ancora). Spero che qualcuno lo trovi utile.

  using System; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.Objects; using System.Transactions; namespace Deskspace.EntityFramework { ///  A Database Initializer for appharbor  /// Code first context public class PopulateOnly : IDatabaseInitializer where T : DbContext { private EdmMetadata metadata; private enum Status { Compatable, Invalid, Missing } ///  Initializer that supports creating or populating a missing or empty database  ///  Context to create for  public void InitializeDatabase(T context) { // Get metadata hash string hash = EdmMetadata.TryGetModelHash(context); bool exists; using (new TransactionScope( TransactionScopeOption.Suppress )) { exists = context.Database.Exists(); } if (exists) { ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; var dbHash = GetHashFromDatabase( objectContext ); Status compatability = string.IsNullOrEmpty( dbHash )? Status.Missing : (dbHash != hash)? Status.Invalid : Status.Compatable; if (compatability == Status.Missing) { // Drop all database objects ClearDatabase( objectContext ); // Recreate database objects CreateTables( objectContext ); // Save the new hash SaveHash( objectContext, hash ); } else if (compatability == Status.Invalid) { throw new Exception( "EdmMetadata does not match, manually update the database, expected: " + Environment.NewLine + "<[(" + hash + ")}>" ); } } else { context.Database.Create(); context.SaveChanges(); } } private void ClearDatabase(ObjectContext objectContext) { objectContext.ExecuteStoreCommand( DropAllObjects ); } private void CreateTables(ObjectContext objectContext) { string dataBaseCreateScript = objectContext.CreateDatabaseScript(); objectContext.ExecuteStoreCommand( dataBaseCreateScript ); } private void SaveHash(ObjectContext objectContext, string hash) { objectContext.ExecuteStoreCommand( string.Format(UpdateEdmMetaDataTable, hash.Replace( "'", "''" )) ); } private string GetHashFromDatabase(ObjectContext objectContext) { foreach (var item in objectContext.ExecuteStoreQuery( GetEdmMetaDataTable )) { return item; } return string.Empty; } private const string UpdateEdmMetaDataTable = @" Delete From EdmMetadata; Insert Into EdmMetadata (ModelHash) Values ('{0}');"; private const string GetEdmMetaDataTable = @" If Exists (Select * From INFORMATION_SCHEMA.TABLES tables where tables.TABLE_NAME = 'EdmMetaData') Select Top 1 ModelHash From EdmMetadata; Else Select '';"; private const string DropAllObjects = @" declare @n char(1) set @n = char(10) declare @stmt nvarchar(max) -- procedures select @stmt = isnull( @stmt + @n, '' ) + 'drop procedure [' + name + ']' from sys.procedures -- check constraints select @stmt = isnull( @stmt + @n, '' ) + 'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.check_constraints -- functions select @stmt = isnull( @stmt + @n, '' ) + 'drop function [' + name + ']' from sys.objects where type in ( 'FN', 'IF', 'TF' ) -- views select @stmt = isnull( @stmt + @n, '' ) + 'drop view [' + name + ']' from sys.views -- foreign keys select @stmt = isnull( @stmt + @n, '' ) + 'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.foreign_keys -- tables select @stmt = isnull( @stmt + @n, '' ) + 'drop table [' + name + ']' from sys.tables -- user defined types select @stmt = isnull( @stmt + @n, '' ) + 'drop type [' + name + ']' from sys.types where is_user_defined = 1 exec sp_executesql @stmt"; } } 

Solo per contribuire alla soluzione di @ Luhmann, ecco la mia ma leggermente modificata per far cadere correttamente FK e PK.

 using System.Data.Entity; using System.Data.Entity.Design; using System.Data.Entity.Infrastructure; using System.Data.Metadata.Edm; using System.Data.Objects; using System.Globalization; using System.Linq; using System.Security.Cryptography; using System.Text; using System.Xml; namespace SISQuote.Server.Persistence { public class DontDropExistingDbCreateTablesIfModelChanged : IDatabaseInitializer where T : DbContext { private EdmMetadata edmMetaData; public bool TryInitializeDatabase(T context) { ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; string modelHash = GetModelHash(objectContext); if (CompatibleWithModel(modelHash, context, objectContext)) return false; DeleteExistingTables(objectContext); CreateTables(objectContext); SaveModelHashToDatabase(context, modelHash, objectContext); return true; } public void InitializeDatabase(T context) { TryInitializeDatabase(context); } private void SaveModelHashToDatabase(T context, string modelHash, ObjectContext objectContext) { if (edmMetaData != null) objectContext.Detach(edmMetaData); edmMetaData = new EdmMetadata(); context.Set().Add(edmMetaData); edmMetaData.ModelHash = modelHash; context.SaveChanges(); } private void CreateTables(ObjectContext objectContext) { string dataBaseCreateScript = objectContext.CreateDatabaseScript(); objectContext.ExecuteStoreCommand(dataBaseCreateScript); } private void DeleteExistingTables(ObjectContext objectContext) { objectContext.ExecuteStoreCommand(DeleteAllTablesScript); } private string GetModelHash(ObjectContext context) { var csdlXmlString = GetCsdlXmlString(context).ToString(); return ComputeSha256Hash(csdlXmlString); } public bool CompatibleWithModel(DbContext context) { ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; return CompatibleWithModel(GetModelHash(objectContext), context, objectContext); } private bool CompatibleWithModel(string modelHash, DbContext context, ObjectContext objectContext) { var isEdmMetaDataInStore = objectContext.ExecuteStoreQuery(LookupEdmMetaDataTable).FirstOrDefault(); if (isEdmMetaDataInStore == 1) { edmMetaData = context.Set().FirstOrDefault(); if (edmMetaData != null) { return modelHash == edmMetaData.ModelHash; } } return false; } private string GetCsdlXmlString(ObjectContext context) { if (context != null) { var entityContainerList = context.MetadataWorkspace.GetItems(DataSpace.SSpace); if (entityContainerList != null) { EntityContainer entityContainer = entityContainerList.FirstOrDefault(); var generator = new EntityModelSchemaGenerator(entityContainer); var stringBuilder = new StringBuilder(); var xmlWRiter = XmlWriter.Create(stringBuilder); generator.GenerateMetadata(); generator.WriteModelSchema(xmlWRiter); xmlWRiter.Flush(); return stringBuilder.ToString(); } } return string.Empty; } private static string ComputeSha256Hash(string input) { byte[] buffer = new SHA256Managed().ComputeHash(Encoding.ASCII.GetBytes(input)); var builder = new StringBuilder(buffer.Length * 2); foreach (byte num in buffer) { builder.Append(num.ToString("X2", CultureInfo.InvariantCulture)); } return builder.ToString(); } private const string DeleteAllTablesScript = @"declare @cmd varchar(4000) DECLARE cmds0 CURSOR FOR SELECT 'ALTER TABLE ' + TABLE_NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' DECLARE cmds1 CURSOR FOR SELECT 'ALTER TABLE ' + TABLE_NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS DECLARE cmds2 CURSOR FOR SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES DECLARE cmds3 CURSOR FOR SELECT 'DROP TABLE [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES open cmds0 while 1=1 begin fetch cmds0 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds0 deallocate cmds0 open cmds1 while 1=1 begin fetch cmds1 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds1 deallocate cmds1 open cmds2 while 1=1 begin fetch cmds2 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds2 deallocate cmds2 open cmds3 while 1=1 begin fetch cmds3 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds3 deallocate cmds3"; private const string LookupEdmMetaDataTable = @"Select COUNT(*) FROM INFORMATION_SCHEMA.TABLES T Where T.TABLE_NAME = 'EdmMetaData'"; } } 

Ho preso un approccio leggermente diverso a questo problema. Questo sembra un buon posto per condividere i risultati.

Voglio solo creare tabelle che non esistono già nel database. Questo ha il vantaggio di essere in grado di distribuire nuove tabelle senza cancellare il resto del database.

Questo aiuta anche se si hanno più contesti di dati in una catena di ereditarietà. Ad esempio, se hai diviso la tua applicazione in diversi gruppi. Si potrebbe avere un contesto di dati in un modulo “principale” e quindi ereditarlo in un diverso assieme per moduli aggiuntivi. Questa configurazione funziona bene, ma gli inizializzatori Drop / Create incorporati non gli piacciono perché l’hash del modello cambia continuamente. Controllando l’esistenza della tabella, l’inizializzazione richiede un po ‘più tempo, ma non si ha nessuno di questi problemi.

Ad ogni modo, ecco il codice:

 ///  /// Database Initializer to create tables only if they don't already exist. /// It will never drop the database. Does not check the model for compatibility. ///  /// The data context public class CreateTablesOnlyIfTheyDontExist : IDatabaseInitializer where TContext : DataContext { public void InitializeDatabase(TContext context) { using (new TransactionScope(TransactionScopeOption.Suppress)) { // If the database doesn't exist at all then just create it like normal. if (!context.Database.Exists()) { context.Database.Create(); return; } // get the object context var objectContext = ((IObjectContextAdapter)context).ObjectContext; // get the database creation script var script = objectContext.CreateDatabaseScript(); if (context.Database.Connection is SqlConnection) { // for SQL Server, we'll just alter the script // add existance checks to the table creation statements script = Regex.Replace(script, @"create table \[(\w+)\]\.\[(\w+)\]", "if not exists (select * from INFORMATION_SCHEMA.TABLES " + "where TABLE_SCHEMA='$1' and TABLE_NAME = '$2')\n$&"); // add existance checks to the table constraint creation statements script = Regex.Replace(script, @"alter table \[(\w+)\]\.\[(\w+)\] add constraint \[(\w+)\]", "if not exists (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS " + "where TABLE_SCHEMA='$1' and TABLE_NAME = '$2' " + "and CONSTRAINT_NAME = '$3')\n$&"); // run the modified script objectContext.ExecuteStoreCommand(script); } else if (context.Database.Connection is SqlCeConnection) { // SQL CE doesn't let you use inline existance checks, // so we have to parse each statement out and check separately. var statements = script.Split(new[] { ";\r\n" }, StringSplitOptions.RemoveEmptyEntries); foreach (var statement in statements) { var quoteSplitStrings = statement.Split('"'); if (statement.StartsWith("CREATE TABLE")) { // Create a table if it does not exist. var tableName = quoteSplitStrings[1]; const string sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_NAME='{0}'" var checkScript = string.Format(sql, tableName); if (objectContext.ExecuteStoreQuery(checkScript).First() == 0) objectContext.ExecuteStoreCommand(statement); } else if (statement.Contains("ADD CONSTRAINT")) { // Add a table constraint if it does not exist. var tableName = quoteSplitStrings[1]; var constraintName = quoteSplitStrings[3]; const string sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS " + "WHERE TABLE_NAME='{0}' AND CONSTRAINT_NAME='{1}'"; var checkScript = string.Format(sql, tableName, constraintName); if (objectContext.ExecuteStoreQuery(checkScript).First() == 0) objectContext.ExecuteStoreCommand(statement); } else { // Not sure what else it could be. Just run it. objectContext.ExecuteStoreCommand(statement); } } } else { throw new InvalidOperationException( "This initializer is only compatible with SQL Server or SQL Compact Edition" ); } } } } 

Anch’io stavo cercando una buona soluzione dal momento che Godaddy non consente la creazione di database e quindi non crea tabelle. Poiché la versione più recente di Entity Framework ha EDMData obsoleto, ho modificato il codice di Alex per vedere se esiste una tabella DropMeToRecreateDatabase oppure, se non esiste, elimina tutte le tabelle e ricrea nuove tabelle.

 using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.Objects; using System.Linq; namespace LadyTreble.DatabaseInitializer { public class DontDropExistingDbCreateTablesIfTableDropped : IDatabaseInitializer where T : DbContext { public bool TryInitializeDatabase(T context) { var objectContext = ((IObjectContextAdapter)context).ObjectContext; if (objectContext.ExecuteStoreQuery(GetTableCount).FirstOrDefault() == 0) { this.DeleteExistingTables(objectContext); this.CreateTables(objectContext); } return true; } public void InitializeDatabase(T context) { this.TryInitializeDatabase(context); } private void CreateTables(ObjectContext objectContext) { string dataBaseCreateScript = objectContext.CreateDatabaseScript(); objectContext.ExecuteStoreCommand(dataBaseCreateScript); } private void DeleteExistingTables(ObjectContext objectContext) { objectContext.ExecuteStoreCommand(DeleteAllTablesScript); } private const string DeleteAllTablesScript = @"declare @cmd varchar(4000) DECLARE cmds0 CURSOR FOR SELECT 'ALTER TABLE ' + TABLE_NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' DECLARE cmds1 CURSOR FOR SELECT 'ALTER TABLE ' + TABLE_NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS DECLARE cmds2 CURSOR FOR SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES DECLARE cmds3 CURSOR FOR SELECT 'DROP TABLE [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES open cmds0 while 1=1 begin fetch cmds0 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds0 deallocate cmds0 open cmds1 while 1=1 begin fetch cmds1 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds1 deallocate cmds1 open cmds2 while 1=1 begin fetch cmds2 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds2 deallocate cmds2 open cmds3 while 1=1 begin fetch cmds3 into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds3 deallocate cmds3 CREATE TABLE DropMeToRecreateDatabase(id int IDENTITY(1,1) NOT NULL)"; private const string GetTableCount = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME = 'DropMeToRecreateDatabase'"; } } 

Lo svilupperà il Power Pack della Generazione di database di Entity Designer . Non sono sicuro se funzioni ancora con Code First, ma ne vale la pena.