Entity Framework with stored procedures only

Question

I have a question about the wisdom of using an entity framework only with stored procedures in our scenario.

We plan to have an N-tier architect with user interface, BusinessLayer (BLL), DataAccessLayer (DAL), and BusinessObjectDefinitions (BOD) interfaces. The BOD level is known by all other layers, and the results of query execution in the DAL must be converted to objects (defined in the BOD) before moving to the BLL.

We will use only stored procedures for all CRUD methods. Therefore, if we select a stored procedure, we will add the import function, create a complex type, and when we execute this function, we will convert the values ​​of the complex type to the BOD class and pass it to BLL. Thus, we do not have objects in the model, just complex types that are converted to business objects.

I’m not sure that all this makes sense, because, in my opinion, we are losing a lot of advantages, offers EF.

Or am I completely wrong?

Answer 1

I would not use EF if everything I just used was stored in procs.

Personally, I would look at something like PetaPoco, Massive, or even directly on Ado.Net.

EDIT

Here is an example of using PetaPoco SP and outputting custom types

http://weblogs.asp.net/jalpeshpvadgama/archive/2011/06/20/petapoco-with-stored-procedures.aspx

Answer 2

I disagree with both of the existing answers here. Petapoco is great, but I think that EF still offers a number of advantages.

Petapoco works great (maybe even better than EF) for executing simple stored procedures that read a single object or list of entities. However, as soon as you read the data and begin to modify it, I feel that EF is a clear winner.

To insert / update data using petapoco, you need to manually call the stored procedure insert / update using:

db.Execute("EXEC spName @param1 = 1, @param2 = 2") 

Manually building a stored procedure call and declaring all parameters gets old very quickly when the insert / update stored procedures insert rows with several pairs of columns. This gets even worse when invoking updated stored procedures that implement optimistic concurrency (i.e., pass initial values ​​as parameters).

In addition, you run the risk of making a typo in your call to the stored procedure, which most likely will not be detected before execution.

Now compare this to an entity framework: in EF, I would just bind my stored procedure to my entity in edmx. There is less risk of typos because the entity infrastructure tools automatically generate a mapping by analyzing my stored procedure.

The essence of the framework will also cope with optimistic concurrency without any problems. Finally, when it comes time to save changes, the only challenge is to call:

 entities.SaveChanges() 

Answer 3

I agree, if you rely on stored procedures for all CRUD methods, then there is no need to use EF.

Answer 4

I use EF to display stored procedure calls like our DAL. This saves time when writing your DAL by matching functions. We do not use LINQ to SQL in the same way that our DBA does not want direct access to the data table.