Entity Framework with no direct table access

Question

There are some similar questions I've found here, but none of them fully answer the question I'm asking. Similar questions: here and here

In my company, I develop C# .NET applications and our Server Admins in IT don't allow direct table access so we're required to use stored procedures to insert, update, and delete from a table. In the past I've been using DataSets and TableAdapters to access the tables of a database, but I'm starting to realize that everything is starting to use Entity Framework.

Herein lies my issue. There is another developer here that uses the Entity Framework, but he doesn't actually use entities. He uses Entity Models that have no tables or views, only stored procedures. Doesn't this defeat the purpose of the Entity Framework, or am I crazy?

Also, I realize that you can map the insert, update, and delete functionality to stored procedures and this article mentions some interesting ways of implementing that, but creating views for every table would be a major pain. Are there any more practical or easily implemented alternatives?

Answer 1

It depends on ones background and preferences.

Some people came up using stored procedures and are very comfortable with them. They love T-SQL and can do a lot with it. They tend to write the SPs first and then use a minimal wrapper to use the SP. Note some people prefer their SPs a lot of the logic such as validation, business rules, data transformations, etc.

Other people are more comfortable with object oriented programming and prefer to write classes and then serialize those classes to and from a database. In these cases the logic tends to be in the classes.

Entity framework supports both types of development. It is mostly agnostic of development style and not overly opinionated.

But in your situation you and your team need to devise what the standard is. You need to make a decision about how many EF features you are going to use and you style of database development.

I personally lean toward using everything the EF can provide, so I let it generate all of the entities. Then I use Linq to Objects to move data to and from the entities, and let EF deal with the database I/O. I find doing this allows me to create nice tight code that is reusable and testable, and most importantly, understandable.

Answer 2

I think you are on the right track with the EF stored procedures support. I would disagree with your pov about it would be pain to create stored proc and views.

Views: One View per table

Stored procedures: You will need at most three stored procedures

Create
Update
Delete

And most of it is boiler plate code.

If the database is too big try using some sort of code generator tool. Or at its very simple have a template for your stored procedures in Visual Studio's SSDT project.

Once you have above four things in place considering you use some sort of Generic repository pattern, it would be quire easy to write your Business Services code.

public class MyBusinessService : IMyBusinessService
{
    public void InsertData(DataDTO dto)
    {
         ...
         _repo.Add(data);
         _repo.SaveChanges();
    }
}

If you think about it , the amount of time you spend writing custom stored procedures for each operations, is large compare to one off three stored procedures and one view. In addition all your services code can be unit testable.

The thing you will need to consider when using EF (and is true for any ORM) is that its generating SQL for you from your C# code, so needs to pay attention about how you write your EF to Linq queries.