When to use stored procedures and SQL queries in Entity Framework Code First [closed]

Question

What are the appropriate uses of stored procedures and SQL queries in EF code-first, particularly in ASP.NET web development? I was heavily dependent on them before I stared to use Entity Framework. However, with Entity Framework, hopefully we do not need to worry about writing SQL and creating stored procedures to create, insert, update, and read data, etc.

I know that using SQL might be good in some cases in EF, such as when clearing a whole table:

dbcontext.Database.ExecuteSqlCommand("delete from MyTable");

I do not want to underestimate the usability of stored procedures and SQL queries, and would like to learn the scenarios when they might be useful in EF code-first applications. Can you share good practices from your own projects?

Answer 1

In my opinion, I think there are plenty of times where you may need to do something significantly complex, where it warrants keeping that complexity in a stored proc.

Some examples might include;

• BULK INSERT operations from a user uploaded file to process
• Recursive CTEs
• Use of Temp Tables/Table Variables to perform advanced processing
• Making use of existing database functions, procs, linked servers etc
• Instances where the queries are sufficiently advanced that the query plan generated with an ORM is less than optimal, and you know you can do better with plain SQL. Also being able to use query hints in SQL is quite beneficial (albeit a rarity too)

I've been involved in projects in the past where we let the ORM handle the vast majority of instances, but we had a handful of places where we deferred to hand crafted SQL where it was deemed more appropriate.

I wouldn't suggest that any ORM is suitable for all tasks.

To a degree, I'd also suggest that the ability of your DEV team might also help you decide what to use where. Your ORM might be fine for simple CRUD and semi-advanced search type queries. However, for more complicated processing, if your team are going to spend twice as long trying to do something in an ORM than simply doing it in SQL, then you have to ask if that is a good use of time and money.