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.

Middling

Give them a context with a user that doesn't have write access to the database. – Fretted

Thanks. Im using an SQLite database. Just found out that it can be opened in readonly mode via a connection string option. – Middling

Don't give them a DbContext, give them an IQueryable or several. – Rampart

Answer 1

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>();
    }
}

Smacking

it was obvious you're an 'inside man' :) - this is a lot more interesting than a 'readonly' connection – Talkative

Note that using AsNoTracking() will make it impossible to use lazy loading. – Slub

@TomPažourek I don't know if that's true... I think EF still creates lazy-loading proxies, but identity resolution might get a little weird. – Smacking

@bricelam: Ok, thanks for the response, I'll have to do some more experiments to see how it works... – Slub

Is there way to prevent the execution of stored procedures from the DbContext? – Patronymic

@Patronymic No, use database permissions for that. – Smacking

Don't forget to override public override Task<int> SaveChangesAsync() as well. – Jardena

Beware of the ExecuteStoreQuery method. – Electromotor

I tried this with a read-only DB user and still got an error telling me the CREATE TABLE permission was denied. Are we sure entity framework can work with a read-only user? – Comfy

@AdamR.Grey You need to create the database schema using a user with permissions, but after that a read-only user will work. – Smacking

Don't rely on this, because (context as IObjectContextAdapter).ObjectContext.SaveChanges() will still work. The best choice is to use the DbContext(string nameOrConnectionString); contstructor with a read/write connectionstring for database creation stuff and a readonly connection string afterwards. – Benn

Throw an Obsolete Attribute on SaveChanges to remind the developer not to use it. [Obsolete("DB Context is read-only",true)] #pragma warning disable CS0809 – Cottbus

Also add Configuration.AutoDetectChangesEnabled=false; in your context constructor. – Cottbus

@Smacking In EntityFrameworkCore it should be public IQueryable<Customer> Customers => Set<Customer>().AsNoTracking(); – Sushi

@DiPix. Very nice change from a method to a property. Thanks. (And to bricelam for this answer as well) – Draconic

Answer 2

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();

Winnow

Does this method allow for use of a db_datareader only sql login? With a standard DBContext EF throws CREATE TABLE permission denied even when my query code doesn't include any SaveChanges(). – Clueless

And make it inherit from IDisposable – Bashemath

Instead of using Set<>, I'd suggest Query<>. public IQueryable<TEntity> Get<TEntity>() where TEntity : class { return _dbContext.Query<TEntity>().AsNoTracking(); } – Novah

@Bashemath - not sure I'd do that. Since this call did not create the DbContext, it should not dispose it. This could lead to some hard to track down bugs later. – Novah

@AllanNielsen Query<> is marked deprecated. According to it Set<> should be used. – Donica

EXCELLENT "prefer composition over inheritance" answer ! I added a small bit to the answer, if you (Ehsan) do not feel it adds to the answer, please reject or edit-out my small change. – Draconic

Any yes, I am doing exactly as you mention, putting this into place for my "Query" side of the CQSR pattern. Another link: learn.microsoft.com/en-us/archive/msdn-magazine/2015/june/… – Draconic

Answer 3

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)

Manrope

You could place the SaveChanges() methods within #pragma warning disable CS0809 and #pragma warning restore CS0809 to disable the warning and still use override. – Hafler

Answer 4

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

and override SaveChanges to throw Exception

Boccherini

Answer 5

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.

Chippy

Answer 6

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();

    // [...]
}

Terrier

Answer 7

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; }
   
 }

Secondary

Answer 8

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(); }
    }

..... }

Immorality

OK. Down marked, but why? – Immorality