Connect to database with a different user/connection string

[xr280xr]

I am currently using this, I think, by the book and it's awesome. But I came across a problem I'm unsure how to solve today. I have a query that I need to execute using a different database login/user because it requires additional permissions. I can create another connection string in my web.config, but I'm not sure how to specify that for this query, I want to use this new connection string. Here is my usage:

In my logic layer:
private static IDbContextScopeFactory _dbContextFactory = new DbContextScopeFactory();

public static Guid GetFacilityID(string altID)
{
            ...
            using (_dbContextFactory.CreateReadOnly())
            {
                entity = entities.GetFacilityID(altID)
            }
}

That calls into my data layer which would look something like this:

private AmbientDbContextLocator _dbcLocator = new AmbientDbContextLocator();

    protected CRMEntities DBContext
    {
        get
        {
            var dbContext = _dbcLocator.Get<CRMEntities>();

            if (dbContext == null)
                throw new InvalidOperationException("No ambient DbContext....");

            return dbContext;
        }
    }

    public virtual Guid GetFaciltyID(string altID)
    {
        return DBContext.Set<Facility>().Where(f => f.altID = altID).Select(f => f.ID).FirstOrDefault();
    }

Currently my connection string is set in the default way:

public partial class CRMEntities : DbContext
{
    public CRMEntities()
        : base("name=CRMEntities")
    {}
}

Is it possible for this query to use a different connection string?

[dusan-tkac]

Can't you just create another DbContext using new connection string with same database with different credentials?
Set up your IDbContextFactory implementation to create new DbContext instance as well when requested and you get it's reference in the same way you're getting the reference to your current DbContext.

[xr280xr]

I'm using code first, but I could probably update the t4 file to generate another DbContext. That's an awful lot of duplicated code when all that needs to change is the connection to the database though. P.S. I don't currently have an IDbContextFactory but will look into it.

[dusan-tkac]

You don't have to generate it. Just hand-write the new DbContext in a separate project. Only include entities you need for that query.
Or you can use Entity Data Model Wizard's "Code First from Database" in the new project and again only include tables or views you need.

[xr280xr]

Sorry - above I meant I'm not using code first. I'm using database first. I have customizations to my DbContext that I would have to duplicate and keep in sync if I created another one. The DbContext is a model of my database and my database is the same database regardless of which user is using it. I don't see creating one DbContext per user as being a scalable or conceptually accurate solution.

For now, I modified my DbContextScope source. I created a CustomAmbientDbContextLocator whose Get method accepts a connection string parameter and changed the DbContextCollection's InitializedDbContexts to a Dictionary<KeyValuePair<Type, string>, DbContext>. So rather than my project having two different DbContexts, the DbContextCollection caches one instance of the DbContext per connection string it was created with. I think my implementation tarnishes the elegance of the project a little bit - it could be fancier - but it's doing what I need.

[shoaibshakeel381]

There is a much easier solution for your problem. You can just implement IDbContextFactory interface and provide DbContext instances yourself. This factory will be provided in IDbContextScopeFactory constructor. It is usually null and DbContextCollection creates DbContext object itself. But if a factory is available then that factory will be called for new instances.

You can easily use whichever connection string you want in that factory.