CODEWITHSUNDEEP
entity-framework
actf
actf

Reputation: 549

Disabling SQL Generation in Entity Framework?

I'm wondering if anyone has found a way to completely disable the EF from using generated SQL or to completely disable the EF from executing ad-hoc SQL statements. To phrase this another way, I'd like to force the Entity Framework to only interact with the database using Stored Procedures that are defined as mappings in my .edmx file.

Why would I want to do something like this?

I'm using the EF as my ORM in an application that interacts with the database using only Stored Procedures (due to security requirements). It's very easy to occasionally forget to define a Stored Procedure mapping in my .edmx file. When this happens the EF will attempt to generate ad-hoc SQL at runtime - which results in a runtime security error, as the EF will try to execute this generated SQL against a database that prohibits ad-hoc SQL execution.

Since most of my development work and unit testing is done with a user account that does have ad-hoc SQL execution permission enabled, these runtime security errors are often missed.

Some of the options I've considered here:

  1. Switch to another ORM - maybe there's another ORM that supports stored procedures better, however so far I haven't really seen a better alternative.

  2. Disable ad-hoc SQL execution in SQL on my user account, that I use for development. This is problematic though because my unit tests, in some cases, need to use ad hoc SQL to inject test data, etc into the database. It's also problematic because when I'm writing code it's very convenient to be able to execute ad-hoc sql statements against the database (to manipulate data, inject test data, etc), and disabling ad-hoc SQL execution would mean constantly switching between users or loosing this ability.

  3. Somehow disabling ad-hoc SQL generatiion in the EF. This seems to be by far the best option, as I could continue developing, and testing with a user that has SQL execution permissions - and if I forget a Stored Procedure mapping, my tests would fail, or I would get a runtime error.

Upvotes: 0

Views: 125

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127603

What I would do is run the unit test itself as a different user than your development user that does not have ad-hoc privileges but run the setup and teardown steps of the unit test as your development user.

It gives you the best of both worlds, both unit testing in the same environment as deployment and admin powers to set up said test.

Upvotes: 1

Related Questions