Possible to use the Repository Pattern + Stored Procedures ? Should / can they return IQueryable?

Question

Hi folks,

I'm a big fan of using the Repository pattern to return IQueryable<T> objects. I then let my services layer determine what does what (eg. filter by XXX, order by YYY, project into ABCD, etc).

But I've got some hardcore DB stuff, so I've got it all wrapped up into a Stored Procedure. Works fine. I know EF can execute stored procs .. but I'm not sure how this fits into a Repository Pattern data layer.

Does anyone have any examples / suggestions? Do i let the repository method execute the stored procedure, and then i return the result (eg. ICollection<Foo> as AsQueryable .. so the service layer then just queries on that result?

Answer 1

I would suggest going ahead and doing just that. Setup the stored procedure in the EF model, returning whichever entity it needs to, then in your repository create a get that addresses what the stored procedure is using. The difference in how the data is returned is occurring inside your DAL (in this case the EF model), your repository is still accessing a context to find and return data.

I am doing something similar right now and that was the best solution I could come up with. It allowed me to continue doing my data access at the EF Model and keep my repository separated from the way the model is collecting the data. The repository is still clueless about how the model gets the data and the rest of the app won't see anything different about the repository functions.