SQL Code First DB - Create tables, not drop/create

Question

I'm trying to get a MVC 3 deployment off the ground under the Azure trial, but seem to have hit a wall with getting the EF4.1 database to create itself.

When running, it will connect successfully, but give the error:

Invalid object name 'dbo.TableName'.

I believe this is because the DB exists, but there are no tables in it. I've been able to reproduce it locally - The DB recreation works fine if I delete my SQL Express DB, but not if I then delete all tables and leave the DB.

I've read up on the methods that can be added to Global.asax:

protected void Application_Start()
{
    // Use any of:
    System.Data.Entity.Database.SetInitializer<MyDatabaseContext>(new DropCreateDatabaseIfModelChanges<MyDatabaseContext>());
    System.Data.Entity.Database.SetInitializer<MyDatabaseContext>(new CreateDatabaseIfNotExists<MyDatabaseContext>());
    System.Data.Entity.Database.SetInitializer<MyDatabaseContext>(new DropCreateDatabaseAlways<MyDatabaseContext>());
}

... which don't seem to work. Some give errors about not being about to get the model hash in EdmMetadata table.

How can I get EF4.1/Code First to create the DB structure in an already existing DB? (And in Azure...). Or do I have to script the DB from SQL Management studio, and run it against the destination DB?

possible duplicate of Recreate entity framework tables, not databases? — Ladislav Mrnka
– Ladislav Mrnka, Commented Sep 13, 2011 at 8:21

Khachatur · Accepted Answer · 2013-05-11 03:45:47Z

2

check David.Cline(Development) blog for "Drop & Create Tables upon Model Change":

http://www.davidcline.info/2012/05/technologies-c-v4.html

and this link may be helpful:

http://blogs.microsoft.co.il/blogs/gilf/archive/2011/05/30/creating-a-code-first-database-initializer-strategy.aspx

Thanks, Khachatur

answered May 11, 2013 at 3:45

Khachatur

1,0091 gold badge14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan Manzhos · Accepted Answer · 2011-11-27 10:07:58Z

You need to create a different database initializer, which will just clean the database instead of recreating.

Here it is

using System.Collections.ObjectModel;
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 Infrastructure
{  
public partial class RecreateSchemaIfModelChanges<T> : IDatabaseInitializer<T> where T : DbContext
{
    private EdmMetadata _edmMetaData; 

    private bool CompatibleWithModel(string modelHash, DbContext context, ObjectContext objectContext)
    {
        if (objectContext.ExecuteStoreQuery<int>("Select COUNT(*) \r\n              FROM INFORMATION_SCHEMA.TABLES T \r\n              Where T.TABLE_NAME = 'EdmMetaData'", new object[0]).FirstOrDefault<int>() == 1)
        {
            this._edmMetaData = context.Set<EdmMetadata>().FirstOrDefault<EdmMetadata>();
            if (this._edmMetaData != null)
            {
                return (modelHash == this._edmMetaData.ModelHash);
            }
        }
        return false;
    }

    private static string ComputeSha256Hash(string input)
    {
        byte[] buffer = new SHA256Managed().ComputeHash(Encoding.ASCII.GetBytes(input));
        StringBuilder builder = new StringBuilder(buffer.Length * 2);
        foreach (byte num in buffer)
        {
            builder.Append(num.ToString("X2", CultureInfo.InvariantCulture));
        }
        return builder.ToString();
    }

    private void CreateTables(ObjectContext objectContext)
    {
        string commandText = objectContext.CreateDatabaseScript();
        objectContext.ExecuteStoreCommand(commandText, new object[0]);
    }

    private string GetCsdlXmlString(ObjectContext context)
    {
        if (context != null)
        {
            ReadOnlyCollection<EntityContainer> items = context.MetadataWorkspace.GetItems<EntityContainer>(DataSpace.SSpace);
            if (items != null)
            {
                EntityModelSchemaGenerator generator = new EntityModelSchemaGenerator(items.FirstOrDefault<EntityContainer>());
                StringBuilder output = new StringBuilder();
                XmlWriter writer = XmlWriter.Create(output);
                generator.GenerateMetadata();
                generator.WriteModelSchema(writer);
                writer.Flush();
                return output.ToString();
            }
        }
        return string.Empty;
    }

    private string GetModelHash(ObjectContext context)
    {
        return ComputeSha256Hash(this.GetCsdlXmlString(context));
    }

    public void InitializeDatabase(T context)
    {
        ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
        string modelHash = this.GetModelHash(objectContext);
        if (!this.CompatibleWithModel(modelHash, context, objectContext))
        {
            this.DeleteExistingTables(objectContext);
            this.CreateTables(objectContext);
            this.SaveModelHashToDatabase(context, modelHash, objectContext);
            this.Seed(context);
        }
    }

    private void SaveModelHashToDatabase(T context, string modelHash, ObjectContext objectContext)
    {
        if (this._edmMetaData != null)
        {
            objectContext.Detach(this._edmMetaData);
        }
        this._edmMetaData = new EdmMetadata();
        context.Set<EdmMetadata>().Add(this._edmMetaData);
        this._edmMetaData.ModelHash = modelHash;
        context.SaveChanges();
    }

    private void DeleteExistingTables(ObjectContext objectContext)
    {
        var dropConstraintsScript =
            @"declare @cmd varchar(4000)
declare cmds cursor for 
           select  'ALTER TABLE ' + so.TABLE_NAME + ' DROP CONSTRAINT ' +         
so.constraint_name  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so order by  
so.CONSTRAINT_TYPE
open cmds
while 1=1
   begin
      fetch cmds into @cmd   
          if @@fetch_status != 0 break
                           print @cmd
                           exec(@cmd)
   end
close cmds
            deallocate cmds";
        string dropTablesScript =
            @"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";
        objectContext.ExecuteStoreCommand(dropConstraintsScript);
        objectContext.ExecuteStoreCommand(dropTablesScript);
    }

}
}

Dudi · Accepted Answer · 2015-06-27 19:54:10Z

Was built for EF6 but still good DbInitializer for that issue:

using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data.Entity.Migrations;

namespace MyProject.Models
{
public class MrSaleDbInitializer :
    //System.Data.Entity.DropCreateDatabaseIfModelChanges<MrSaleDbContext>
    System.Data.Entity.DropCreateDatabaseAlways<MrSaleDbContext>
{

    /*
     * Seed: Don't delete the current db, don't delete any tables, but clear all rows data in current 
     * db-tables, then seed the db with a new initial data.
     * note: Won't clear any Migration like tables or any AspNet tables such as: __MigrationHistory, AspNetUsers, AspNetRoles.
     */
    protected override void Seed(MrSaleDbContext context)        
    {
        this.ClearDb(context);
        this.SeedAfterClearingDb(context);
        base.Seed(context);         
    }


    private void ClearDb(MrSaleDbContext context)
    {
        //Optional: disable all foreign keys (db-schema will be loosed).
        //context.Database.ExecuteSqlCommand("EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? NOCHECK CONSTRAINT all'");

        List<string> tableNames = context.Database.SqlQuery<string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE '%Migration%' AND TABLE_NAME NOT LIKE 'AspNet%'").ToList();

        for (int i = 0; tableNames.Count > 0; i++)
        {
            try
            {
                //To delete all tables and not just clean them from data, replace "DELETE FROM {0}" in "DROP TABLE {0}":
                context.Database.ExecuteSqlCommand(string.Format("DELETE FROM {0}", tableNames.ElementAt(i % tableNames.Count)));

                tableNames.RemoveAt(i % tableNames.Count);
                i = -1; //flag: a table was removed. in the next iteration i++ will be the 0 index.
            }
            catch (System.Data.SqlClient.SqlException e)   // ignore errors as these are expected due to linked foreign key data    
            {
                //throw new Exception("Unable to clear any relevant table in data-base (due to foriegn key constraint ?). See inner-exception for more details.", e);
                if ((i % tableNames.Count) == (tableNames.Count - 1))
                {
                    //end of tables-list without any success to delete any table, then exit with exception:
                    throw new System.Data.DataException("Unable to clear all relevant tables in database (foriegn key constraint ?). See inner-exception for more details.", e);
                }

            }

        }

        context.SaveChanges();
    }


    /*
     * SeedAfterClearingDb: seed the data-base with initial date after database was created. 
     */
    public void SeedAfterClearingDb(MrSaleDbContext context)
    {
        //seed the data-base with initial date after database was created. 

        //then...

        //update all posts and save changes:

        context.SaveChanges();

    }
}

}

Collectives™ on Stack Overflow

SQL Code First DB - Create tables, not drop/create

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related