Entity framework 6 connecting multiple database implemention advice

Question

Entity framework 6 connecting multiple database implemention advice

entity-framework entity-framework-6

Question

I am a novice when it comes to entity framework.I usually use ado.net because is faster than any ORM and the repetitive code can be easily generated.

Now I have decided to give EF6 a go again and gain some experience in EF

Scenario.

Need to migrate data for many clients.(30 databases)

Each client will have their own staging database
Each database will have different tables
All databases will have/share the same "Views" schema.

Basically we decided that regardless of the clients tables ,they must all share the same Views. So when we read the data we dont care because the views columnNames will be the same for them all.

Ado.net Implementation

Is very simple.I my dal i have methods like "GetCustomers","GetAccounts" etc... and all I need to do is change the connectionString and I can read the views from any database.Does not get simpler than this.

EF implementation

Please correct me if I am wrong. In order for EF to work I would have to generate code for 30 databases (databaseFirst).

Is there a way I can use just a connection string to connect to the right database and based on that connection string read data from the views?

How would you do it using EF6 by just changing the connection string?

Can you give a noddy example how to do it?

Any suggestions

user9969

View more on Stack Overflow

tschmit007 · Accepted Answer · 12/4/2013 10:22:15 AM

here it is, just a bit more of 10 minutes... (EF 5 on .net 4.5)

using System;
using System.Linq;
using System.Data.Entity;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using System.Data.Objects.SqlClient;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;

namespace testef {               
    public class EntityZ {
        public Int32 Id { get; set; }
        public String P1 { get; set; }
    }        

    public class EntityZConfiguration : EntityTypeConfiguration<EntityZ> {
        public EntityZConfiguration()
            : base() {
            ToTable("v", "dbo"); // the view
            HasKey(x => x.Id); //required

            //the mapping of properties
            Property(x => x.Id).HasColumnName("id");
            Property(x => x.P1).HasColumnName("value");
        }
    }  

    public class TestEFContext : DbContext {            
        public DbSet<EntityZ> Entities { get; set; }

        public TestEFContext(String cs)
            : base(cs) {
            //Database.SetInitializer<TestEFContext>(new DropCreateDatabaseAlways<TestEFContext>());
            Database.SetInitializer<TestEFContext>(null);

        }

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

            modelBuilder.Configurations.Add(new EntityZConfiguration());
        }
    }

    class Program {
        static void Main(string[] args) {
            //creating the db 
            using (SqlConnection conn = new SqlConnection("Data Source=ALIASTVALK;Initial Catalog=master;Integrated Security=True; MultipleActiveResultSets=True")) {
                conn.Open();
                using (SqlCommand com = conn.CreateCommand()) {
                    com.CommandText = "declare @v int = 0 select @v = 1 from sys.databases where name = 'TestEF' if @v = 1 drop database TestEF";
                    com.ExecuteNonQuery();
                    com.CommandText = "create database TestEF";
                    com.ExecuteNonQuery();
                    com.CommandText = "use TestEF";
                    com.ExecuteNonQuery();
                    com.CommandText = "create table t (i int not null, t nvarchar(max))";
                    com.ExecuteNonQuery();
                    com.CommandText = "insert into  t (i, t) values (1, 'hello world')";
                    com.ExecuteNonQuery();
                    com.CommandText = "create view v as select i as id, t as value from t";
                    com.ExecuteNonQuery();
                }
            }

            String cs = @"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True";
            using (TestEFContext ctx = new TestEFContext(cs)) {
                foreach (EntityZ z in ctx.Entities) {
                    Console.WriteLine("{0}: {1}", z.Id, z.P1);
                }

            }                
        }
    }
}

Entity framework 6 connecting multiple database implemention advice

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related