Stored Procedures & Direct SQL

Jul 9, 2014 at 8:50 PM
First I want to say I have seen the "No Support" bit in the FAQ, but do read this (admittedly lengthly) description of a scenario and possible idea.

I've managed to get Effort.EF6 up and running with my Entity Framework context setup for unit testing and loading CSV data and so far it's working fantastically.

One feature that is needed fo rthis project is a hybrid approach to data access. For example, the simple data retrieval and marshalling of objects is left to entity framework. However, some of the more complicated queries that can be performance heavy are done through stored procedures as LINQ is not able to generate the most efficient SQL we would like.

As an example, we have a search system that allows you to find a person in the system using several different search criteria and filters. Previously, when solely using EF, the SQL that was generated was extremely difficult to read and created more joins than were necessary. As a result, the query would often take a long time to execute or even time out completely. So we instead write an optimised stored procedure that returns the IDs of the records we want based on the search criteria and then EF loads the entities from this collection of IDs.

This logic is hidden with the data access layer in repositories, so the call does not know if an SP is called or not, as it does not need to.

I know on the FAQ you mention no support for SPs, but entity framework does now support them, and I see from the NMemory source, that they can be created. Are there plans to have some kind of support? The thing that struck me is that unless you use CodeFirst SPs you cannot generate the LINQ for the SPs, and you definitely couldn't have custom SPs like I've described. However, you could write that ini LINQ if purely using NMemory under the hood.

As I see this tool as more likely a testing tool than a production item, do you think adding some way of being able to add stored procedures to a database instance. My thoughts would be to specify the text (name) of the SP as a key for executing some sort of delegate, which either runs LINQ on the entities themselves or does some other kind of logic.

Currently I can't see a way to do this as all of the NMemory stuff is hidden inside an internal layer of the library.

Sep 8, 2014 at 7:16 PM

Sorry for responding so late.

My main problem is that (as far as i know) the EDMX or the DbContext do not know about implementation of a stored procedure. This follows that using such functionality wouldn't be nearly as convenient as using the pure Linq to Entity or ESQL interface because you would have to define each sp separately for Effort. Currently I have no idea how could I push such queries down to the Effort engine :(.

I personally would recommend to create an implementation for the DAL repo component that uses pure Linq to achieve the same query as the sp. This implementation would be injected during testing.

Oct 13, 2016 at 9:21 PM
Edited Oct 13, 2016 at 9:24 PM

I can appreciate that your code would not know how to "do" all of the steps within the stored proc. But couldn't you create a stub that would allow the user to define what comes back to fake the response, not actually execute code?

IDataLoader loader = new SomeMagicLoaderToPrePopulateMyStoredProcResponses();
using (MyEntities ctx = Effort.ObjectContextFactory.CreateTransient<MyEntities>(loader))
    List<usp_MyStoredProc_Response> response = ctx.usp_MyStoredProc(a, b, c);
The variables A, B and C would be ignored, but there because the call to MyStoredProc requires them. However, the loader has already PRE-DEFINED the data that will come back on this call so that we can mock-out what might happen in specific scenarios.

Just a thought. Is this even possible?