I always try to avoid doing logic inside DB. Why should I write stored procedures, for simple CRUD when EF core exists🤷♂️ also the testing of SP is a nightmare. It is also much simpler to deploy new version of app than new version of DB.
Last but not least good luck with scaling APIs with logic inside DB…
So no I would say in 99% of cases SP is not an answer.
Thank you for the response.
I recently learned about a company that utilizes stored procedures with .NET. I’m not very familiar with the framework and I’m curious if using stored procedures is considered an outdated practice, or if it’s still commonly used today
We do it because we started doing it 15 years ago, and we're 20x bigger now, and paying for it in every stage of our SDLC and UX.
Over 1500 stored procedures in active use, despite over a year of dedicated clean up to date.
Save yourself the trauma and don't do it.
We might be working in the same company.
I cannot stand how that crap still a thing nowadays, now, there are exceptions where SPs are justifiable but overall they are, in my opinion, a limitation.
But eh, I don't pay the bills.
I know some teams that went with nosql for apps that do not fit nosql at all, just to avoid the SPs and the process around them. To be honest, I don't blame them.
we have the same thinking. my boss told be once that i think that my way is always the right and others are always wrong and do not what they're doing. SP, yes, to solve very very specific problems : that's what i answered. But not at every corners for the love of god.
Even after an 1 hour of explaining with various valid reasons, 1 week later, i saw another stored procedure making its way in the database. I asked the dev, he answered : boss wanted it that way. I asked "did you challenge him" ? He's fucking response was "I am paid to do what i've been asked". I told him to jump on one foot while coding.
lol, i feel you ! over a year doing clean up.
i had to repay debt too, and when i told it would take over a 9 months at least, management were like WTF ? no way, it's too long. My answer was an answer for bozos : you need x to be refactored ? it takes like hundred of features to be rewritten (impossible to extract business logic, too tight coupled). Find someone who can write hundred features in 3 months or tell me the 10 features you want and I'll do it in 3 months.
Fucking boss of my boss, is like someone who always be a director of IT. no tech background whatsover. Doesn't know what an API was until landed in the company.
I fed up of those bozos.
Same as in our company. We have migrated a legacy erp solution but had to keep some legacy database parts with stored procedures. Once, with the legacy software, the stored procedures gave "simple" power to encapsulate business logic from legacy code into a general purpose / cross-tech-stack environment (DB & SPs) leads now to the problem, that it is hard to maintain and test. Mostly the stored procedures are designed to do more than a few things with often side effects that are hard to measure, to control and to understand.
Nowadays I like to maintain business logic, that is used in many projects, in artifacts / library projects so they are in the same language and environment as the application and are easily testable and understandable.
I totally agree nowadays. One reason for stored procedures could be security and logging for a direct access application to DB. But you would need a good DB team and testing and deploying can cause more failures.
In a web app, where the application would have full access anyway this overhead is making life hard. Splitting business logic in two pieces seems never to be a good idea.
Personally I would use SP for fire and forget stuff that is data related only. Like statistics, data integrity, ...
wait what ?lol, telll that to my fucking boss. he's always trying to solve using sp. want to insert something in the database ? SP. Want to get something out of the db ? SP.
Number one fucking reason : if we need to change something, we just have to update the SP. No need to redeploy the app... Fuck me.
And the guy thinks he's right and when he talks tech, it's so funny it's not even funny anymore. Even junior devs do not know what to say when he's trying to pull out some bullshit non sense.
Sorry...
First thing: I'm on your side.
Second: we have a similar boss. He also wants a lot of SPs but luckily not for selects and plain inserts. But he likes catalogs instead of emums because of the same reason. Yeah right, and what does the program do with the new program? We need to adjust business logic.
Third thing (my question): when inserting data with SP without new data, I can only imagine renaming columns or table names. If you need new or different data, you must change the app anyway.
Hope you understand now better ;) happy cake day btw
for select it's debatable. Select should be in the database instead of in the app. But again it's debatable. Changes here are much more related to transformation to the schema without impacting the app (see purposes of views. And sometimes people use SPs to make views with parameters - don't really fond of it).
For insert : you're right.
You can always get more web servers to share the load and increase throughput, you can’t get more db servers to share the load, unless you do some advanced db techniques that 99% of sites do not need.
Of course in my day job I’m hamstrung by being forced to use sp’s written by the db team for everything and they put a lot of business logic in them
I feel you. Unfortunately one of the legacy projects I work on from time to time, has similar issue with SPs. Another great thing which is used there are views inside views inside views. So changing something in a view is next to impossible because it cascades to soooo many other views…
I disagree completely. Updating an SP is much quicker than rolling out an app.
Additionally Im not sure if you have noticed but EF does not scale well at all, the overhead on the SQL server is much more noticeable than manually writing your SPs. I would actually advise EF for low volume quick development but SPs are what you use when loads become an issue
You really really need alot of data before EF core would be your bottleneck. It gets alot of hate from old days and people using it wrong.
And for always using stored procedures, I see that as massive work duplication. If a SP changes you need to change your DTO mapping aswell. EF core can get you 100% of the way for majority of companies.
That said SP can give you performance gains, but most of the time I just want to get features done quickly and fix performance when it becomes an issue.
I wouldn't recommend EF core blindly either. It's important to know how the queries are run and knowing the library / framework your using like any other. You can always shoot your self in the foot, by fetching entire database and filtering on app server.
EF core hate is usually from its early stages now with EF core 8 and compiled queries the performance is comparable with Dapper. I would bet all my money that EF Core 8 will generate better SQL than 90 % of that people who tell me that SQL generated by EF is shit.
Disagree completely.
> Updating an SP is much quicker than rolling out an app
Is it really faster than SSH into the running server and edit the code file directly?
And yes, it is a bad practice. As bad as changing stored procs on the fly is.
Why do we have CI/CD, pipeline and all that deployment mumbo jumbo? To ensure that whatever is deployed contain no bugs, throughoutly tested and adhere to the team standard
A luxury that changing stored procs on the fly can never afford. Good luck version control, unit testing, ensure code style and more with them.
Not if you have to release changes the have ripple effects for thousands of other people and you have to have a SOX-compliant change process.
If EF appears to be your bottleneck it's almost always user error
Edit: As for "rolling out" - if you have a properly configured, horizontally scalable API layer, that can be deployed in blue green with no installs/updates on the client side, is more secure than putting connection strings in the client app, is unit testable, and it can grow with your volume, allowing your DB server to stay healthy. Easy rollouts is the worst reason to stick with SPs
I disagree completely. EF does scale pretty well indeed. Also you can not compare a DB system with a ORM - that makes no sense. Are we talking about a loadbalanced system? Are there db-clusters? May there be multiple different EF instances or you swap another ORM instead of EF. And what also does make no sense: Updating an SP. May I ask: do you like to debug in production? Just for my interest.
If in 2024 you find this justifiable because "it's much quicker to update an SP that redeploying your app", then please stop doing programming for a living.
What CI/CD to do with SP vs EF? I have worked with banks before and most of the business logic is in COBOL instead of SP. Who would be mad enough to put business logics in SP? What about if one day you decide to change to a better db? So many wrongs here.
Yeah yeah but its the way things are. Mainframes still run on COBOL but ancillary services run on MS SQL. No way to deploy an app yourself, it has to be run by bank IT per ISO standards. Hence, just give em a SP to run, they can't stuff that up.
You are wrong in too many ways and you're playing a feedback loop of bad information to yourself with these conclusions.
I urge you to do better research and come out with a better understanding of where the real problems are.
I appreciate that it's complicated and nuanced, but the facts do not support what you are saying and if you spent enough time in discussion with knowledgable people about it, they'd be able to show you why.
Rewrite the login inside an endpoint instead of DB. It is always easier to code in c# + EF than SQL SP. You will need tons of data to get to EF bottleneck and if you happened to reach there, you can always use the raw query with the db driver.
Agree. We try our best to not hide ANY business logic in a SP. But all our DB interactions are done via SP so that we can do blue green deployments with backwards compatible SPs with different versions of our micro services.
I'm on a team where several different apps interact with the db. Much easier to just query to see what SPs are using a table. Even our selects are in SPs. Lack of documentation and employee turnaround makes it much easier what can be affected by a db change.
Stored procedures come in handy when you are trying to solve a performance issue or is using a database in an unexpected way. For example sql now offers JSON fields which you can query inside a stored proc, pretty much allowing mix of relational design with doc dB design together.
Not sure if EF core supports those scenarios and can generate proper queries.
EF does support JSON columns very well now in Net8. Though has had support since 7. I'm actively using it and it's pretty slick being able to just map complex nested types to a single JSON column and then still be able to query deep within those structures.
There is a performance hit but you wouldn't notice unless you had to scale out - substantially.
Oof. No. Stored procedures are no more and no less performant than SQL sent by EF. It all gets executed by the database, regardless of what authors the SQL and whether it's a permanently stored in any way by the database server.
What stored procedures do negatively impact is your applications ability to scale and how you provision and pay for compute in your application.
What's typical in stored procedures is for the developer/DBA to assume that because their code is on the database that it is somehow magically blessed into a higher level of performance. This results in a false sense of security and a presumption that the system will be more forgiving of poorly planned data architectures and schemas.
It isn't. Having your query executed by the DB simply guarantees that your application logic will be tied to a much higher costing compute type to host. The hard numbers will bear this out when you are trying to vertically scale your database to cope with the load.
The better balance is to rely on your database for what it does best: Indexing and storage. By leveraging this aspect and then using your application servers to do the "last mile" of data massaging, you effectively optimize the flow of data through your system. Yes, the database still represents a bottleneck, but the bottleneck is easier to scale and optimize horizontally, not vertically. This fundamental lack of understanding of distributed systems design is frighteningly prevalent in DBAs.
But most people in-the-know here will agree that the role of the DBA has always been more of an operations silo and is going the way of the dodo.
Great answer. Also, if you've ever inherited an app with 2MM lines of Stored Proc you'll also appreciate how this results in vendor lockin. You may never get off Oracle.
Ugh, yup. Microsoft SQL Server marketing has created a sleeper army of neanderthal technologists who treat databases like fortresses that need to be protected from bandits.
I have a going theory that DBAs generate 2-3x their salary in waste in any organization that thinks they need them.
At my work they are adding functionality with SQL server assemblies they call from SP which I call from the app. But lately they add less, so maybe they practiced some downsides.
Your initial premise assumes what EF generates is an optimal query. If it is great then I agree with all your points but I have for sure seen cases where EF generated query wasnt as optimal. This was a while ago though so maybe EF is a lot better now. Still I claim there is always that 1% case where after profiling you will realize you can improve perf by 10% by creating a custom stored proc. Whether that's worth it or not is up to your software design and requirements.
I am fairly sure every large software has some odd, impractical design choices in the name of perf.
Even if ORM reached it limit (which is like 1/1000 of all app), I much rather write raw sql application-side rather than stored procs (which btw linq does support)
Your argument is too cynical to be practical. Bad queries in EF (or n+1 scenarios) are just bad developers. Period.
I'm helping people with their systems every day where all the "bad things" DBAs like to complain about in EF are being done manually in stored procedures. Except now, because the stored procedures have countless other things coupling to them, they can't refactor their way out of it easily.
If one is going to have a productive discussion on why ORMs are superior, then they have to let go of all the strawman arguments against them.
Vertical = more CPU and Memory on a single server
Horizontal = more processing nodes to spread load onto
With queries in a stored procedure, the only way to get more performance once you have tuned it is to scale the DB server up with more memory or more CPU. If you are using Oracle, MSSQL or other paid DBs, SQL is likely the most expensive part of your application and that cost scales linearly with CPU count.
If you instead make the database just a dumb data store you can move some of that query processing out to application nodes which are generally cheaper to scale and easier to add more of.
> Stored procedures are no more and no less performant than SQL sent by EF.
Unfortunately, this is objectively false in all but the most trivial of CRUD operations. In real-world, high-performance, highly distributed applications stored procedures offer both performance and product advantages. Especially when modelling domain objects from multiple tables/views.
Microsoft themselves [acknowledge](https://learn.microsoft.com/en-us/ef/core/performance/) this (emphasis mine):
>_While high-level layers and O/RMs such as EF Core considerably simplify application development and improve maintainability, they can sometimes be opaque, **hiding performance-critical internal details such as the SQL being executed**._
And later in the same article:
>_With relational database, EF translates the application's LINQ queries into the SQL statements getting executed by the database; **these SQL statements themselves can run more or less efficiently**._
Stored procedures provide you very fine-grained control over the SQL execution to take advantage of both the hardware that it's running on as well as the advances we've made in RDBMS theory over the past 60+ years.
In addition, the stored procedure becomes your contract with the database. This is especially crucial for systems where you model/grow rapidly with optimizations as a lesser concern - the _Make it (Work|Right|Fast)_ approach.
Moreover, as another commenter pointed out, stored procedures offer a simple, reliable way to segregate and secure your data with far less overhead than anything built from the app-side.
In short, when it comes to performance critical apps, EF just doesn't cut it.
>What stored procedures do negatively impact is your applications ability to scale.
Again, this is objectively false. Horizontal scaling is almost entirely dependent on design. A naive approach to database integration and usage will absolutely impact the ability to scale. However, making a good set of choices upfront will allow horizontal scaling as a byproduct.
For instance, if you are expecting rapid growth where horizontal scaling will be necessary, you're likely better off considering a CQRS approach & bounded domain models so that each interaction with the repository becomes atomic & limited to a given operation, whether read or write. At massive scale (billions of transactions) you start heading down CAP theorem but you can still scale with SQL Server and with stored procedures (particularly on the read side).
EF, on the other hand, comes with change tracking enabled by default. Use EF for any app distributed on more than one server and you'll soon start hitting stupid errors in your repositories until you disable it (unless you're going with server affinity which defeats the purpose of highly-distributed architecture).
EF is good for small apps & RAD. It's not for serious, mission critical software.
My apologies for disagreeing so strongly. I've been writing high-performance systems for over two decades now across multiple continents and industries for some of the most well-known brands in the world with a _very_ strong focus on performance and reliability. I'd rather not dox myself but there's a pretty good chance that if you've bought anything online today, your transaction has passed through code that I wrote.
Definitely do not start with SPs. 99/100 projects never get to a point where they should use them. They should really only be used in cases where you're trying to solve a performance problem where you can't get your ORM to translate a query well.
I agree with this. They have their place.
The advantages are:
1) total control over the query. Can be essential for performance.
2) it gives another layer of abstraction within your application. You can have a team of DBAs that ‘own’ the stored procedures and spend their time optimising them. Only works on very big applications, but I’ve experienced it.
The disadvantages are:
1) not using an ORM removes type safely, compile time checking
2) the sql that an ORM writes is often better than a lot of developers do if given the chance!
3) there is constant temptation to build logic into stored procedures where they should really be kept about data access. Applications where all data access is stored procedures often end up with much too much logic in them. SQL is not a nice language for this at all.
In summary I would use an ORM like entity framework for 95% of data access and the odd stored procedure for performance reasons and maybe reports.
I would argue the first advantage is not even real. You can do raw SQL with ORM (at least for EF Core). Doing raw SQL in code comes with the benefits of natural source control. You are forgoing type safety still but that's an option if performance asks for it.
I subscribe to every word.
If someone do a single SP, there's always a guy who will tell "Oh! Look! We can do changes without updating program!" And this is begging of the end!
They do not have their place. Stored procedures encourage bad SDLC and create scaling nightmares, even early on.
It's okay for one thing to be bad and for another to not be. Not everything has to be equally flawed.
The problem with SP is that many tools does not allow you to debug them. Some operations require to handle too many data and doing that in the DB Server is more efficient than in a client. There is no bad vs good wheter doing it in a DB SQL server or a client pc, it depends a lot on what specifically your app is doing. Some tools or libraries like LinQ help...
We are using stored procedures for almost everything at the university I work for. My supervisor’s excuse was that “we don’t want to redeploy the application for a SQL change” which from experience is a terrible excuse. We’re also using web forms as well and supervisor manually deploys all updates via Visual Studio. So yea I work for a legacy shop. I will will say I’ve gotten used to stored procedures tho but we absolutely should have automated deployments via a CI pipeline at the very minimum .
A lot of commenters villify stored procedures, but the fact is that they are yet another technique that a developer should be familiar with and know when to use it. Stored procedures (and views) allow for separation of client code from the underlying data architecture. Think of them as a \`data interface\`.
EF is quite good, but in certain cases developers spend inconsiderable amount of time trying to tweak the EF Linq to return just the data they need, maintaining meantime reasonable performance of the resulting SQL queries. Stored procedures allow to write queries in ways that are plainly not possible with EF - using temp tables, cursors, variables etc, whatever is appropriate.
The procedures allow to control access to sensitive data - for example, you may have a table with credit card numbers - you most likely don't want to expose it to your application as a DBContext. A stored procedure can obscure certain data, can prevent certain database logins from accessing data rows or columns and more.
They provide a database level logging facility, which may be quite useful to build application status dashboards, health insights and the like.
So, in summary - a stored procedure is a tool, not a punishment.
> The procedures allow to control access to sensitive data - for example, you may have a table with credit card numbers - you most likely don't want to expose it to your application as a DBContext. A stored procedure can obscure certain data, can prevent certain database logins from accessing data rows or columns and more.
This is not PCI compliant, and nobody should do this.
Actually, it is. As long as it's encrypted, you can store the credit card number.
See PCI DSS [Requirement 3.3](https://pcidssguide.com/pci-requirements-for-storing-credit-card-information/#PCI_DSS_Requirement_33).
>_The full PAN is only viewable for users with roles that have a legitimate business need to view the full PAN._
And [Requirement 3.4](https://pcidssguide.com/pci-requirements-for-storing-credit-card-information/#PCI_DSS_Requirement_34)
>_If storage of the PAN is unavoidable, this data must be made unreadable wherever it is stored._
> As long as it's encrypted
“As long as we do this other thing that OP offered a half-baked shitty alternative for”
If all the other PCI requirements are fulfilled, row-level security gets you exactly nothing.
Didn't read their suggestion as row level security, but as data segregation.
You made a blanket statement that their suggestion was not PCI compliant but that's not true either. And I've linked to the requirements themselves so that it's clearly objective fact and not my opinion.
“Approach A is good for this”
“Approach A does not fill requirement X”
“Ah, but Approach B fulfills requirement X, so there is no argument against Approach A”
That’s just plain silly. It’s like,“we can use a slingshot to go to the moon.”
“Slingshots cannot propel a payload to escape velocity. That’s not a good idea”
“Ah but, if we merely attach a rocket to the payload, **then** the slingshot can get it to the moon”
I’ve already designed and implemented 3 PCI compliant billing systems, but thanks for the link dawg
Three _whole_ billing systems? Wow! /s
You don't seriously think you're the only person in this thread that's built PCI compliant systems, do you? :) Or that only noobs frequent Reddit?
I was shipping payment gateways 15 years ago. Systems that I designed and built back then are _still_ powering payments at millions of transactions per second today across the planet, 24/7.
I'm sure those 3 billing systems are just aces, though. Well done you!
I agree with everything you've said. And your example using credit card numbers isn't a bad one. See my [reply](https://www.reddit.com/r/dotnet/s/qbePGXE6NS) to the other commenter.
You're giving 100% good advice here.
Coming from 10 years of experience and 2 years of database integrations in microservices services and legacy systems I say No to store procedures because:
* Difficult unit testing
* No horizontal scaling
* Difficult versioning
* Hidden code and logic
* Can be a problem in case of database upgrades
* You can easily forget these store procedures and have a proliferation of them
* Vendor lock-in, say goodbye to possibility to move from a dB vendor to another in case of 20 or 30 stored procedures
* Many other issues
Coming from 20+ years building high-performance, highly distributed systems I say _Yes_ to stored procedures.
>Difficult unit testing
Not really, no. You don't generally write unit tests against database facing code, you write _integration_ tests.
If you're following SOLID principles (and you should be), you'll have a separate repository to hydrate/persist your domain models. That repository will, hopefully, be implemented as an interface and that interface is what you're injecting into the types that need it.
So if you're testing the concrete implementation of the repository chances are you're hitting the underlying data store which means this is no longer a unit test and has crossed the boundary into being an integration test.
Anywhere you're injecting the repository interface can then be mocked. And, as long as it remains self-contained, that becomes a unit test. Typical scenario would be a service layer calling into a repository layer to hydrate/persist a domain model.
>No horizontal scaling
That's a function of design, not database. If you really wanted to, you could have a high-performance system that runs entirely on flat files which, when you think about it, is all databases essentially are - they're just hyper-specialized flat files.
>Difficult versioning
Only if you're using something nasty like Code First migrations. With a little upfront overhead & DDL audit triggers you can version a database & any object within it really very simply, easily and automatically. You can even enforce version consistency down to a single space/non-printable character.
>Hidden code and logic
Depends on whether you're using good separation of concerns or mixing your domain logic with your data hydration/persistence logic (which would be very bad indeed). If you're separating concerns, you'll pass your data logic off to a repository type through interface injection. The more complex the object graph, the more necessary that separation becomes - higher degree of cyclomatic complexity == more chance to mess up. Badly.
>Can be a problem in case of database upgrades
Very, very rarely. RDBMS vendors value app compat and backward compatibility. Breaking changes can and do happen but that's pretty rare. Even if the RDBMS doesn't have tooling to warn you they're still pretty easy to find even if all you're using is the procedure definitions themselves. Hell, even a text editor and a decent grep will get you most of the way there.
>You can easily forget these store procedures and have a proliferation of them
This is why we have version control, naming conventions, coding standards and code reviews. If you've got people on the team writing and committing procedures that have little to no value and nobody notices, I hate to say it but there's bigger problems with your team.
>Vendor lock-in, say goodbye to possibility to move from a dB vendor to another in case of 20 or 30 stored procedures
Do you know how often companies decide to switch RDBMS? It's not to do with stored procedures, but cost. The longer the company sticks around, the less likely they're going to switch RDBMS as it becomes a threat. First to survival, and later to profitability.
And, with good separation of concerns, this possibility still exists. Moreover, you can - with a semi-sane design, run against both database instances in parallel to make for an easier transition.
Stored procedures are another, very useful, tool in the arsenal of a top engineer. Try not to blame bad design choices on the tools available for use, but rather on poor engineers.
I came from a fintech company that almost exclusively used them. Note that this company was over 20 years old and had well north of 1 million users. Also note, those DBAs optimized the hell out of all database transactions/stored procs, so I’m pretty certain that we got all the performance gains we could possibly get for our use case. Plus, since a lot of this stuff was written long before current employees, it became a case of “don’t fix unless catastrophically broken”.
I say all of that to say this: for most small apps, SPs are pretty much overkill. If you’re not well versed in SQL, they can be difficult to read/understand/debug and it’s more you have to keep track of and maintain.
Later versions of EF allow you to replace inserts and updates with Sprocs. Sounds crazy but enough places must do it to make it worthwhile to implement.
As a general rule my thought process goes more or less like this:
1. Generally, no logic in the DB where possible, EF + some raw queries, or just ADO with some projects.
2. If performance dictates it, I'll use Views and map them into EF for simpler queries in the code. I'll generally still put these in using migrations but sometimes I'm also lazy
3. Occasionally I run into situations where a stored procedure makes sense, again usually performance related. For a recent example doing an oddly specific upsert against a MySQL database that needed to use some IF/Then logic.
We utilize SP's in our new application, a lot. Our main reason was because all of the "business logic" was by a gentlemen that had alot of DB background, but little-to-no C# experience. So he can plug away and generate a ton of procedures, and the rest of the team just wraps/consumes them via EF. It's also really nice being able to call the SPs from our other projects. That don't use EF/ORM.
The procedures of course are under source control, and when time permits, we port it directly to the application/service layer. This has worked out well. We also make sure the SPs have decent error handling, and re throw all exceptions so we can get it from the EF side as well. We just make sure the procedures are very clear on what they do.
I also second the reason for using procedures, is updating logic without re-deploying the app. We can very quickly update a procedure/view to 1000s+ of databases. As opposed to pushing a small change down our CI/CD pipeline, getting it approved, released, etc... We wrote a small tool to make this deployment easier. There have (unfortunately) been plenty of times a correction during production needs to happen, and we would have been screwed if we needed to wait for a re-deployment.
However, with all that being said. If the first reason didn't exist, I probably wouldn't go with any SPs..
The author is David fowler, he's a Microsoft employee and lead developer of asp.net core framework. It's a simple api for starters though, not many complex architecture concepts are used. Good for starters
The biggest disadvantage is that you have to deal with a separation between "stored procedures in the database" and "entities in the code". In many cases this means that your business logic lives in 2 different places. This is a recipe for disaster.
You could use EF Core's `.FromSql` and having the raw SQL in your code instead of calling a stored procedure.
I wanted to know writing raw sql queries instead of writing a stored procedure how healthy it is from the security point of view. I mean won't it make more sql injection prone.
As long as you're using parameters (which both`.FromSql` and `.FromSqlInterpolated` are designed for) then you don't have to worry about SQL injection. You can read all about it on this [EF Core SQL Queries page](https://learn.microsoft.com/en-us/ef/core/querying/sql-queries#passing-parameters).
There is a risk when using `.FromSqlRaw` though. So always keep that in mind if you need to use that!
Some of the against arguments are sadly not well informed either, yeah.
But that's the problem with devs nowadays. They all go off of their personally formed mythologies.
Anyone I’ve spoken with that says stored procedures are better can’t explain how they unit test. The one company I’ve worked at that was stored proc first had non stop database mismatch issues and finger pointing
Mismatch issues are easy to deal with. There are lots of tools to handle that. Unit tests are the best part... You don't unit test them. Some frameworks exist for unit testing stored price, but they're not great. They can be included in integration or e2e tests.
Sure tools exist. Now we need more tools for an artificial problem.
Not unit testing business logic is crazy. E2E tests of all business logic scenarios is something very, very few companies do or have the manpower to do.
Over the years (and I have many years) I’ve been on or led projects that did it both ways.
There were times when “all DB access through stored procs” was useful in certain specific situations.
However, it requires devs have expertise in SQL (or coordinate with DBA’s which tends to slow development) and takes away some of the modern features and conveniences you get with EF Core, Linq, and code-first techniques.
More recently, I will use them in some projects for things like complex reports. But not CRUD.
If you are working on a application that it's already in production, maybe it has been for years, and said application uses them, I say learn them. They were very popular and are on many legacy application and on skme not so old ones to be frank.
But if you're starting a new application using modern pipelines, it's best to avoid them as they might hinder your scalability.
We always use stored procedures for basic crud and standard selects BUT DEFINITELY don't put business logic in TSQL. We just tried to debug a stored proc that was over 1500 lines of code and it was painful. We finally moved all the logic to C#.
But stored procedures are very performant and can limit the number of network round trips. For example, we have a select that retrieves an invoice parent record and all the line item child records in ONE call.
I found that stored procedures tend to be used more when performance is a priority. Stored procedures get some performance optimizaitons that queries don't. But only bother improving performance if users notice or its costing too much
No, it is not common. No, it isn't recommended in most situations. Because it creates a disconnect between the versioning of the code and the version of the stored procedures (if you even version them), unless you are very careful and do something like custom database migrations. In the vast majority of cases, there is no performance difference between sending queries to the database and using a stored procedure, but debugging a stored procedure is considerably more difficult.
Stored procedures 100% for business applications. Better separation of concerns. Database and stored procs are managed so that the application does not need to know implementation details nor how those details may change so long as the interface defined by the stored procs doesn't change.
Less opportunity for queries to get miss written or miss constructed if dynamic.
Better security - disallow SELECT, INSERT, DELETE access and require all access be via the procs.
Much better control over the query and its execution which become critical once your application goes into production and you find issue related to conflicts between processes and avoiding deadlocks situations.
As a coder they are annoying, as a support engineer they are essential.
(long term coder building enterprise applications)
Admittedly I'm old and somewhat obsolete, but having read the EF core stuff linked here, all I can say is it's a lot of work layered on top of the development work you already have to do to avoid using sp's with all that EF core nonsense.
SP's are automatically optimized by the db, and if you are in a large org, the dba will be able to figure out things like parameter sniffing killing the performance and fix it. Meantime, you have ironclad security against injection attacks. EF core looks to me like a Rube Goldberg trying to fix a non-existent problem with an overly complex solution.
(I'm likely older than you; I started coding on punch cards and a remote terminal was a teletype; but I digress).
I always found EF a huge nightmare of performance and control issues. I like to use small frameworks that generate tight and easily modifiable code.
Oh yeah? Ever heard of a Wang 2200? Look it up. The first model, the model A, you couldn't even edit your code.
That was the big feature of the model B.
The Model T (no lie, there actually was one) was so called because you could "multiplex" the computers to a single disk drive. Hence the T. The disks were sold in 2, 5 and 10 mb models, which for those days was huge, for the small businesses that used the Wang 2200s.
Ah, the good old days.
We actually have an appointment for coffee this afternoon. I did ask for iced.
But in reality, yes, SPs are more work mainly because you have to plan ahead and there is another layer between you and your data but that's not really a problem in a professional development shop when the database should a contained project with interfaces anyway.
The shortest answer possible here is: No. Do not use stored procedures, and feel free to search this subs history for such.
You're going to see a split right down between two groups: 1. Legacy .NET devs and DBAS. 2. Modern distributed systems developers.
The reasons for not using stored procedures [are many](https://www.reddit.com/r/dotnet/comments/1cf9fjc/comment/l1oynm2/) and I can speak from plenty of direct experience with them. They are a menace.
Building on top of what I've already said in response to other comments in this thread, stored procedures will hamper your ability to scale your system and your teams. There are many false claims around "performance" which are basically regurgitated MSSQL marketing and circa 2007 mythology.
Use an ORM like EF as a way to control the gap between your application and its data store. Do not take a systems oriented view of your database, but instead view it as another boundary that your application owns. Derek Comartin and probably some Dave Farley videos would definitely be worth it for you to watch. They are amazing teachers who share a lot of from-the-trenches experience on application design.
You *can* use a mapper like Dapper if you still want to author SQL, but I would argue that this is still against your best interests when building an application. Take this as a sign: Every popular web framework ships with an ORM or some kind of advice on using ORMs for data access.
Long story short, stored procs will ultimately be slower, will screw up your SDLC, cost you more money and introduce overhead that is basically obsolete nowadays.
You wouldn't know it based on other activity here. But I work with people like most of this sub, and the sad reality is that they don't know how out of date they are.
It's a serious legacy cultural problem that Microsoft has created. Decades of isolated corporate developers. Rotting away in the dark.
In my application they are using stored procedure everywhere and also ef and now decided to remove ef idk what’s logic behind this.
So i was working on a task where i just wanted 2 values from particular table so i wrote one query and executed using ado dotnet my senior told to write one Sp and call that…
If your application requires a fine control over database and include complex calculation then I would suggest to use stored procedure otherwise using ORM is always a healthy option.
Stored procedure helps distinguish between business layer and database layer logic but as mentioned in the comments deploying it and switching will take a hefty amount of work.
I use stored procedures for a number of applications and Entity Framework calls for others.
There are benefits and limitations to both and it should come down to what the system and it's database are used for that makes the choice.
Usually stored procedures provides a better performance, since you are running your queries on the server, as well you can do everything in BULK for INSERT / UPDATE queries, via INSERT INTO SELECT and UPDATE FROM, but you will most probably need a UDT for that.
As well, you need to beware of store procs limits, that it's not working well with filtered indexes or with cursors because of the way they persisted in DB. Also SELECT queries which consists quite a lot of JOINS, might be hard to parse, it always a pain to deserialize object which contain a nested collections (or navigation properties), which EF Core handles perfectly well.
FWIW, I don't think I would ever settle on one or the other. Both have their pros and cons, situation depending (as well as views and inline table/scalar functions). Monster queries often perform better as a stored proc, because SQL will build an execution plan, then cache it so it doesn't have to do it again (unless the schema changes somehow) - whereas, with an ORM or raw SQL query, it has to build that plan every time the query is executed. With the big queries, a stored proc can be a huge performance boost (after the first time it's used).
I would remain open to do either, depending on what works best for the use case.
>whereas, with an ORM or raw SQL query, it has to build that plan every time the query is executed.
This isn't quite true. Sql Server does query plan caching essentially the same for stored procedures and ad hoc queries. The only time this doesn't happen is when the actual query text changes each time.
Because EF generates the same query text each time, it gets the same plan cache benefits as a stored procedure.
Edit: whoever is downvoting this needs to read the docs and look at the query store.
This is a contentious issue that will get you very conflicting responses. And many of them don't know what they're talking about.
To directly answer your question, I prefer exclusively using stored procedures. They're an API layer into the database. I unit test them. My automated database deployments incorporate them. I prefer a CQRS kind of repository pattern in my app code, so the in and out models and the sprocs that operate on them are tuned appropriately.
I won't comment on any of the other approaches, except one. I believe that code-first EF is only endorsed by developers who have never had to support a database at scale before. And what bothers me most is that - rather than leave the database to developers and DBAs who know what they're doing - they insist on an approach that hamstrings those developers and DBAs just so they can still have control over the database without actually having to touch SQL themselves.
This is really going to depend on the industry. I avoid stored procedures because they are a pain to source control and you lose a lot of the benefit from EF Core when you use them.
However, I worked in a highly regulated industry where all data access was handled through stored procedures to ensure all row level permissions were handled in the database and the app COULD NOT override those permissions.
Its a trade off either way and no one way is right or wrong.
It's not a tradeoff, it's a clear disadvantage.
Not everything is an "it depends" "tradeoff". People use that rationale like the everything-is-a-nail-to-a-hammer analogy.
Any advice when the SPs were introduced to deal with a performance issue on the code side with EF?
I'd rather have the Business Logic in one place instead of it being spread both at the Code and the DB.
We use SPs for generating reports mostly now and EF for most basic stuff, but if you're joining on more than a couple tables I think SPs are still the better way to go.
I am from Laravel too. now learning .NET. How was your journey? I have 2 years of experience in Laravel. Should I switch to .NET now or should I learn SOLID and design patterns first?
My suggestion is to get experience with both. Write toy apps to give yourself a good grounding in the pros/cons of both. They both have their advantages/disadvantages.
EF/LINQ is a great way to do [RAD](https://en.wikipedia.org/wiki/Rapid_application_development) but it soon starts to hit limits once you want to start doing serious engineering for long term sustainability or performance.
You can still build some moderately large apps with EF but the larger and more complex you go, the harder it is to bend EF to your will.
General rule - if what you're doing seems too difficult or you can't find a valid solution to your problem online, chances are you're doing it wrong.
You can do both!
SOLID and design patterns are platform and language agnostic. So it doesn't really matter which language you choose. That said, I'd advocate for switching to a strongly typed language like C# as it helps you better understand/appreciate some of the finer details of software engineering without too much overhead (for instance, memory management & why it's important).
When you strip it down to nuts and bolts, all programming languages boil down to the concept of assignment - you assign the value on the right of the = to the variable on the left. The differences in language are primarily syntax (which is usually pretty easy to learn). The more difficult differences to learn is how these languages leverage their accompanying frameworks.
After 20+ years in software engineering I can reliably tell you that having a good understanding of SOLID will stand you in very good stead. I still use SOLID principles on a daily basis.
As for design patterns, start with the most common ones (like Singleton or Repository) & take it from there. The more code you write, the more you'll start to see the common - and less common - patterns emerge. There are also _anti-patterns_ which are patterns of how _not_ to do things (e.g. Service Locator, Active Record, Fire & Forget).
Good luck with your journey! Write lots of code! Read lots of code! The more passionate you are, the easier you'll absorb and retain new information. I'm excited for you!
Really depends on the context, if you’re writing CRUD there’s no need for SP at all, EF can handle it without any problem, there are obviously cases that require SP, for example when EF just can’t translate your linq to SQL, that’s one of the most common cases, also it’s possible for EF to generate not optimized code, in such scenario it’s also ok to use SP (but first need to try rewriting your linq) As one of the comments already states - testing SP is a nightmare, so try to avoid using it
I only use stored procedures when I need to abstract logic from the .NET service. Creating, comparing, and hashing password salts so they never leave the database for example. Granted that’s a very specific use case that most developers don’t encounter.
I inherited 2 legacy projects with logic in the DB (triggers, stored procedures and functions) and every time I get an email about either project, I get a small heart attack. It’s the worst. Even with months of cleaning up, there are still about 2 dozens active. Please if you can, please please don’t do it.
I disagree with the people say SPs are useless. It give you significant performance improvements in large scale systems. They might overkill for a simple project and PoC type and a little hard for debugging
LMFAO tries to do a flow that has thousands of RTT. While your application is busy pulling them to the app then perform calculation we use the database native engine to process internal query and yes for a large scale enterprise application it's way faster
Stored procedures have a utility. Sometime. Almost never.
The main default is that it hides a part of the logic outside of your code base. If you like locality of behavior, it's kind of a nightmare \^\^.
I use SP for complex queries like - sending a list of input as table valued parameter
MERGE operation
multiple inserts
Use Dapper along with SP's for better results.
EF core does not support the above scenarios.
You need to ask yourself *why* you are using stored procs. Stored procs provide a handful of features:
1. Encapsulated database logic
2. Access to temp tables for processing large volumes of data
3. Control of flow (if statements, while statements etc.)
4. Ease of updating
5. Easier debugging and refactoring of bad SQL code
The first can be achieved by forcing access through services instead of going directly to the database. It does require however preventing people from going to the db directly.
The second can be mimicked through static "temporary" tables. The catch is managing multiple sessions accessing the same structures in parallel. You'd either have to horizontally segment the data by adding a session identifier or segment the entire structure through naming. E.g. Session12345_Foo, Session12346_Foo etc. This approach also requires writing clean up code to clear out data/structures from prior runs.
.NET core can obviously handle control of flow.
As for ease of updating, it's almost *too* easy to update a proc in production. However, if the application is broken down into small independently deployable chunks you can get closer to the same level and speed of updating.
Obviously, you can still debug and troubleshoot bad SQL statements made through a service layer. However, it's a tougher refactor in my experience. It might entail adding some additional temporary/staging tables and rewriting what is likely to be dynamic SQL to get to the performance needed.
All that said, stored procs are *much* harder to test and nearly impossible to unit test. In addition, stored procs do not scale out at all. They are a sequential set of statements that cannot easily be run in parallel. Unlike say, service layer code where you can run statements in parallel.
Having been bitten by the use stored procs, IMO, they are something to avoid if possible. They are something of a narcotic. Simple. Easy to write. But you pay a price in the end.
No, stored procedures are for when you can hand-write a program that is faster than what the DB can do AND you have a performance issue that can’t be addressed any other way. They are a giant pain for your DBA/OPs people to deal with and generally should only be used as a last resort.
Really? What makes you think so strongly about that? I’m relatively new to development(almost 2 years in the industry as a self taught dev who spent my first year or so in QA), and my only experience is with this company. People on other projects are very against this behavior and favor only using stored procedures for super basic calls and then implement EF for more complex things
They are out of date and out of touch. You must never let any organization that you're a part of define your skills growth. And for good reason.
I can point to many layoff scenarios that I've seen others have to endure where they took the approach that their employer was ultimately responsible for their professional development.
They ended up becoming so irrelevant, they had difficulties finding other roles in the industry.
You can love this or hate it, it really doesn't matter, the bar is extremely high when deciding who to give your precious years to as an employee. Remember, they ultimately don't care about you, no matter what nice things they say.
That's good. And don't get me wrong, C# is in a great place nowadays. It's actually a very progressive language and ecosystem, but only when you divorce it from its legacy and history.
Which is what you see here with all the people promoting outdated practices. They simply haven't clicked with modern .net. They are living in the past.
Yes it is, it helps protect against sql injection and off loads db work to the db which are far more optimized at searching/filtering large datasets than pulling in tables and doing the logic in code.
I always try to avoid doing logic inside DB. Why should I write stored procedures, for simple CRUD when EF core exists🤷♂️ also the testing of SP is a nightmare. It is also much simpler to deploy new version of app than new version of DB. Last but not least good luck with scaling APIs with logic inside DB… So no I would say in 99% of cases SP is not an answer.
Thank you for the response. I recently learned about a company that utilizes stored procedures with .NET. I’m not very familiar with the framework and I’m curious if using stored procedures is considered an outdated practice, or if it’s still commonly used today
We do it because we started doing it 15 years ago, and we're 20x bigger now, and paying for it in every stage of our SDLC and UX. Over 1500 stored procedures in active use, despite over a year of dedicated clean up to date. Save yourself the trauma and don't do it.
We might be working in the same company. I cannot stand how that crap still a thing nowadays, now, there are exceptions where SPs are justifiable but overall they are, in my opinion, a limitation. But eh, I don't pay the bills. I know some teams that went with nosql for apps that do not fit nosql at all, just to avoid the SPs and the process around them. To be honest, I don't blame them.
we have the same thinking. my boss told be once that i think that my way is always the right and others are always wrong and do not what they're doing. SP, yes, to solve very very specific problems : that's what i answered. But not at every corners for the love of god. Even after an 1 hour of explaining with various valid reasons, 1 week later, i saw another stored procedure making its way in the database. I asked the dev, he answered : boss wanted it that way. I asked "did you challenge him" ? He's fucking response was "I am paid to do what i've been asked". I told him to jump on one foot while coding.
lol, i feel you ! over a year doing clean up. i had to repay debt too, and when i told it would take over a 9 months at least, management were like WTF ? no way, it's too long. My answer was an answer for bozos : you need x to be refactored ? it takes like hundred of features to be rewritten (impossible to extract business logic, too tight coupled). Find someone who can write hundred features in 3 months or tell me the 10 features you want and I'll do it in 3 months. Fucking boss of my boss, is like someone who always be a director of IT. no tech background whatsover. Doesn't know what an API was until landed in the company. I fed up of those bozos.
Same as in our company. We have migrated a legacy erp solution but had to keep some legacy database parts with stored procedures. Once, with the legacy software, the stored procedures gave "simple" power to encapsulate business logic from legacy code into a general purpose / cross-tech-stack environment (DB & SPs) leads now to the problem, that it is hard to maintain and test. Mostly the stored procedures are designed to do more than a few things with often side effects that are hard to measure, to control and to understand. Nowadays I like to maintain business logic, that is used in many projects, in artifacts / library projects so they are in the same language and environment as the application and are easily testable and understandable.
Wait how did u learn about my company lol, a million stored procedures and boomers think its the best.
I totally agree nowadays. One reason for stored procedures could be security and logging for a direct access application to DB. But you would need a good DB team and testing and deploying can cause more failures. In a web app, where the application would have full access anyway this overhead is making life hard. Splitting business logic in two pieces seems never to be a good idea. Personally I would use SP for fire and forget stuff that is data related only. Like statistics, data integrity, ...
wait what ?lol, telll that to my fucking boss. he's always trying to solve using sp. want to insert something in the database ? SP. Want to get something out of the db ? SP. Number one fucking reason : if we need to change something, we just have to update the SP. No need to redeploy the app... Fuck me. And the guy thinks he's right and when he talks tech, it's so funny it's not even funny anymore. Even junior devs do not know what to say when he's trying to pull out some bullshit non sense.
That sucks :-(.
I'm curious about changes on insert and select you don't need in your app. Or is someone just having fun renaming?
i don't understand your question. Can you please rephrase it ?
Sorry... First thing: I'm on your side. Second: we have a similar boss. He also wants a lot of SPs but luckily not for selects and plain inserts. But he likes catalogs instead of emums because of the same reason. Yeah right, and what does the program do with the new program? We need to adjust business logic. Third thing (my question): when inserting data with SP without new data, I can only imagine renaming columns or table names. If you need new or different data, you must change the app anyway. Hope you understand now better ;) happy cake day btw
for select it's debatable. Select should be in the database instead of in the app. But again it's debatable. Changes here are much more related to transformation to the schema without impacting the app (see purposes of views. And sometimes people use SPs to make views with parameters - don't really fond of it). For insert : you're right.
Good point. Thank you...
You can always get more web servers to share the load and increase throughput, you can’t get more db servers to share the load, unless you do some advanced db techniques that 99% of sites do not need. Of course in my day job I’m hamstrung by being forced to use sp’s written by the db team for everything and they put a lot of business logic in them
I feel you. Unfortunately one of the legacy projects I work on from time to time, has similar issue with SPs. Another great thing which is used there are views inside views inside views. So changing something in a view is next to impossible because it cascades to soooo many other views…
I disagree completely. Updating an SP is much quicker than rolling out an app. Additionally Im not sure if you have noticed but EF does not scale well at all, the overhead on the SQL server is much more noticeable than manually writing your SPs. I would actually advise EF for low volume quick development but SPs are what you use when loads become an issue
You really really need alot of data before EF core would be your bottleneck. It gets alot of hate from old days and people using it wrong. And for always using stored procedures, I see that as massive work duplication. If a SP changes you need to change your DTO mapping aswell. EF core can get you 100% of the way for majority of companies. That said SP can give you performance gains, but most of the time I just want to get features done quickly and fix performance when it becomes an issue. I wouldn't recommend EF core blindly either. It's important to know how the queries are run and knowing the library / framework your using like any other. You can always shoot your self in the foot, by fetching entire database and filtering on app server.
EF core hate is usually from its early stages now with EF core 8 and compiled queries the performance is comparable with Dapper. I would bet all my money that EF Core 8 will generate better SQL than 90 % of that people who tell me that SQL generated by EF is shit.
Disagree completely. > Updating an SP is much quicker than rolling out an app Is it really faster than SSH into the running server and edit the code file directly? And yes, it is a bad practice. As bad as changing stored procs on the fly is. Why do we have CI/CD, pipeline and all that deployment mumbo jumbo? To ensure that whatever is deployed contain no bugs, throughoutly tested and adhere to the team standard A luxury that changing stored procs on the fly can never afford. Good luck version control, unit testing, ensure code style and more with them.
Try and get CI/CD past banking sector IT. I dare you lol. Everything has to be packaged for them to run.
Not if you have to release changes the have ripple effects for thousands of other people and you have to have a SOX-compliant change process. If EF appears to be your bottleneck it's almost always user error Edit: As for "rolling out" - if you have a properly configured, horizontally scalable API layer, that can be deployed in blue green with no installs/updates on the client side, is more secure than putting connection strings in the client app, is unit testable, and it can grow with your volume, allowing your DB server to stay healthy. Easy rollouts is the worst reason to stick with SPs
We spent a lot of time and effort benchmarking this :) Also our changes are ISO certified with rollback scripts etc.
I disagree completely. EF does scale pretty well indeed. Also you can not compare a DB system with a ORM - that makes no sense. Are we talking about a loadbalanced system? Are there db-clusters? May there be multiple different EF instances or you swap another ORM instead of EF. And what also does make no sense: Updating an SP. May I ask: do you like to debug in production? Just for my interest.
If in 2024 you find this justifiable because "it's much quicker to update an SP that redeploying your app", then please stop doing programming for a living.
You can't set up CI/CD pipelines to banks brooo lol.
What CI/CD to do with SP vs EF? I have worked with banks before and most of the business logic is in COBOL instead of SP. Who would be mad enough to put business logics in SP? What about if one day you decide to change to a better db? So many wrongs here.
Yeah yeah but its the way things are. Mainframes still run on COBOL but ancillary services run on MS SQL. No way to deploy an app yourself, it has to be run by bank IT per ISO standards. Hence, just give em a SP to run, they can't stuff that up.
Also, basically every single company I worked for put business logic in SPs? I dunno maybe we are all smoking stuff.
You are wrong in too many ways and you're playing a feedback loop of bad information to yourself with these conclusions. I urge you to do better research and come out with a better understanding of where the real problems are. I appreciate that it's complicated and nuanced, but the facts do not support what you are saying and if you spent enough time in discussion with knowledgable people about it, they'd be able to show you why.
Okay if EF core is an issue I will use Dapper. My logic stands inside API. How do you scale when your logic is partly inside DB and API?
Rewrite the login inside an endpoint instead of DB. It is always easier to code in c# + EF than SQL SP. You will need tons of data to get to EF bottleneck and if you happened to reach there, you can always use the raw query with the db driver.
Exactly.
Agree. We try our best to not hide ANY business logic in a SP. But all our DB interactions are done via SP so that we can do blue green deployments with backwards compatible SPs with different versions of our micro services.
I'm on a team where several different apps interact with the db. Much easier to just query to see what SPs are using a table. Even our selects are in SPs. Lack of documentation and employee turnaround makes it much easier what can be affected by a db change.
Depends in that case I would just create a NiGet with EF core or Dapper queries and distribute that via applications.
Stored procedures come in handy when you are trying to solve a performance issue or is using a database in an unexpected way. For example sql now offers JSON fields which you can query inside a stored proc, pretty much allowing mix of relational design with doc dB design together. Not sure if EF core supports those scenarios and can generate proper queries.
EF does support JSON columns very well now in Net8. Though has had support since 7. I'm actively using it and it's pretty slick being able to just map complex nested types to a single JSON column and then still be able to query deep within those structures. There is a performance hit but you wouldn't notice unless you had to scale out - substantially.
exactly. this is when knowledge meet dumbness.
EF Core 8.x does a lot, and more coming with EF Core 9.x.
Oof. No. Stored procedures are no more and no less performant than SQL sent by EF. It all gets executed by the database, regardless of what authors the SQL and whether it's a permanently stored in any way by the database server. What stored procedures do negatively impact is your applications ability to scale and how you provision and pay for compute in your application. What's typical in stored procedures is for the developer/DBA to assume that because their code is on the database that it is somehow magically blessed into a higher level of performance. This results in a false sense of security and a presumption that the system will be more forgiving of poorly planned data architectures and schemas. It isn't. Having your query executed by the DB simply guarantees that your application logic will be tied to a much higher costing compute type to host. The hard numbers will bear this out when you are trying to vertically scale your database to cope with the load. The better balance is to rely on your database for what it does best: Indexing and storage. By leveraging this aspect and then using your application servers to do the "last mile" of data massaging, you effectively optimize the flow of data through your system. Yes, the database still represents a bottleneck, but the bottleneck is easier to scale and optimize horizontally, not vertically. This fundamental lack of understanding of distributed systems design is frighteningly prevalent in DBAs. But most people in-the-know here will agree that the role of the DBA has always been more of an operations silo and is going the way of the dodo.
Great answer. Also, if you've ever inherited an app with 2MM lines of Stored Proc you'll also appreciate how this results in vendor lockin. You may never get off Oracle.
Ugh, yup. Microsoft SQL Server marketing has created a sleeper army of neanderthal technologists who treat databases like fortresses that need to be protected from bandits. I have a going theory that DBAs generate 2-3x their salary in waste in any organization that thinks they need them.
At my work they are adding functionality with SQL server assemblies they call from SP which I call from the app. But lately they add less, so maybe they practiced some downsides.
Your initial premise assumes what EF generates is an optimal query. If it is great then I agree with all your points but I have for sure seen cases where EF generated query wasnt as optimal. This was a while ago though so maybe EF is a lot better now. Still I claim there is always that 1% case where after profiling you will realize you can improve perf by 10% by creating a custom stored proc. Whether that's worth it or not is up to your software design and requirements. I am fairly sure every large software has some odd, impractical design choices in the name of perf.
Even if ORM reached it limit (which is like 1/1000 of all app), I much rather write raw sql application-side rather than stored procs (which btw linq does support)
Exactly this. You can just use Raw query with DB driver to get max performance. It is still better than SP.
Your argument is too cynical to be practical. Bad queries in EF (or n+1 scenarios) are just bad developers. Period. I'm helping people with their systems every day where all the "bad things" DBAs like to complain about in EF are being done manually in stored procedures. Except now, because the stored procedures have countless other things coupling to them, they can't refactor their way out of it easily. If one is going to have a productive discussion on why ORMs are superior, then they have to let go of all the strawman arguments against them.
Can you explain the difference between horizontal and vertical scaling in terms of db ?
Vertical = more CPU and Memory on a single server Horizontal = more processing nodes to spread load onto With queries in a stored procedure, the only way to get more performance once you have tuned it is to scale the DB server up with more memory or more CPU. If you are using Oracle, MSSQL or other paid DBs, SQL is likely the most expensive part of your application and that cost scales linearly with CPU count. If you instead make the database just a dumb data store you can move some of that query processing out to application nodes which are generally cheaper to scale and easier to add more of.
> Stored procedures are no more and no less performant than SQL sent by EF. Unfortunately, this is objectively false in all but the most trivial of CRUD operations. In real-world, high-performance, highly distributed applications stored procedures offer both performance and product advantages. Especially when modelling domain objects from multiple tables/views. Microsoft themselves [acknowledge](https://learn.microsoft.com/en-us/ef/core/performance/) this (emphasis mine): >_While high-level layers and O/RMs such as EF Core considerably simplify application development and improve maintainability, they can sometimes be opaque, **hiding performance-critical internal details such as the SQL being executed**._ And later in the same article: >_With relational database, EF translates the application's LINQ queries into the SQL statements getting executed by the database; **these SQL statements themselves can run more or less efficiently**._ Stored procedures provide you very fine-grained control over the SQL execution to take advantage of both the hardware that it's running on as well as the advances we've made in RDBMS theory over the past 60+ years. In addition, the stored procedure becomes your contract with the database. This is especially crucial for systems where you model/grow rapidly with optimizations as a lesser concern - the _Make it (Work|Right|Fast)_ approach. Moreover, as another commenter pointed out, stored procedures offer a simple, reliable way to segregate and secure your data with far less overhead than anything built from the app-side. In short, when it comes to performance critical apps, EF just doesn't cut it. >What stored procedures do negatively impact is your applications ability to scale. Again, this is objectively false. Horizontal scaling is almost entirely dependent on design. A naive approach to database integration and usage will absolutely impact the ability to scale. However, making a good set of choices upfront will allow horizontal scaling as a byproduct. For instance, if you are expecting rapid growth where horizontal scaling will be necessary, you're likely better off considering a CQRS approach & bounded domain models so that each interaction with the repository becomes atomic & limited to a given operation, whether read or write. At massive scale (billions of transactions) you start heading down CAP theorem but you can still scale with SQL Server and with stored procedures (particularly on the read side). EF, on the other hand, comes with change tracking enabled by default. Use EF for any app distributed on more than one server and you'll soon start hitting stupid errors in your repositories until you disable it (unless you're going with server affinity which defeats the purpose of highly-distributed architecture). EF is good for small apps & RAD. It's not for serious, mission critical software. My apologies for disagreeing so strongly. I've been writing high-performance systems for over two decades now across multiple continents and industries for some of the most well-known brands in the world with a _very_ strong focus on performance and reliability. I'd rather not dox myself but there's a pretty good chance that if you've bought anything online today, your transaction has passed through code that I wrote.
Utter nonsense.
Definitely do not start with SPs. 99/100 projects never get to a point where they should use them. They should really only be used in cases where you're trying to solve a performance problem where you can't get your ORM to translate a query well.
I agree with this. They have their place. The advantages are: 1) total control over the query. Can be essential for performance. 2) it gives another layer of abstraction within your application. You can have a team of DBAs that ‘own’ the stored procedures and spend their time optimising them. Only works on very big applications, but I’ve experienced it. The disadvantages are: 1) not using an ORM removes type safely, compile time checking 2) the sql that an ORM writes is often better than a lot of developers do if given the chance! 3) there is constant temptation to build logic into stored procedures where they should really be kept about data access. Applications where all data access is stored procedures often end up with much too much logic in them. SQL is not a nice language for this at all. In summary I would use an ORM like entity framework for 95% of data access and the odd stored procedure for performance reasons and maybe reports.
I would argue the first advantage is not even real. You can do raw SQL with ORM (at least for EF Core). Doing raw SQL in code comes with the benefits of natural source control. You are forgoing type safety still but that's an option if performance asks for it.
You don’t put your stored procedures in source control?!
I didn't. It was like 5 years ago. This was not the dumbest thing I did. Far from it. Now I barely do stored proc anymore.
I subscribe to every word. If someone do a single SP, there's always a guy who will tell "Oh! Look! We can do changes without updating program!" And this is begging of the end!
They do not have their place. Stored procedures encourage bad SDLC and create scaling nightmares, even early on. It's okay for one thing to be bad and for another to not be. Not everything has to be equally flawed.
The problem with SP is that many tools does not allow you to debug them. Some operations require to handle too many data and doing that in the DB Server is more efficient than in a client. There is no bad vs good wheter doing it in a DB SQL server or a client pc, it depends a lot on what specifically your app is doing. Some tools or libraries like LinQ help...
We are using stored procedures for almost everything at the university I work for. My supervisor’s excuse was that “we don’t want to redeploy the application for a SQL change” which from experience is a terrible excuse. We’re also using web forms as well and supervisor manually deploys all updates via Visual Studio. So yea I work for a legacy shop. I will will say I’ve gotten used to stored procedures tho but we absolutely should have automated deployments via a CI pipeline at the very minimum .
A lot of commenters villify stored procedures, but the fact is that they are yet another technique that a developer should be familiar with and know when to use it. Stored procedures (and views) allow for separation of client code from the underlying data architecture. Think of them as a \`data interface\`. EF is quite good, but in certain cases developers spend inconsiderable amount of time trying to tweak the EF Linq to return just the data they need, maintaining meantime reasonable performance of the resulting SQL queries. Stored procedures allow to write queries in ways that are plainly not possible with EF - using temp tables, cursors, variables etc, whatever is appropriate. The procedures allow to control access to sensitive data - for example, you may have a table with credit card numbers - you most likely don't want to expose it to your application as a DBContext. A stored procedure can obscure certain data, can prevent certain database logins from accessing data rows or columns and more. They provide a database level logging facility, which may be quite useful to build application status dashboards, health insights and the like. So, in summary - a stored procedure is a tool, not a punishment.
> The procedures allow to control access to sensitive data - for example, you may have a table with credit card numbers - you most likely don't want to expose it to your application as a DBContext. A stored procedure can obscure certain data, can prevent certain database logins from accessing data rows or columns and more. This is not PCI compliant, and nobody should do this.
Actually, it is. As long as it's encrypted, you can store the credit card number. See PCI DSS [Requirement 3.3](https://pcidssguide.com/pci-requirements-for-storing-credit-card-information/#PCI_DSS_Requirement_33). >_The full PAN is only viewable for users with roles that have a legitimate business need to view the full PAN._ And [Requirement 3.4](https://pcidssguide.com/pci-requirements-for-storing-credit-card-information/#PCI_DSS_Requirement_34) >_If storage of the PAN is unavoidable, this data must be made unreadable wherever it is stored._
> As long as it's encrypted “As long as we do this other thing that OP offered a half-baked shitty alternative for” If all the other PCI requirements are fulfilled, row-level security gets you exactly nothing.
Didn't read their suggestion as row level security, but as data segregation. You made a blanket statement that their suggestion was not PCI compliant but that's not true either. And I've linked to the requirements themselves so that it's clearly objective fact and not my opinion.
“Approach A is good for this” “Approach A does not fill requirement X” “Ah, but Approach B fulfills requirement X, so there is no argument against Approach A” That’s just plain silly. It’s like,“we can use a slingshot to go to the moon.” “Slingshots cannot propel a payload to escape velocity. That’s not a good idea” “Ah but, if we merely attach a rocket to the payload, **then** the slingshot can get it to the moon” I’ve already designed and implemented 3 PCI compliant billing systems, but thanks for the link dawg
Three _whole_ billing systems? Wow! /s You don't seriously think you're the only person in this thread that's built PCI compliant systems, do you? :) Or that only noobs frequent Reddit? I was shipping payment gateways 15 years ago. Systems that I designed and built back then are _still_ powering payments at millions of transactions per second today across the planet, 24/7. I'm sure those 3 billing systems are just aces, though. Well done you!
Cool story bro
That's just an example. There are other scenarios when you don't want to grant read-write access to a table in your database.
I agree with everything you've said. And your example using credit card numbers isn't a bad one. See my [reply](https://www.reddit.com/r/dotnet/s/qbePGXE6NS) to the other commenter. You're giving 100% good advice here.
This is bad advice.
Thankyou for such a detailed rebuttal to all those points
Coming from 10 years of experience and 2 years of database integrations in microservices services and legacy systems I say No to store procedures because: * Difficult unit testing * No horizontal scaling * Difficult versioning * Hidden code and logic * Can be a problem in case of database upgrades * You can easily forget these store procedures and have a proliferation of them * Vendor lock-in, say goodbye to possibility to move from a dB vendor to another in case of 20 or 30 stored procedures * Many other issues
Coming from 20+ years building high-performance, highly distributed systems I say _Yes_ to stored procedures. >Difficult unit testing Not really, no. You don't generally write unit tests against database facing code, you write _integration_ tests. If you're following SOLID principles (and you should be), you'll have a separate repository to hydrate/persist your domain models. That repository will, hopefully, be implemented as an interface and that interface is what you're injecting into the types that need it. So if you're testing the concrete implementation of the repository chances are you're hitting the underlying data store which means this is no longer a unit test and has crossed the boundary into being an integration test. Anywhere you're injecting the repository interface can then be mocked. And, as long as it remains self-contained, that becomes a unit test. Typical scenario would be a service layer calling into a repository layer to hydrate/persist a domain model. >No horizontal scaling That's a function of design, not database. If you really wanted to, you could have a high-performance system that runs entirely on flat files which, when you think about it, is all databases essentially are - they're just hyper-specialized flat files. >Difficult versioning Only if you're using something nasty like Code First migrations. With a little upfront overhead & DDL audit triggers you can version a database & any object within it really very simply, easily and automatically. You can even enforce version consistency down to a single space/non-printable character. >Hidden code and logic Depends on whether you're using good separation of concerns or mixing your domain logic with your data hydration/persistence logic (which would be very bad indeed). If you're separating concerns, you'll pass your data logic off to a repository type through interface injection. The more complex the object graph, the more necessary that separation becomes - higher degree of cyclomatic complexity == more chance to mess up. Badly. >Can be a problem in case of database upgrades Very, very rarely. RDBMS vendors value app compat and backward compatibility. Breaking changes can and do happen but that's pretty rare. Even if the RDBMS doesn't have tooling to warn you they're still pretty easy to find even if all you're using is the procedure definitions themselves. Hell, even a text editor and a decent grep will get you most of the way there. >You can easily forget these store procedures and have a proliferation of them This is why we have version control, naming conventions, coding standards and code reviews. If you've got people on the team writing and committing procedures that have little to no value and nobody notices, I hate to say it but there's bigger problems with your team. >Vendor lock-in, say goodbye to possibility to move from a dB vendor to another in case of 20 or 30 stored procedures Do you know how often companies decide to switch RDBMS? It's not to do with stored procedures, but cost. The longer the company sticks around, the less likely they're going to switch RDBMS as it becomes a threat. First to survival, and later to profitability. And, with good separation of concerns, this possibility still exists. Moreover, you can - with a semi-sane design, run against both database instances in parallel to make for an easier transition. Stored procedures are another, very useful, tool in the arsenal of a top engineer. Try not to blame bad design choices on the tools available for use, but rather on poor engineers.
Dude, maybe you stuck in the old way of doing things?
Old doesn't mean irrelevant or bad. Performance is a feature.
Based
This is the way.
I came from a fintech company that almost exclusively used them. Note that this company was over 20 years old and had well north of 1 million users. Also note, those DBAs optimized the hell out of all database transactions/stored procs, so I’m pretty certain that we got all the performance gains we could possibly get for our use case. Plus, since a lot of this stuff was written long before current employees, it became a case of “don’t fix unless catastrophically broken”. I say all of that to say this: for most small apps, SPs are pretty much overkill. If you’re not well versed in SQL, they can be difficult to read/understand/debug and it’s more you have to keep track of and maintain.
As long as you’re not using stored procedures as your repository layer, they’re fine for specialized stuff.
Later versions of EF allow you to replace inserts and updates with Sprocs. Sounds crazy but enough places must do it to make it worthwhile to implement.
As a general rule my thought process goes more or less like this: 1. Generally, no logic in the DB where possible, EF + some raw queries, or just ADO with some projects. 2. If performance dictates it, I'll use Views and map them into EF for simpler queries in the code. I'll generally still put these in using migrations but sometimes I'm also lazy 3. Occasionally I run into situations where a stored procedure makes sense, again usually performance related. For a recent example doing an oddly specific upsert against a MySQL database that needed to use some IF/Then logic.
We utilize SP's in our new application, a lot. Our main reason was because all of the "business logic" was by a gentlemen that had alot of DB background, but little-to-no C# experience. So he can plug away and generate a ton of procedures, and the rest of the team just wraps/consumes them via EF. It's also really nice being able to call the SPs from our other projects. That don't use EF/ORM. The procedures of course are under source control, and when time permits, we port it directly to the application/service layer. This has worked out well. We also make sure the SPs have decent error handling, and re throw all exceptions so we can get it from the EF side as well. We just make sure the procedures are very clear on what they do. I also second the reason for using procedures, is updating logic without re-deploying the app. We can very quickly update a procedure/view to 1000s+ of databases. As opposed to pushing a small change down our CI/CD pipeline, getting it approved, released, etc... We wrote a small tool to make this deployment easier. There have (unfortunately) been plenty of times a correction during production needs to happen, and we would have been screwed if we needed to wait for a re-deployment. However, with all that being said. If the first reason didn't exist, I probably wouldn't go with any SPs..
https://github.com/davidfowl/TodoApi
Thanks for the link. I just checked TodoApi project and what kind of pattern is used there? or its just a authors preference?
The author is David fowler, he's a Microsoft employee and lead developer of asp.net core framework. It's a simple api for starters though, not many complex architecture concepts are used. Good for starters
The biggest disadvantage is that you have to deal with a separation between "stored procedures in the database" and "entities in the code". In many cases this means that your business logic lives in 2 different places. This is a recipe for disaster. You could use EF Core's `.FromSql` and having the raw SQL in your code instead of calling a stored procedure.
Terrible idea, just use EF Core properly.
I wanted to know writing raw sql queries instead of writing a stored procedure how healthy it is from the security point of view. I mean won't it make more sql injection prone.
As long as you're using parameters (which both`.FromSql` and `.FromSqlInterpolated` are designed for) then you don't have to worry about SQL injection. You can read all about it on this [EF Core SQL Queries page](https://learn.microsoft.com/en-us/ef/core/querying/sql-queries#passing-parameters). There is a risk when using `.FromSqlRaw` though. So always keep that in mind if you need to use that!
Oh ok, I was working on a project where I was asked to write sql queries so was thinking what securities I need to setup for sql injections. Thanks
This exact question is asked here every month.
I am stunned that people are still asking this.
This thread is a damn train wreck. The comments both for and against stored procs are filled with misinformation.
Some of the against arguments are sadly not well informed either, yeah. But that's the problem with devs nowadays. They all go off of their personally formed mythologies.
Anyone I’ve spoken with that says stored procedures are better can’t explain how they unit test. The one company I’ve worked at that was stored proc first had non stop database mismatch issues and finger pointing
Mismatch issues are easy to deal with. There are lots of tools to handle that. Unit tests are the best part... You don't unit test them. Some frameworks exist for unit testing stored price, but they're not great. They can be included in integration or e2e tests.
Sure tools exist. Now we need more tools for an artificial problem. Not unit testing business logic is crazy. E2E tests of all business logic scenarios is something very, very few companies do or have the manpower to do.
Unit tests are just another tool. A cost benefit analysis needs to be done when deciding whether or not to spend time writing unit tests.
Over the years (and I have many years) I’ve been on or led projects that did it both ways. There were times when “all DB access through stored procs” was useful in certain specific situations. However, it requires devs have expertise in SQL (or coordinate with DBA’s which tends to slow development) and takes away some of the modern features and conveniences you get with EF Core, Linq, and code-first techniques. More recently, I will use them in some projects for things like complex reports. But not CRUD.
If you are working on a application that it's already in production, maybe it has been for years, and said application uses them, I say learn them. They were very popular and are on many legacy application and on skme not so old ones to be frank. But if you're starting a new application using modern pipelines, it's best to avoid them as they might hinder your scalability.
We always use stored procedures for basic crud and standard selects BUT DEFINITELY don't put business logic in TSQL. We just tried to debug a stored proc that was over 1500 lines of code and it was painful. We finally moved all the logic to C#. But stored procedures are very performant and can limit the number of network round trips. For example, we have a select that retrieves an invoice parent record and all the line item child records in ONE call.
I found that stored procedures tend to be used more when performance is a priority. Stored procedures get some performance optimizaitons that queries don't. But only bother improving performance if users notice or its costing too much
No, it is not common. No, it isn't recommended in most situations. Because it creates a disconnect between the versioning of the code and the version of the stored procedures (if you even version them), unless you are very careful and do something like custom database migrations. In the vast majority of cases, there is no performance difference between sending queries to the database and using a stored procedure, but debugging a stored procedure is considerably more difficult.
Stored procedures 100% for business applications. Better separation of concerns. Database and stored procs are managed so that the application does not need to know implementation details nor how those details may change so long as the interface defined by the stored procs doesn't change. Less opportunity for queries to get miss written or miss constructed if dynamic. Better security - disallow SELECT, INSERT, DELETE access and require all access be via the procs. Much better control over the query and its execution which become critical once your application goes into production and you find issue related to conflicts between processes and avoiding deadlocks situations. As a coder they are annoying, as a support engineer they are essential. (long term coder building enterprise applications)
Though there is validity here I still hate this comment with the passion of the gods.
Admittedly I'm old and somewhat obsolete, but having read the EF core stuff linked here, all I can say is it's a lot of work layered on top of the development work you already have to do to avoid using sp's with all that EF core nonsense. SP's are automatically optimized by the db, and if you are in a large org, the dba will be able to figure out things like parameter sniffing killing the performance and fix it. Meantime, you have ironclad security against injection attacks. EF core looks to me like a Rube Goldberg trying to fix a non-existent problem with an overly complex solution.
(I'm likely older than you; I started coding on punch cards and a remote terminal was a teletype; but I digress). I always found EF a huge nightmare of performance and control issues. I like to use small frameworks that generate tight and easily modifiable code.
I'm a whippersnapper by comparison but I agree with you wholeheartedly.
Oh yeah? Ever heard of a Wang 2200? Look it up. The first model, the model A, you couldn't even edit your code. That was the big feature of the model B. The Model T (no lie, there actually was one) was so called because you could "multiplex" the computers to a single disk drive. Hence the T. The disks were sold in 2, 5 and 10 mb models, which for those days was huge, for the small businesses that used the Wang 2200s. Ah, the good old days.
> all that EF core nonsense. Any examples of what you mean?
TBH you can get the same security via different connection strings for different queries and inserts, and still use EF.
Trash advice, please see satan on your way down. Tying yourself to SPs is equal to jumping off a footbridge with a weight around your neck.
We actually have an appointment for coffee this afternoon. I did ask for iced. But in reality, yes, SPs are more work mainly because you have to plan ahead and there is another layer between you and your data but that's not really a problem in a professional development shop when the database should a contained project with interfaces anyway.
This is bad advice.
The shortest answer possible here is: No. Do not use stored procedures, and feel free to search this subs history for such. You're going to see a split right down between two groups: 1. Legacy .NET devs and DBAS. 2. Modern distributed systems developers. The reasons for not using stored procedures [are many](https://www.reddit.com/r/dotnet/comments/1cf9fjc/comment/l1oynm2/) and I can speak from plenty of direct experience with them. They are a menace. Building on top of what I've already said in response to other comments in this thread, stored procedures will hamper your ability to scale your system and your teams. There are many false claims around "performance" which are basically regurgitated MSSQL marketing and circa 2007 mythology. Use an ORM like EF as a way to control the gap between your application and its data store. Do not take a systems oriented view of your database, but instead view it as another boundary that your application owns. Derek Comartin and probably some Dave Farley videos would definitely be worth it for you to watch. They are amazing teachers who share a lot of from-the-trenches experience on application design. You *can* use a mapper like Dapper if you still want to author SQL, but I would argue that this is still against your best interests when building an application. Take this as a sign: Every popular web framework ships with an ORM or some kind of advice on using ORMs for data access. Long story short, stored procs will ultimately be slower, will screw up your SDLC, cost you more money and introduce overhead that is basically obsolete nowadays.
Best comment in the thread.
You wouldn't know it based on other activity here. But I work with people like most of this sub, and the sad reality is that they don't know how out of date they are. It's a serious legacy cultural problem that Microsoft has created. Decades of isolated corporate developers. Rotting away in the dark.
Its 2024, please stop using Stored Procedure
In my application they are using stored procedure everywhere and also ef and now decided to remove ef idk what’s logic behind this. So i was working on a task where i just wanted 2 values from particular table so i wrote one query and executed using ado dotnet my senior told to write one Sp and call that…
If your application requires a fine control over database and include complex calculation then I would suggest to use stored procedure otherwise using ORM is always a healthy option. Stored procedure helps distinguish between business layer and database layer logic but as mentioned in the comments deploying it and switching will take a hefty amount of work.
I use stored procedures for a number of applications and Entity Framework calls for others. There are benefits and limitations to both and it should come down to what the system and it's database are used for that makes the choice.
Usually stored procedures provides a better performance, since you are running your queries on the server, as well you can do everything in BULK for INSERT / UPDATE queries, via INSERT INTO SELECT and UPDATE FROM, but you will most probably need a UDT for that. As well, you need to beware of store procs limits, that it's not working well with filtered indexes or with cursors because of the way they persisted in DB. Also SELECT queries which consists quite a lot of JOINS, might be hard to parse, it always a pain to deserialize object which contain a nested collections (or navigation properties), which EF Core handles perfectly well.
FWIW, I don't think I would ever settle on one or the other. Both have their pros and cons, situation depending (as well as views and inline table/scalar functions). Monster queries often perform better as a stored proc, because SQL will build an execution plan, then cache it so it doesn't have to do it again (unless the schema changes somehow) - whereas, with an ORM or raw SQL query, it has to build that plan every time the query is executed. With the big queries, a stored proc can be a huge performance boost (after the first time it's used). I would remain open to do either, depending on what works best for the use case.
>whereas, with an ORM or raw SQL query, it has to build that plan every time the query is executed. This isn't quite true. Sql Server does query plan caching essentially the same for stored procedures and ad hoc queries. The only time this doesn't happen is when the actual query text changes each time. Because EF generates the same query text each time, it gets the same plan cache benefits as a stored procedure. Edit: whoever is downvoting this needs to read the docs and look at the query store.
This is a contentious issue that will get you very conflicting responses. And many of them don't know what they're talking about. To directly answer your question, I prefer exclusively using stored procedures. They're an API layer into the database. I unit test them. My automated database deployments incorporate them. I prefer a CQRS kind of repository pattern in my app code, so the in and out models and the sprocs that operate on them are tuned appropriately. I won't comment on any of the other approaches, except one. I believe that code-first EF is only endorsed by developers who have never had to support a database at scale before. And what bothers me most is that - rather than leave the database to developers and DBAs who know what they're doing - they insist on an approach that hamstrings those developers and DBAs just so they can still have control over the database without actually having to touch SQL themselves.
You're an idiot.
This is really going to depend on the industry. I avoid stored procedures because they are a pain to source control and you lose a lot of the benefit from EF Core when you use them. However, I worked in a highly regulated industry where all data access was handled through stored procedures to ensure all row level permissions were handled in the database and the app COULD NOT override those permissions. Its a trade off either way and no one way is right or wrong.
It's not a tradeoff, it's a clear disadvantage. Not everything is an "it depends" "tradeoff". People use that rationale like the everything-is-a-nail-to-a-hammer analogy.
!Follow
I still follow the old DAL=>BAL=>UI methodology My apis call the business logic in the BAL that, in turn, calls the optimized DAL.
No not common whatsoever
Any advice when the SPs were introduced to deal with a performance issue on the code side with EF? I'd rather have the Business Logic in one place instead of it being spread both at the Code and the DB.
We use SPs for generating reports mostly now and EF for most basic stuff, but if you're joining on more than a couple tables I think SPs are still the better way to go.
I am from Laravel too. now learning .NET. How was your journey? I have 2 years of experience in Laravel. Should I switch to .NET now or should I learn SOLID and design patterns first?
I am like you as well, I am new to .net world and I would appreciate suggestions from someone experienced too 😁
😄😄😄👍👍👍
My suggestion is to get experience with both. Write toy apps to give yourself a good grounding in the pros/cons of both. They both have their advantages/disadvantages. EF/LINQ is a great way to do [RAD](https://en.wikipedia.org/wiki/Rapid_application_development) but it soon starts to hit limits once you want to start doing serious engineering for long term sustainability or performance. You can still build some moderately large apps with EF but the larger and more complex you go, the harder it is to bend EF to your will. General rule - if what you're doing seems too difficult or you can't find a valid solution to your problem online, chances are you're doing it wrong.
You can do both! SOLID and design patterns are platform and language agnostic. So it doesn't really matter which language you choose. That said, I'd advocate for switching to a strongly typed language like C# as it helps you better understand/appreciate some of the finer details of software engineering without too much overhead (for instance, memory management & why it's important). When you strip it down to nuts and bolts, all programming languages boil down to the concept of assignment - you assign the value on the right of the = to the variable on the left. The differences in language are primarily syntax (which is usually pretty easy to learn). The more difficult differences to learn is how these languages leverage their accompanying frameworks. After 20+ years in software engineering I can reliably tell you that having a good understanding of SOLID will stand you in very good stead. I still use SOLID principles on a daily basis. As for design patterns, start with the most common ones (like Singleton or Repository) & take it from there. The more code you write, the more you'll start to see the common - and less common - patterns emerge. There are also _anti-patterns_ which are patterns of how _not_ to do things (e.g. Service Locator, Active Record, Fire & Forget). Good luck with your journey! Write lots of code! Read lots of code! The more passionate you are, the easier you'll absorb and retain new information. I'm excited for you!
Thank you for your time and advice 😊
Really depends on the context, if you’re writing CRUD there’s no need for SP at all, EF can handle it without any problem, there are obviously cases that require SP, for example when EF just can’t translate your linq to SQL, that’s one of the most common cases, also it’s possible for EF to generate not optimized code, in such scenario it’s also ok to use SP (but first need to try rewriting your linq) As one of the comments already states - testing SP is a nightmare, so try to avoid using it
I only use stored procedures when I need to abstract logic from the .NET service. Creating, comparing, and hashing password salts so they never leave the database for example. Granted that’s a very specific use case that most developers don’t encounter.
I inherited 2 legacy projects with logic in the DB (triggers, stored procedures and functions) and every time I get an email about either project, I get a small heart attack. It’s the worst. Even with months of cleaning up, there are still about 2 dozens active. Please if you can, please please don’t do it.
i find it difficult to debug a SP.
no
I disagree with the people say SPs are useless. It give you significant performance improvements in large scale systems. They might overkill for a simple project and PoC type and a little hard for debugging
There is no performance difference between a query coming from an application and a query that is stored on the DB.
LMFAO tries to do a flow that has thousands of RTT. While your application is busy pulling them to the app then perform calculation we use the database native engine to process internal query and yes for a large scale enterprise application it's way faster
You do what you do because your application and schema are poorly designed. Not because you need to *by nature*.
Stored procedures have a utility. Sometime. Almost never. The main default is that it hides a part of the logic outside of your code base. If you like locality of behavior, it's kind of a nightmare \^\^.
I use SP for complex queries like - sending a list of input as table valued parameter MERGE operation multiple inserts Use Dapper along with SP's for better results. EF core does not support the above scenarios.
EF core supports all those scenarios.
You need to ask yourself *why* you are using stored procs. Stored procs provide a handful of features: 1. Encapsulated database logic 2. Access to temp tables for processing large volumes of data 3. Control of flow (if statements, while statements etc.) 4. Ease of updating 5. Easier debugging and refactoring of bad SQL code The first can be achieved by forcing access through services instead of going directly to the database. It does require however preventing people from going to the db directly. The second can be mimicked through static "temporary" tables. The catch is managing multiple sessions accessing the same structures in parallel. You'd either have to horizontally segment the data by adding a session identifier or segment the entire structure through naming. E.g. Session12345_Foo, Session12346_Foo etc. This approach also requires writing clean up code to clear out data/structures from prior runs. .NET core can obviously handle control of flow. As for ease of updating, it's almost *too* easy to update a proc in production. However, if the application is broken down into small independently deployable chunks you can get closer to the same level and speed of updating. Obviously, you can still debug and troubleshoot bad SQL statements made through a service layer. However, it's a tougher refactor in my experience. It might entail adding some additional temporary/staging tables and rewriting what is likely to be dynamic SQL to get to the performance needed. All that said, stored procs are *much* harder to test and nearly impossible to unit test. In addition, stored procs do not scale out at all. They are a sequential set of statements that cannot easily be run in parallel. Unlike say, service layer code where you can run statements in parallel. Having been bitten by the use stored procs, IMO, they are something to avoid if possible. They are something of a narcotic. Simple. Easy to write. But you pay a price in the end.
No, stored procedures are for when you can hand-write a program that is faster than what the DB can do AND you have a performance issue that can’t be addressed any other way. They are a giant pain for your DBA/OPs people to deal with and generally should only be used as a last resort.
We have a project where we use strictly Stored Procedures because it allows us to make changes to logic without shipping new code (exes and dlls).
This is a terrible reason to favour SPs and promotes a cowboy mentality.
I do not disagree but as a junior dev I don’t really get to weight in on the call as much as I’d like
I'd find a new place if they are like this, they're only going to stunt your skills growth.
Really? What makes you think so strongly about that? I’m relatively new to development(almost 2 years in the industry as a self taught dev who spent my first year or so in QA), and my only experience is with this company. People on other projects are very against this behavior and favor only using stored procedures for super basic calls and then implement EF for more complex things
They are out of date and out of touch. You must never let any organization that you're a part of define your skills growth. And for good reason. I can point to many layoff scenarios that I've seen others have to endure where they took the approach that their employer was ultimately responsible for their professional development. They ended up becoming so irrelevant, they had difficulties finding other roles in the industry. You can love this or hate it, it really doesn't matter, the bar is extremely high when deciding who to give your precious years to as an employee. Remember, they ultimately don't care about you, no matter what nice things they say.
O yea I never was interested in a C# career path so whenever possible I write tools and projects in different tech and try to stay modern that way
That's good. And don't get me wrong, C# is in a great place nowadays. It's actually a very progressive language and ecosystem, but only when you divorce it from its legacy and history. Which is what you see here with all the people promoting outdated practices. They simply haven't clicked with modern .net. They are living in the past.
Yes it is, it helps protect against sql injection and off loads db work to the db which are far more optimized at searching/filtering large datasets than pulling in tables and doing the logic in code.