Should I use entity framework for CRUD and let the database handle the complexity that comes with high end queries?

Question

I am new to ef and liking it since it reduces the overhead of writing common queries by replacing it with simple add, remove functions. Agreed.

Today I got into the argument with my colleague who has been using it for a while and approached him for advice on when to use Stored Procedures and when to use EF and for what?

He replied;

Look, the simple thing is that you can use both but what's the point of using an ORM if you are doing it all in database i.e. stored procedures. So, how would you figure out what to do where and why? A simple formula that I have learned that use ORM for all CRUD operation and queries that require 3-4 joins but anything beyond that you better use stored procedures.

I thought, analyzed and replied;

Well, this isn't the case, I have seen blogs and examples where people are doing massive things with EF and didn't need to write any procedure.

But he's stubborn and calling it performance overhead which is yet beyond my understanding since I am relatively new as compared to him.

So, my question is that whether you only need to handle CRUD in ef or should do a lot more in EF as a replacement of stored procedures.

Answer 1

As always, it depends. Stored procedures, views, indexes and triggers (etc) are great tools for a DBA, making sure that the (relational) database itself guarantees consistence, secures proper permissions and performs as well as possible.

However, those tools are a sure path towards a database-centric design, where the whole system revolves around the database. This means that all code will have to adapt to the physical database model. The data types used by the database will dictate in-memory types etc. The reason for this is that it will be very hard to initiate a refactoring of the database layer from your business logic code if a recactoring includes rewriting (and testing/debugging) stored procedures, triggers and custom SQL code.

The main benefit of EF (and similar tools) is not (IMHO) that they make simple CRUD components easy to develop, but that subsequent refactoring of those components becomes much easier. (Given that you do code-first)

Yes, there is a performance price to pay once you start to do complex joins. That price is much higher if you have a naive approach to EF.

However

If you run into real (as in end user actually suffers) performance issues because of sql queries containing may complex joins, then stored procedures are a band-aid at best.

You can optimize storage for data integrity or for query performance, but not both. If you need both, look into CQRS or similar patterns.

TL;DR

• EF makes writing and refactoring data access code faster, which is good.
• The price for this is non-optimal queries (worse if you are not experienced with EF)
• Stored procedures and other DBA techniques can improve DB performance, which is good.
• The price for this is that business code will be harder to write and refactor.
• If query performance is an issue (given competent EF use), reevaluate overall design rather than trying to optimize SQL.

Answer 2

I tend to take your approach on the matter. ORMs are great for basic CRUD and relatively simple queries, but often times I prefer to go to the database directly, usually for performance reasons.

I can write a complex view (or sproc) in SQL Server that can be optimized much more easily than the same can in Linq. On the other hand, I think it's correct also to be wary of putting actual logic inside your database.

ORMs are one tool for interacting with a database, and for day to day things, they can simplify enormously as opposed to writing SQL statements. But, as is the case with just about every other tool, it's not a silver bullet to completely replace DB interaction.

I tend to have two rules I follow here:

1. Attempt to only use direct DB objects for performance optimization reasons, NOT for calculation of business logic (sometimes the two cross paths, but often you can generate base views/sprocs as a source for a .NET method that will then perform the actual logical set comparisons), and...
2. Use your DBContext class/repository class to call the views/sprocs you may have created for these optimization purposes. That is, if you have a DBContext class to get your standard EF DBSets, you should also expose and consume your calls to the "supporting objects" there (so, sproc dbo.GenerateReportSet, for example, could be called like _context.GenerateReportSet(dateStart, dateEnd)

Answer 3

But he's stubborn and calling it performance overhead which is yet beyond my understanding

It's hard to have a detailed discussion about something when you don't understand (and thus are unable to acknowledge) part of the problema outset.

Very simply put: automation can only be so clever. When dealing with complex and intricate queries, EF will generate a working query, but it won't be as efficient as you want it to be.
EF isn't badly developed. The generated inefficient queries are just an inevitable consequence of what EF is (and isn't) built to do. EF is a great tool that avoid trivial and repetitive code; but those benefits are lost when you're dealing with highly customized (generally complex) queries.

Think of it this way: my sedan is built for road use. On the road, it will be much easier to drive than a large 4x4. Can I take my car offroad? Within reason, yes. But my car wasn't built for offroading, and thus my car's benefits are lost when not on the road, whereas the 4x4 starts to shine and will be easier to drive offroad.
Like my car is built for road use, EF is built for simple CRUD tasks. It can be used in other ways but no one every designed it with that in mind.

"Look, the simple thing is that you can use both but what's the point of using an ORM if you are doing it all in database i.e. stored procedures."

That argument makes little sense. What's the point of using a toothbrush if your arm moves the toothbrush anyway. Just use your arm to clean your teeth!

Well, this isn't the case, I have seen blogs and examples where people are doing massive things with EF and didn't need to write any procedure.

Just because it can be done does not mean that it is being done well. I've seen people eat the food I made but that doesn't mean I'm a good cook. It just means I'm not fully incapable of cooking.

EF can do many things, but its efficiency starts falling apart as the complexity of the query increases. In cases where the subsequent performance hit is unacceptable, EF is not the tool to use.

So, my question is that whether you only need to handle CRUD in ef or should do a lot more in EF as a replacement of stored procedures.

Like many things, it depends on what you need.

For simple CRUD operations, EF is a good tool to use. It removes the need for a lot of boilerplated handling logic.

If you only have simple data queries, or don't care about optimizing performance, then you can use EF for those since you're already using EF for CRUD anyway.

However, if you have intricate data queries and care about the performance optimization, then you won't want to use EF. Our lead architect (who used to be a DBA and very much cares about DB performance) currently advocates for using EF and Dapper simultaneously, specifically because they each specialize in part of the workload. He initially suggested only using Dapper, but he has conceded the ease of CRUD with EF and accepts its usage in CRUD-heavy applications.

But again this is subjective. You might prefer to stick to one tool and suck up the drawbacks (e.g. use EF and deal with slow complex queries; or use Dapper and deal with having to write boilerplate CRUD code). Or you may be in favor of using different tools so that you can squeeze performance at all times.

Answer 4

Just to add to what @Guran has answered: I don’t have experience with EF specifically, but I have seen that it’s easy to forget that you are dealing with joins and expensive operations when you are using an ORM. So you start treating model instances as regular variables and include them in complex operations which in turn will probably be turned into even more complex sql queries. I have also seen client side code with loops and aggregations that could have been done more efficiently in the database server.

So if you hand write sql/stores procedures you will be more aware about the performance implications.

Answer 5

In most cases where a sproc is needed for some requirement. Place a sql script file in the codebase to execute in the case of future business rule refactoring needs. I would assume most new and experienced developers have some experience with working in a dbms. Anyone needing to know the DB sproc requirements can review and update the the scripts as needed. Business rules and DB requirements exist in the source code files. I to believe there is no one way method in most complex solutions. (JMO).

Answer 6

You are thinking from the database perspective. All your logic is placed around or inside the database. This is called database-driven design.

Let me explain the idea.

Databases are just details, that should be responsible for storing and returning data. That is the main purpose of any data store. When you are applying DBMS specific features like Stored procedures, Triggers, etc. You are just moving you business logic inside the database.

I have seen this approach in a lot of legacy systems, where almos all business rules are defined inside stored procuderes. I don't suggest to use stored procedures at all, only for specific cases.

Consider a situation when you decided to move to another DBMS and it doesn't support Stored Procuders, or they are defined in completely other way, you have to spend a lot of time for this migration. Furtermore, it can be really tricky sometimes to debug stored procedures. Or you can get unpredictable behavior of your system when something went wrong inside a stored procedure.

Entity framework is a great tool, it allows you to define your models without defining a database schema. This is called the Model-First approach. That is exactly the idea of decoupling your business rules from details like databases. You defined a Domain Model that fits for your business needs without thinking about infrastructure details.

Answer 7

Your colleague is essentially correct. But perhaps set in their ways.

At the end of the day EF is a fancy way to generate SQL commands. You trust that its syntax and generation methods allow you to express complex queries and generate efficient SQL.

But if you can just write that efficient SQL yourself. You can skip EF.

This is particularly true for spocs, as once you start using them you have to abandon the EF query syntax and just run the sproc. You might as well use SqlClient.

However!!

You can take it a step further and not use sprocs either. Just hard code your SQL. The arguement that sprocs run faster isnt really true for parameterised SQL as it will be compiled on the first run.

Use this is to test your colleague. Do they really want to streamline their code, or are they just used to making sprocs and sticking to what they know.

Personally I do not use EF or sprocs and find sticking to SqlClient repositories makes for highly streamlined, organised and efficient code.