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

Answer 1

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

Answer 2

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