locked
Switch Azure DB to EF6 code first RRS feed

  • Question

  • Hello.

    When translating our project to EF6 code first , we are faced with the
    following problem :

    It is impossible to change the database schema .

    Extend the methods to switch context to another scheme of the box we found
    How to advise developers on the forums , change the database schema can only be
    at the time of initialization and construction of the metadata for database
    objects .

    This construction is performed with a method call:
    protected override void OnModelCreating (DbModelBuilder
    modelBuilder)

    However, this method is called only once - when the first call to the context.
    All further context calls do not result in a call to this method .

    How to advise developers on the forums , it is due to the creation of metadata
    cache , disable the cache is not possible .

    That piece of code , the switching circuitry to initialize :

    public partial class effiedbEntities : DbContext

    {

            private string schemaName {
    get; set; }

            public effiedbEntities(string schemaName) : base("name=effiedbEntitiesDev")

            {

               
    // DbModelBuilder builder = new
    DbModelBuilder();

               
    // Указываем необходимы классы модели

               
    // builder.Entity<ChannelSales>();

               
    // Запускаем вручную процесс создания модели

               
    // DbModel mod = builder.Build(new
    DbProviderInfo("System.Data.SqlClient", "2012"));

               
    // DbCompiledModel res_mod = mod.Compile();

         
          
    this.schemaName = schemaName;

               
    Database.SetInitializer(new CreateDatabaseIfNotExists<effiedbEntities>());

               

         // Database.Initialize(true);

               
    // OnModelCreating(new DbModelBuilder());

            }

            public virtual DbSet<ChannelSales> ChannelSales { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

           // Define schemaName for entities
    ===================================================

          
    modelBuilder.Entity<
    ChannelSales>().ToTable("ChannelSales", schemaName);

           // Define schemaName for entities
    ===================================================

    modelBuilder.Entity<ChannelSales>()

                   
    .HasMany(e => e.QuestItems)

                   
    .WithOptional(e => e.ChannelSales)

                   
    .HasForeignKey(e => e.ChannelSaleID);

          
    modelBuilder.Entity<
    ChannelSales>()

                   
    .HasMany(e => e.TT)

                   
    .WithRequired(e => e.ChannelSales)

                   
    .HasForeignKey(e => e.ChannelSaleID);

    }

    }

    I need help

    How to switch to EF6 code first

    Thanks


    Wednesday, August 31, 2016 1:04 PM

Answers

  • >We think that this is because of cache

    Likely yes.

    I think you'll need to drop down and handle the DbCompiledModel caching manually for this, creating and caching one per schema.  So instead of overriding OnModelCreating, you do something like:

    using System;
    using System.Collections.Concurrent;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication19
    {
        public class MyEntity
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    
        class MyDb : DbContext
        {
            static ConcurrentDictionary<string, DbCompiledModel> modelCache = new ConcurrentDictionary<string, DbCompiledModel>();
    
            static DbCompiledModel GetModelForSchema(string connectionString, string schema)
            {
                schema = schema.ToUpper();
    
                if (modelCache.ContainsKey(schema))
                {
                    return modelCache[schema];
                }
    
                var mb = new DbModelBuilder();
    
                mb.HasDefaultSchema(schema);
                mb.Entity<MyEntity>().ToTable("MY_ENTITY");
    
                var model = mb.Build(new System.Data.SqlClient.SqlConnection(connectionString));
                var compiledModel = model.Compile();
                modelCache.AddOrUpdate(schema, compiledModel, (key, oldValue) => oldValue);
    
                return modelCache[schema];
          
    
            }
            public MyDb( string connectionString, string schema) : base(connectionString,GetModelForSchema(connectionString,schema))
            {
    
            }
            public DbSet<MyEntity> MyEntities { get; set; }
    
            
        }
        class Program
        {
            static void Main(string[] args)
            {
                
                using (var db = new MyDb("server=.;Database=MyDatabase;Integrated Security=true", "schema1"))
                {
                    if (db.Database.Exists())
                          db.Database.Delete();
    
                    db.Database.Initialize(true);
                }
    
                using (var db = new MyDb("server=.;Database=MyDatabase;Integrated Security=true", "schema2"))
                {
                    db.Database.Initialize(true);
                }
            }
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 1, 2016 6:36 PM

All replies

  • I don't understand your question.  Can you describe again what you are trying to do and the problem you are having?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, August 31, 2016 1:36 PM
  • The problem is the following
    We use several database schemas Azure SQL, for example, u1 and u2
    At the time of receiving the first request for a service that uses the EF code first, a method that defines and switches us to a necessary schema runs
    When you connect for the first time - everything works
    If we get a second request, addressed to the other schemes, such as u2, then switch to a scheme u2 does not happen
    Using Debugging tools, we see that there is an attempt to switch to a schema u1
    We think that this is because of cache, which remains after the first connection
    Now we use the 6.1.3 version of the EF
    As a way of solving the problem may be option to disable the cache, as in the previous versions EF or something else
    Thursday, September 1, 2016 4:17 PM
  • >We think that this is because of cache

    Likely yes.

    I think you'll need to drop down and handle the DbCompiledModel caching manually for this, creating and caching one per schema.  So instead of overriding OnModelCreating, you do something like:

    using System;
    using System.Collections.Concurrent;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication19
    {
        public class MyEntity
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    
        class MyDb : DbContext
        {
            static ConcurrentDictionary<string, DbCompiledModel> modelCache = new ConcurrentDictionary<string, DbCompiledModel>();
    
            static DbCompiledModel GetModelForSchema(string connectionString, string schema)
            {
                schema = schema.ToUpper();
    
                if (modelCache.ContainsKey(schema))
                {
                    return modelCache[schema];
                }
    
                var mb = new DbModelBuilder();
    
                mb.HasDefaultSchema(schema);
                mb.Entity<MyEntity>().ToTable("MY_ENTITY");
    
                var model = mb.Build(new System.Data.SqlClient.SqlConnection(connectionString));
                var compiledModel = model.Compile();
                modelCache.AddOrUpdate(schema, compiledModel, (key, oldValue) => oldValue);
    
                return modelCache[schema];
          
    
            }
            public MyDb( string connectionString, string schema) : base(connectionString,GetModelForSchema(connectionString,schema))
            {
    
            }
            public DbSet<MyEntity> MyEntities { get; set; }
    
            
        }
        class Program
        {
            static void Main(string[] args)
            {
                
                using (var db = new MyDb("server=.;Database=MyDatabase;Integrated Security=true", "schema1"))
                {
                    if (db.Database.Exists())
                          db.Database.Delete();
    
                    db.Database.Initialize(true);
                }
    
                using (var db = new MyDb("server=.;Database=MyDatabase;Integrated Security=true", "schema2"))
                {
                    db.Database.Initialize(true);
                }
            }
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 1, 2016 6:36 PM
  • Many thanks, David

    It works!

    Friday, September 2, 2016 12:26 PM