none
Migrations: create view instead of table. Also, how to handle both MSSQL and Oracle

    Question

  • Hi EF team,

    I just tried out how Code First migrations would handle creating a view instead of a table to map to an entity and it appears to handle it really well, so well done! I uploaded my sample to code.msdn here: http://code.msdn.microsoft.com/Recursive-or-hierarchical-bf43a96e.

     

    I do need some guidance on how to handle different providers though. If I target MS SQL Server I want to create a MS SQL Server specific view. If I target Oracle I want an Oracle specific view.

    I could possible detect the provider in the migration as follows:

        public partial class ManagerEmployeesMigration : DbMigration
        {
            public override void Up()
            {
                //CreateTable(
                //    "ManagerEmployees",
                //    c => new
                //        {
                //            ManagerEmployeeID = c.Int(nullable: false),
                //            EmployeeID = c.Int(nullable: false),
                //        })
                //    .PrimaryKey(t => new { t.ManagerEmployeeID, t.EmployeeID });
     
                if (ConfigurationManager.ConnectionStrings[typeof(NorthwindContext).Name].ProviderName == "Devart.Data.Oracle")
                {
                    Sql(@"CREATE OR REPLACE VIEW ""ManagerEmployees""
                                                        AS
      SELECT CAST(CONNECT_BY_ROOT ""EmployeeID"" AS NUMBER(10,0)) AS ""ManagerEmployeeID"",
        ""EmployeeID""                                            AS ""EmployeeID""
      FROM ""Employees""
        CONNECT BY ""ReportsToEmployeeID"" = prior ""EmployeeID""");
                }
                else
                {
                    Sql(@"CREATE VIEW [dbo].[ManagerEmployees]
    AS
        WITH    cte ( ManagerEmployeeID, EmployeeID )
                  AS ( SELECT   EmployeeID ,
                                EmployeeID
                       FROM     dbo.Employees
                       UNION ALL
                       SELECT   e.EmployeeID ,
                                cte.EmployeeID
                       FROM     cte
                                INNER JOIN dbo.Employees AS e ON e.ReportsToEmployeeID = cte.ManagerEmployeeID
                     )
        SELECT  ISNULL(EmployeeID, 0) AS ManagerEmployeeID ,
                ISNULL(ManagerEmployeeID, 0) AS EmployeeID
        FROM    cte");
                }
            }
     
            public override void Down()
            {
                //DropTable("ManagerEmployees");
                if (ConfigurationManager.ConnectionStrings[typeof(NorthwindContext).Name].ProviderName == "Devart.Data.Oracle")
                {
                    Sql("DROP VIEW \"ManagerEmployees\"");
                }
                else
                {
                    Sql("DROP VIEW [dbo].[ManagerEmployees]");
                }
            }
        }

    But it would be better if I could get the provider from the DbMigration class instead of making assumptions about what is configured in app.config or web.config. Can this be added to the api before RTM please?

    many thanks

     

    Remco

    Tuesday, December 06, 2011 1:00 PM

All replies

  • Hi EF Team

    I understand the EF Team may not be able to comment on what will and will not be in the api for RTM, but I would appreciate some acknowledgement that they've read this post. The EF Team did say Code First Migrations would have a provider model allowing for different database providers. Supporting different providers (but not at the same time) in a single application may not be an uncommon scenario. Surely the DbMigration class can be told whether it is invoked using an Oracle or SQL Server provider?

    many thanks

    Remco

    Tuesday, December 20, 2011 1:51 PM
  • Hmmm, no change to the DbMigration class in EF 4.3 beta 1 and no more pre-release versions before RTM. I do not have high hopes now that the DbMigration class in EF 4.3 RTM will be able to tell me the name of the provider (System.Data.SqlClient or Devart.Data.Oracle or other) used when Up or Down is invoked.

    One other bit of feedback on something that has changed in EF 4.3 beta 1: the DbSetExtensions class: should the extension methods not be on IDbSet instead of on DbSet? Now I have to call AddOrUpdate on context.Set<Employee>() instead of on context.Employees, which I have defined as IDbSet<Employee>, not DbSet<Employee>.

    kind regards

    Remco

    Friday, January 13, 2012 8:59 AM