How to achieve read/write separation with Entity Framework

Question

I have a database setup using 'master/slave replication'. I have one master and (at least) one slave, possibly ℕ slaves. For simplicity from here on I'll talk about one master, one slave because determining which slave to use includes some business-logic not relevant to the actual problem at hand.

Here's a schematic of the setup (with ℕ slaves):

In the application (currently using Dapper) I have the following, simplified, code:

abstract class BaseRepo
{
    private readonly string _readconn;
    private readonly string _writeconn;

    public BaseRepo(string readConnection, string writeConnection)
    {
        _readconn = readConnection;     //Actually IEnumerable<string> for ℕ slaves
        _writeconn = writeConnection;
    }

    private SqlConnection GetOpenConnection(string cnstring)
    {
        var c = new SqlConnection(cnstring);
        c.Open();
        return c;
    }

    public SqlConnection GetOpenReadConnection()
    {
        return this.GetOpenConnection(_readconn);
        // Actually we use some business-logic to determine *which* of the slaves to use
    }

    public SqlConnection GetOpenWriteConnection()
    {
        return this.GetOpenConnection(_writeconn);
    }
}

class CustomerRepo : BaseRepo
{
    // ...ctor left out for brevity...

    // "Read" functions use the "read" connection
    public IEnumerable<Customer> ListCustomers()
    {
        using (var c = this.GetOpenReadConnection())
        {
            return c.Query<Customer>("select * from customers order by name");
        }
    }

    // "Write" functions use the "write" connection
    public void UpdateCustomer(Customer cust)
    {
        using (var c = this.GetOpenWriteConnection())
        {
            c.Execute("update customers set name = @name where id = @id", cust);
        }
    }
}

My question is; suppose I want to use Entity Framework ("code first", should that be relevant) instead of Dapper; how would I best go about achieving the same concept; inserts/updates/deletes are executed against the "master" database and selects are executed against a slave (or any of the slaves). Does EF support this scenario at all? What would I need to do to make this work?

Additional info: I already use 'read-only' and 'write-only' users at the SQL Server level as a 'last line of defence' to prevent any mistakes in the DAL. What I'm looking for is a method of limiting my DAL to avoid having to catch SQL Server exceptions because of 'not allowed' actions and having to go to the (incorrect) SQL server in the first place before finding out the desired action is not allowed. I could use the same approach as I do now; instantiate/use the correct DbContext in the method itself (listcustomers/updatecustomer in the above example). I get that. But that would mean I'd have to create a 'wrapper' function for each "CRUD" action on each "entity" which was kind of why I was moving from dapper to EF in the first place; simply expose a DBSet and have EF take care of the changetracking/SQL queries etc. and now, hopefully, also figure out which connectionstring to use for each action.

The simplest way I guess is to give different connection strings to each type of situation. So a solution that is not on the level of Entity Framework but on the level of SQL itself. PS. Please continue using dapper, godspeed — misha130, Mar 03 '16 at 13:34
@misha130 Could you clarify? What would you define as "type of situation"? And what would you define as "on the lever of SQL itself"? — RobIII, Mar 03 '16 at 13:36
You do indeed need to change the connection string at runtime. Entity Framework does provide this facility. This question will show you how! http://stackoverflow.com/questions/22267949/entity-framework-change-connection-string-at-runtime — Gusdor, Mar 03 '16 at 13:36
Types of situation read or write and by on SQL level I mean you create users in your servers that only handle reading and users that only handle writing. Gusdor's post is exactly what I mean — misha130, Mar 03 '16 at 13:40
@Gusdor; that would mean I would create a `ReadContext` and a `WriteContext`(both sharing a baseclass I suppose). But what stops me from reading customers from a writecontext, or vice versa, adding a new customer to a writecontext (other than sql complaining because of read/write permissions). I would like the DAL to simply not allow writes on read-only stuff and vice versa (in case of misconfiguration of permissions on the SQL databases for example). E.g. take care of it in code before it even goes off to SQL, and making it impossible to even add a customer on a wrong server/dbcontext... — RobIII, Mar 03 '16 at 13:41
@Gusdor; Oh, d'oh. I *could* ofcourse use the same approach as I do now; instantiate/use the correct dbcontext in the method itself (listcustomers/updatecustomer in the above example). I get it. But that would mean I'd have to create a 'wrapper' function for each "CRUD" action on each "entity" which was kind of why I was moving from dapper to EF in the first place; simply expose a DBSet and have EF take care of the changetracking/SQL queries etc. — RobIII, Mar 03 '16 at 13:45
I hope it works out for you! For safety, consider making the read connections read only at the SQL level. You will get a big ol' exception if you make a mistake anywhere in your DAL. Easy to unit test. — Gusdor, Mar 03 '16 at 13:46
@Gusdor; yes, indeed, currently I use "read only users" and "write only users" on the SQL server side to prevent accidents in the first place but I'd like that to be a last line of defence (but the strongest indeed). However, I would like my (public interface of my) DAL to prevent me even trying in the first place. — RobIII, Mar 03 '16 at 13:48
[I don't see why this question is on hold because of "too broad"](http://meta.stackoverflow.com/questions/318194/i-dont-see-why-this-question-is-on-hold-because-of-too-broad) — RobIII, Mar 03 '16 at 14:30
You can create contexts with an override of `SaveChanges` that simply doesn't call `base.SaveChanges` when some condition is false. But I don't know how secure you can make this. Maybe based on some naming convention in the database names? — Gert Arnold, Mar 03 '16 at 20:17
I've been working with EF for a couple of years now, and I haven't seen a straightforward way to turn off writes or reads from the model to the database. The suggestion by [Gert][1] is probably your best bet. Override `SaveChanges`. [1]: http://stackoverflow.com/users/861716/gert-arnold — gpersell, Apr 15 '16 at 19:17
To use a custom connection, check this answer: http://stackoverflow.com/a/519378/261050 — Maarten, Apr 28 '16 at 10:00

score 5 · Answer 1 · answered Apr 28 '16 at 09:54

As proposed by others, create a read/write context by default and then create a readonly one inheriting from it. Also be sure to implement in a partial class a constructor accepting another configuration if you wish too.

public partial class CustomerEntities : DbContext
{
    protected CustomerEntities(string nameOrConnectionString):base(nameOrConnectionString)
    {         
    }
}

public class ReadonlyCustomerEntities : CustomerEntities
{
    public ReadonlyCustomerEntities ()
        : base("name=ReadonlyCustomerEntities")
    {          
    }

    public override int SaveChanges()
    {
        // Throw if they try to call this
        throw new InvalidOperationException("This context is read-only.");
    }
}

How to achieve read/write separation with Entity Framework

1 Answers1