I think you've definitely got the gist of EF, that's a good question.
I would recommend that you go down the Linq to Entities option. Don't be afraid to use .Include(x => x.a), and remember that because this joins on another table you'll want to make sure your joins (foreign keys) have indexes on them. To me the maintenance benefit of the entity framework is being able to modify your table structure (add/move/rename columns) and then be able to re-compile your c# code and pickup what's broken - man, this would have saved me DAYS of fixing up stored procs a few years ago after making DB changes. If your logic is inside a stored proc then you don't get this benefit.
Only if your c# is starting to get too complicated to maintain would you consider using a stored proc to do the work, which might be better for maintenance purposes. Or if you're querying a database that can't be effectively used by the entity framework (compound foreign keys or something else).
What you mentioned about Entity Framework happens only if you're model is up to date (in data-driven design). If not, you won't get a compile-time error, and they would definitely defer to become run-time errors.
Consider this scenario. You create a database, with one table called Users
which has 3 columns: Username
, Password
, IsActive
.
You create a project, and add an Entity Framework .edmx
file to it, updating it with the schema of your database. Great till here.
Now what happens if someone change the data type of the IsActive
column from Boolean
to int
, and you don't know about it?
You simply build your project (which builds successfully) and run it, and then you get some errors.
In model-first and code-first development models, the scenario is even worst, as there is no direct and auto-generated mapping between your domain model and database schema.
Thus by the experience I've got till now, I can't say that Entity Framework definitely helps you get mapping problems at compile time. The only case it can help, is to let it generate the model itself from database, and get updated.
Best Answer
Honestly, why force a square peg into a round hole? Entity Framework supports calling stored procedures for a reason; because Entity Framework cannot address every use case that the database itself can support.
I would just use the right tool for the job (which in some of these cases appears to be stored procedures) and not worry about trying to create a faux "pure" architecture by forcing everything into Entity Framework.