How to make Entity Framework Data Context Readonly

Question

I need to expose an Entity Framework Data Context to 3rd party plugins. The purpose is to allow these plugins to fetch data only and not to let them issue inserts, updates or deletes or any other database modification commands. Hence how can I make a data context or entity readonly.

Answer 1

As opposed to the accepted answer, I believe it would be better to favor composition over inheritance. Then there would be no need for keeping methods such as SaveChanges to throw an exception. Moreover, why do you need to have such methods in the first place? You should design a class in a way that its consumer doesn't get fooled when it looks at its list of methods. The public interface should be in align with the actual intent and goal of the class while in the accepted answer having SaveChanges doesn't imply that Context is read-only.

In places where I need to have a read-only context such as in the Read side of CQRS pattern, I use the following implementation. It doesn't provide anything other than Querying capabilities to its consumer.

public class ReadOnlyDataContext
{
    private readonly DbContext _dbContext;

    public ReadOnlyDataContext(DbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public IQueryable<TEntity> Set<TEntity>() where TEntity : class
    {
        return _dbContext.Set<TEntity>().AsNoTracking();
    }
}

By using ReadOnlyDataContext, you can have access to only querying capabilities of DbContext. Let's say you have an entity named Order, then you would use ReadOnlyDataContext instance in a way like below.

readOnlyDataContext.Set<Order>()
                   .Where(q=> q.Status==OrderStatus.Delivered)
                   .ToArray();

An alternate option, if you wanted to hand pick (and limit) which entities are exposed via this new context. You would remove the generic based method above (the complete block with TEntity in it) and use something similar to the below.

    public IQueryable<MyFirstThing> MyFirstHandPickThings => 
              this.dbContext.Set<MyFirstThing>().AsNoTracking();

    public IQueryable<MySecondThing> MySecondHandPickThings => 
              this.dbContext.Set<MySecondThing>().AsNoTracking();

Answer 2

You can create readonly user in SQL/MySQL etc.

CREATE USER 'readonly-user'@'localhost' IDENTIFIED BY 'highly-secure-password';

GRANT SELECT ON special-db.* TO 'readonly-user'@'localhost';

FLUSH PRIVILEGES;

Then you can update the connection string accordingly

 "ConnectionStrings": {
   "readonlyDb": "Server=localhost; Port=3306; Database=special-db; Uid=readonly-user; Pwd=highly-secure-password;"
 }

If you need to go the code route you can always create protected constructors with private setters.

e.g.

public class Koala
 {
     protected Koala() { }

     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int Id { get; private set; }
     public string Name { get; private set; }
   
 }

Answer 3

I have a solution that I believe is as nice at it gets. It is based on other answers here, but uses interfaces to nicely restrict the interface (duh) of both the Context and the ReadOnlyContext.

Note: I'm using EF Core style here, but the pattern can also be used with old EF.

For the Context we follow the normal interface pattern, where the interface defines those aspects of the Context class, that we want to be able to use from our application. Everywhere in our application we will inject IContext instead of Context.

public interface IContext : IDisposable
{
    DbSet<Customer> Customers{ get; }
    int SaveChanges();
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);
}

public class Context :DbContext, IContext
{
    public DbSet<Customer> Customers { get; set; }
    
    public Context(DbContextOptions options)
        : base(options)
    {
    }
}

Now we implement our ReadOnlyContext by extending Context, and limiting its functionality so it becomes readonly, but we also create a matching IReadOnlyContext interface that limits this further by exposing IQueryable instead of DbSet and by not exposing SaveChanges. When using it in our application we inject IReadOnlyContext instead of ReadOnlyContext.

public interface IReadOnlyContext : IDisposable
{
    IQueryable<Customer> Customers { get; }
}


public class ReadOnlyContext : Context, IReadOnlyContext
{
    public new IQueryable<Customer> Customers => base.Customers.AsQueryable();

    public ReadOnlyContext(DbContextOptions options)
        : base(options)
    {
    }


    [Obsolete("This context is read-only", true)]
    public new int SaveChanges()
    {
        throw new InvalidOperationException("This context is read-only.");
    }

    [Obsolete("This context is read-only", true)]
    public new int SaveChanges(bool acceptAll)
    {
        throw new InvalidOperationException("This context is read-only.");
    }

    [Obsolete("This context is read-only", true)]
    public new Task<int> SaveChangesAsync(CancellationToken token = default)
    {
        throw new InvalidOperationException("This context is read-only.");
    }

    [Obsolete("This context is read-only", true)]
    public new Task<int> SaveChangesAsync(bool acceptAll, CancellationToken token = default)
    {
        throw new InvalidOperationException("This context is read-only.");
    }
}

The setup of these contexts could look something like this:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<IReadOnlyContext, ReadOnlyContext>(
            contextOptions => contextOptions
                .UseSqlServer(
                    _configuration["ConnectionStrings:ReadOnlyConnection"] ??
                    _configuration["ConnectionStrings:DefaultConnection"],
                    sqlServerOptions => sqlServerOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
                )
                .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
        );
        services.AddDbContext<IContext, Context>(
            contextOptions => contextOptions
                .UseSqlServer(
                    _configuration["ConnectionStrings:DefaultConnection"],
                    sqlServerOptions =>
                        sqlServerOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
                )
        );
    }

As you can see it works nicely with the dependency injection approach, and it allows for using a separate connection string, which you need if you want to connect to a read-only replicate of an Azure Database.

Answer 4

In my scenario with EF Core/.NET 5.0, I wanted to have compile-time safety for SaveChanges. This only worked with "new" instead of "override".

I'm using read/write and read-only contexts side-by-side, where one inherits from the other since there are lots of tables attached. This is what I use, with "ContextData" being my original R/W DbContext:

public class ContextDataReadOnly : ContextData
{
    public ContextDataReadOnly() : base()
    {
        ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
    }

    [Obsolete("This context is read-only", true)]
    public new int SaveChanges()
    {
        throw new InvalidOperationException("This context is read-only.");
    }

    [Obsolete("This context is read-only", true)]
    public new int SaveChanges(bool acceptAll)
    {
        throw new InvalidOperationException("This context is read-only.");
    }

    [Obsolete("This context is read-only", true)]
    public new Task<int> SaveChangesAsync(CancellationToken token = default)
    {
        throw new InvalidOperationException("This context is read-only.");
    }

    [Obsolete("This context is read-only", true)]
    public new Task<int> SaveChangesAsync(bool acceptAll, CancellationToken token = default)
    {
        throw new InvalidOperationException("This context is read-only.");
    }
}

Note that:

• I had to use "new" instead of "override" when overwriting inherited SaveChanges*() in order to have warnings/errors. With "override", there where no compile time errors/warnings at all.

• With "override" you get CS0809 [1], but not with "new"

• Using "new" will only work for the class itself, but not in context of the parent:

  Base b = new Derived();
  Derived d = new Derived();
  
  b.SaveChanges();     // Calls Base.SaveChanges, will compile and run without exception
  d.SaveChanges();     // Calls Derived.SaveChanges, will not compile
  

• Proper choice of (optional) arguments is required for the variants of SaveChanges and SaveChangesAsync. (This is for .NET 5.0, I have not checked whether it varies for other versions of EF Core/EF)

Conclusion

1. "override" would provide full inheritance, but does not work in my environment
2. "new" provides desired function, but will return unexpected results for certain polymorphism scenarios
3. Not using inheritance at all will be pain if you have many tables

==> There is no silver bullet, and the choice depends on taste and circumstances ...

[1] https://learn.microsoft.com/en-us/dotnet/csharp/misc/cs0809?f1url=%3FappId%3Droslyn%26k%3Dk(CS0809)

Answer 5

Since DbQuery<T> isn't available anymore in Entity Framework Core, you need to modify @bricelam's answer a little bit and directly use IQueryable<T> instead:

public class ReadOnlyContext : DbContext
{
    public IQueryable<Customer> Customers => this.Set<Customer>().AsNoTracking();

    // [...]
}

Answer 6

Situation: I needed to reference DB1 for creating records in DB2 and wanted to protect DB1 in the process. DB1 and DB2 are schema copies of each other.

I updated the autogenerated Entity Context file. And put in a read-only option when instantiating the Entity Context with an override of SaveChanges() to abort writes when using the ReadOnly option.

Downsides:

1. You have to create a separate EF connection string in config settings
2. You'll have to be careful when auto-updating the model. Keep a copy of your code changes and remember to apply it after model updates.
3. No notice is provided that the save was not performed. I chose not to provide a notice because my usage is very limited and we perform alot of saves.

The upsides:

1. You don't have to implement a CQRS type solution.
2. By using the same Entity Model, you don't have to create a second one and maintain it as well.
3. No changes to the DB or its user accounts.

Just make sure when you are naming your context instantiation to name it using ReadOnly or some such.

public partial class db1_Entities : DbContext
{
    public bool IsReadOnly { get; private set; }

    public db1_Entities()
        : base(ConfigurationManager.ConnectionStrings["db1_Entities"].ConnectionString)
    {
    }

    public db1_Entities(bool readOnlyDB)
        : base(ConfigurationManager.ConnectionStrings["db1_ReadOnly_Entities "].ConnectionString)
    {
        //  Don't use this instantiation unless you want a read-only reference.
        if (useReferenceDB == false)
        {
            this.Dispose();
            return;
        }
        else
        { IsReadOnly = true; }
    }

    public override int SaveChanges()
    {
        if (IsReadOnly == true)
        { return -1; }
        else
        { return base.SaveChanges(); }
    }

    public override Task<int> SaveChangesAsync()
    {
        if (isReadOnly == true)
        { return null; }
        else
        { return base.SaveChangesAsync(); }
    }

..... }

Answer 7

public sealed class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options, IHttpContextAccessor httpContextAccessor)
        : base(options)
    {
        ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
    }
}

and override SaveChanges to throw Exception

Answer 8

In addition to connecting with a read-only user, there are a few other things you can do to your DbContext.

public class MyReadOnlyContext : DbContext
{
    // Use ReadOnlyConnectionString from App/Web.config
    public MyContext()
        : base("Name=ReadOnlyConnectionString")
    {
    }

    // Don't expose Add(), Remove(), etc.
    public DbQuery<Customer> Customers
    {
        get
        {
            // Don't track changes to query results
            return Set<Customer>().AsNoTracking();
        }
    }

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

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Need this since there is no DbSet<Customer> property
        modelBuilder.Entity<Customer>();
    }
}