How to call SQL functions / stored procedure when using the Repository pattern

Question

What is the best way to call a SQL function / stored procedure when converting code to use the repository pattern? Specifically, I am interested in read/query capabilities.

Options

1. Add an ExecuteSqlQuery to IRepository
2. Add a new repository interface specific to the context (i.e. ILocationRepository) and add resource specific methods
3. Add a special "repository" for all the random stored procedures until they are all converted
4. Don't. Just convert the stored procedures to code and place the logic in the service layer

Option #4 does seem to be the best long term solution, but it's also going to take a lot more time and I was hoping to push this until a future phase.

Which option (above or otherwise) would be "best"?

NOTE: my architecture is based on ardalis/CleanArchitecture using ardalis/Specification, though I'm open to all suggestions.

Answer 1

Don't treat STORED PROCEDURES as 2nd order citizens. In general, avoid using them because they very often take away your domain code and hide it inside database, but sometimes due to performance reasons, they are your only choice. In this case, you should use option 2 and treat them same as some simple database fetch.

Option 1 is really bad because you will soon have tons of SQL in places you don't want (Application Service) and it will prevent portability to another storage media.

Option 3 is unnecessary, stored procedures are no worse than simple Entity Framework Core database access requests.

Option 4 is the reason why you cannot always avoid stored procedures. Sometimes trying to query stuff in application service/repositories will create very big performance issues. That's when, and only when, you should step in with stored procedures.

Answer 2

https://github.com/ardalis/CleanArchitecture/issues/291

If necessary, or create logically grouped Query services/classes for that purpose. It depends a bit on the functionality of the SPROC how I would do it. Repositories should be just simple CRUD, at most with a specification to help shape the result. More complex operations that span many entities and/or aggregates should not be added to repositories but modeled as separate Query objects or services. Makes it easier to follow SOLID that way, especially SRP and OCP (and ISP) since you're not constantly adding to your repo interfaces/implementations.