Devart Forums

Posted: **Thu 09 Jun 2016 07:28**

Please help me with generating SQL code for code first migration in VS2013 C#.
I have installed dcpostgresql68pro.exe and PostgreSQL 9.4.4
And wrote a program that extracts migration SQL code at run-time, so I could run it manually, instead of just applying migration automatically.
The problem is that the extracted code differs from that which is generated by this command(and it is incorrect)

Code: Select all

Update-Database -Script -TargetMigration:"InitialCreate"

This code is generated by my program

Code: Select all

CREATE TABLE public.rb_group ( 
  rb_group_id serial NOT NULL,
  rb_group_name varchar(50) NOT NULL,
  rb_group_description varchar(300) NOT NULL,
  PRIMARY KEY (rb_group_id)
);

CREATE UNIQUE INDEX unique_key_rb_group_names ON public.rb_group (rb_group_name);

CREATE TABLE public.reference_book ( 
  rb_id serial NOT NULL,
  rb_group_id int NULL,
  rb_name varchar(50) NOT NULL,
  rb_description varchar(300) NOT NULL,
  PRIMARY KEY (rb_id)
);

ALTER TABLE public.reference_book
  ADD CONSTRAINT "FK_reference_book_rb_group_rb_group_id" FOREIGN KEY (rb_group_id) REFERENCES public.rb_group ()
  ON DELETE NO ACTION;

CREATE INDEX "IX_reference_book_rb_group_id" ON public.reference_book (rb_group_id);

CREATE UNIQUE INDEX unique_key_reference_book ON public.reference_book (rb_name);

And this one - by Update-Database command

Code: Select all

CREATE TABLE public.rb_group ( 
  rb_group_id serial NOT NULL,
  rb_group_name varchar(50) NOT NULL,
  rb_group_description varchar(300) NOT NULL,
  PRIMARY KEY (rb_group_id)
);

CREATE UNIQUE INDEX unique_key_rb_group_names ON public.rb_group (rb_group_name);

CREATE TABLE public.reference_book ( 
  rb_id serial NOT NULL,
  rb_group_id int NULL,
  rb_name varchar(50) NOT NULL,
  rb_description varchar(300) NOT NULL,
  PRIMARY KEY (rb_id)
);

CREATE INDEX "IX_reference_book_rb_group_id" ON public.reference_book (rb_group_id);

CREATE UNIQUE INDEX unique_key_reference_book ON public.reference_book (rb_name);

ALTER TABLE public.reference_book
  ADD CONSTRAINT "FK_reference_book_rb_group_rb_group_id" FOREIGN KEY (rb_group_id) REFERENCES public.rb_group (rb_group_id)
  ON DELETE NO ACTION;

That is the command that adds FOREIGN KEY appear in different order and in REFERENCES clause the "rb_group_id" field is missing, which causes the syntax error.

Here is my program

Code: Select all

class SQLGenerator
{
    public static void Gen(Form1 f1)
    {
        var migration_InitialCreate = new InitialCreate();
        migration_InitialCreate.Up();
        System.Diagnostics.Debug.Write(RunMigration(migration_InitialCreate));
    }

    public static string RunMigration(DbMigration migration, bool run_sql = false)
    {
        string genSQL = "";

        var prop = migration.GetType().GetProperty("Operations", BindingFlags.NonPublic | BindingFlags.Instance);//, BindingFlags.NonPublic | BindingFlags.Instance
        if (prop != null)
        {
            IEnumerable<MigrationOperation> operations = prop.GetValue(migration) as IEnumerable<MigrationOperation>;

            var generator = new Devart.Data.PostgreSql.Entity.Migrations.PgSqlEntityMigrationSqlGenerator();
            var statements = generator.Generate(operations, "PostgreSQL, 9.4.4");

            using (SQLGen_DB_Context db = new SQLGen_DB_Context())
            {
                db.Database.Connection.Open();

                foreach (MigrationStatement item in statements)
                {
                    if (genSQL != "") genSQL += "\r\n";
                    genSQL += item.Sql + ";\r\n";
                    if (run_sql == true)
                    {
                        try
                        {
                            db.Database.ExecuteSqlCommand(item.Sql);
                        }
                        catch (Exception ex)
                        {
                            string ex_message = ex.Message;
                            System.Diagnostics.Debug.WriteLine("Error\r\n " + item.Sql + "\r\n" + ex_message);
                        }
                    }
                }
            }
        }

        return genSQL;
    }

    public class rb_group
    {
        [Key]
        public int rb_group_id { get; set; }
        [Required, StringLength(50)]
        [Index("unique_key_rb_group_names", IsUnique = true)]
        public string rb_group_name { get; set; }
        [Required, StringLength(300)]
        public string rb_group_description { get; set; }

        public virtual ICollection<reference_book> reference_books { get; set; }
    }

    public class reference_book
    {
        [Key]
        public int rb_id { get; set; }
        public Nullable<int> rb_group_id { get; set; }
        [Required, StringLength(50)]
        [Index("unique_key_reference_book", Order = 1, IsUnique = true)]
        public string rb_name { get; set; }
        [Required, StringLength(300)]
        public string rb_description { get; set; }

        public virtual rb_group rb_group { get; set; }
    }

    public class SQLGen_DB_Context : DbContext
    {
        public SQLGen_DB_Context()
            : base("DefaultConnection")
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // If you are letting EntityFrameowrk to create the database, 
            // it will by default create the __MigrationHisotry table in the dbo schema
            // Use HasDefaultSchema to specify alternative (i.e public) schema
            modelBuilder.HasDefaultSchema("public");
        }

        public DbSet<rb_group> rb_groups { get; set; }
        public DbSet<reference_book> reference_books { get; set; }
    }


}

201606090649292_InitialCreate.cs:

Code: Select all

namespace SQLGenerator.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class InitialCreate : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "public.rb_group",
                c => new
                    {
                        rb_group_id = c.Int(nullable: false, identity: true),
                        rb_group_name = c.String(nullable: false, maxLength: 50),
                        rb_group_description = c.String(nullable: false, maxLength: 300),
                    })
                .PrimaryKey(t => t.rb_group_id)
                .Index(t => t.rb_group_name, unique: true, name: "unique_key_rb_group_names");
            
            CreateTable(
                "public.reference_book",
                c => new
                    {
                        rb_id = c.Int(nullable: false, identity: true),
                        rb_group_id = c.Int(),
                        rb_name = c.String(nullable: false, maxLength: 50),
                        rb_description = c.String(nullable: false, maxLength: 300),
                    })
                .PrimaryKey(t => t.rb_id)
                .ForeignKey("public.rb_group", t => t.rb_group_id)
                .Index(t => t.rb_group_id)
                .Index(t => t.rb_name, unique: true, name: "unique_key_reference_book");
            
        }
        
        public override void Down()
        {
            DropForeignKey("public.reference_book", "rb_group_id", "public.rb_group");
            DropIndex("public.reference_book", "unique_key_reference_book");
            DropIndex("public.reference_book", new[] { "rb_group_id" });
            DropIndex("public.rb_group", "unique_key_rb_group_names");
            DropTable("public.reference_book");
            DropTable("public.rb_group");
        }
    }
}

Configuration.cs

Code: Select all

namespace SQLGenerator.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<SQLGenerator.SQLGen_DB_Context>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
            SetSqlGenerator(Devart.Data.PostgreSql.Entity.Migrations.PgSqlConnectionInfo.InvariantName, new Devart.Data.PostgreSql.Entity.Migrations.PgSqlEntityMigrationSqlGenerator());
        }

        protected override void Seed(SQLGenerator.SQLGen_DB_Context context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

Posted: **Fri 10 Jun 2016 17:47**

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

Posted: **Wed 22 Jun 2016 14:40**

You are using an undocumented functionality of Entity Framework. The list of the MigrationOperation objects, obtained via the DbMigration.Operations property, is not sorted in a proper way, and the objects are not fully configured. The generator of our EF provider (and other providers as well) does not guarantee to process such a list of operations.

We recommend to use the following approach.
There is a DbMigrator class in Entity Framework. It can be used for upgrading to the needed migration and for getting a script. DbMigrator sorts the operations list correctly and completes configuration (adds names of columns to the operations of adding FK). After this, it passes the updated list to the generator of a particular provider.

Creation of an object:

Code: Select all

var configuration = new Configuration();
var migrator = new DbMigrator(configuration);

Getting a script (the second argument of the ScriptUpdate method is the name of migration):

Code: Select all

var scriptor = new MigratorScriptingDecorator(migrator);
string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: migration.GetType().Name);

Upgrading to a migration:

Code: Select all

migrator.Update(migration.GetType().Name);

This works.

If you want to use your approach, you should implement the functionality of DbMigrator which is missing in DbMigration. For better understanding, refer to the code of DbMigrator (from EntityFramework.dll) at https://entityframework.codeplex.com/So ... igrator.cs. Pay attention to private methods ExecuteOperations and FillInForeignKeyOperations.

Devart Forums

Help using PgSqlEntityMigrationSqlGenerator

Help using PgSqlEntityMigrationSqlGenerator

Re: Help using PgSqlEntityMigrationSqlGenerator

Re: Help using PgSqlEntityMigrationSqlGenerator