Create stored procedure on Effort database for Unit Test

Question

I have a function calling a stored procedure with Entity Framework :

public async Task<List<Entity>> GetEntity(int id)
{
       var param = new SqlParameter("@id", id);
       return await myContext.Database
           .SqlQuery<MyEntity>("[myStoredProcedure] @id", param)
           .ToListAsync();
}

And i would like to create a unit test for it using Effort. I already have Effort (and NMemory database) to simulate a database (based of my context), on Initialize for each Unit Test, like :

[TestInitialize]
public void Initialize()
{
     Effort.Provider.EffortProviderConfiguration.RegisterProvider();
     EffortProviderFactory.ResetDb()
     using (var context = new MyContext("PWET"))
     {
          context.Database.CreateIfNotExists();
          context.Constructeurs.Add(new Constructeur { Nom = "Zebra" });
          context.Constructeurs.Add(new Constructeur { Nom = "Joya" });
          context.SaveChanges();
     }
}

Where EffortProviderFactory is :

public class EffortProviderFactory : IDbConnectionFactory
{
    private static DbConnection _connection;
    private readonly static object _lock = new object();

    public static void ResetDb(){
        lock (_lock){
            _connection = null;
        }
    }
    public DbConnection CreateConnection(string nameOrConnectionString)
    {
        lock (_lock){
            if (_connection == null)
                _connection = Effort.DbConnectionFactory.CreateTransient();
            return _connection;
        }
    }
}

I tested adding the stored procedure creation like that :

[TestInitialize]
public void Initialize()
{
     Effort.Provider.EffortProviderConfiguration.RegisterProvider();
     EffortProviderFactory.ResetDb()
     using (var context = new MyContext("PWET"))
     {
          context.Database.CreateIfNotExists();
          context.Database.ExecuteSqlCommand(@"
CREATE PROCEDURE [dbo].[myStoredProcedure]
@id INT = 0
AS
BEGIN

SELECT foo
FROM bar 
WHERE foo.Id = @id

ORDER BY foo.Id;
END");
     }
}

But it throws an NotSupportedException. How can i do, and what is the best way ?

Answer 1

Effort is a file-based in-memory database provider that supplies a DbContext instance with a privately owned temporary database: new context, new database, no test interactions. That's the good part.

The downside, of course, is that it isn't --and never will be-- a full-fledged database engine. Therefore it will never support stored procedures written in any of the common SQL dialects (like t-SQL or PL-SQL). As far as Effort (i.e. NMemory) has stored procedures, it's merely a stored IQueryable, as is evident from the StoredProcedure constructor. Noting remotely related to t-SQL stored procedures.

The only way to test stored procedures in your data access layer code, which is a very good idea, is to write integration tests. There are roughly two approaches to making integration tests independent of one another:

• Create/seed a new database for each test

• Use an existing database with test cases and roll back changes after each test, for example using TransactionScope.

Integration tests are never going to be as fast as unit tests and they only complement unit tests, but nevertheless, in my own coding practice related to data layers they've become first-class citizens in the test suite. For me, correctness is more important than speed.