Setar schema in query with Repository

Question

I have my Repository:

  public class RepositoryBase<T> where T : class
  {
    protected AppDbContext _context;
    protected IDbSet<T> _dbSet;

    public RepositoryBase()
    {
      _context = _context ?? new AppDbContext();
      _dbSet = _dbSet ?? _context.Set<T>();
    }
    public virtual IQueryable<T> GetAll()
    {
      return _dbSet;
    }
  }

In it I have my Gelall method.

By default, the Entity Framework let the Schema DBO, correct?

But I need to do this consultation in another Schema,which Seventh in Runtime

How could I set the Schema for this DbSet?

Answer 1

@Rod I would define this schema in the mapping of my entity. That could be passed as parameter to Context. Would do something like

public class MeuContext : DbContext
{
    string _schema = "";
    public MeuContext(string schema = "dbo")
    {
        _schema = schema;
    }
    public DbSet<Estado> Estado { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Estado>().ToTable("Estado", _schema);
        modelBuilder.Entity<Estado>().HasKey(a => a.EstadoId);
        modelBuilder.Entity<Estado>().Property(a => a.EstadoId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        base.OnModelCreating(modelBuilder);
    }
}

public class Estado
{
    public int EstadoId { get; set; }
    public string Descricao { get; set; }
    public string Tipo { get; set; }
}

I didn’t get to test it, but I think it should work.

Answer 2

To reset in Runtime, you’ll need a library to make this switch for you:

http://efmodeladapter.codeplex.com/

If you need me to pack the library in a Nuget package, just talk.

Or you can define an extension method for EntityConnection with the following:

public static EntityConnection Create(
    string schema, string connString, string model)
{
    XmlReader[] conceptualReader = new XmlReader[]
    {
        XmlReader.Create(
            Assembly
                .GetExecutingAssembly()
                .GetManifestResourceStream(model + ".csdl")
        )
    };
    XmlReader[] mappingReader = new XmlReader[]
    {
        XmlReader.Create(
            Assembly
                .GetExecutingAssembly()
                .GetManifestResourceStream(model + ".msl")
        )
    };

    var storageReader = XmlReader.Create(
        Assembly
            .GetExecutingAssembly()
            .GetManifestResourceStream(model + ".ssdl")
    );
    XNamespace storageNS = "http://schemas.microsoft.com/ado/2009/02/edm/ssdl";

    var storageXml = XElement.Load(storageReader);

    foreach (var entitySet in storageXml.Descendants(storageNS + "EntitySet"))
    {   
        var schemaAttribute = entitySet.Attributes("Schema").FirstOrDefault();
        if (schemaAttribute != null)
        {
            schemaAttribute.SetValue(schema);
        }
    }
    storageXml.CreateReader();

    StoreItemCollection storageCollection =
        new StoreItemCollection(
            new XmlReader[] { storageXml.CreateReader() }
        );
    EdmItemCollection conceptualCollection = new EdmItemCollection(conceptualReader);
    StorageMappingItemCollection mappingCollection =
        new StorageMappingItemCollection(
            conceptualCollection, storageCollection, mappingReader
        );

    var workspace = new MetadataWorkspace();
    workspace.RegisterItemCollection(conceptualCollection);
    workspace.RegisterItemCollection(storageCollection);
    workspace.RegisterItemCollection(mappingCollection);

    var connectionData = new EntityConnectionStringBuilder(connString);
    var connection = DbProviderFactories
        .GetFactory(connectionData.Provider)
        .CreateConnection();
    connection.ConnectionString = connectionData.ProviderConnectionString;

    return new EntityConnection(workspace, connection);
}

By instantiating the context, you pass it on to him EntityConnection created by this method.

For more questions, see this question in the OS.