Run unit test with memory database and fromsqlraw is giving error

Question

I’m trying to build a unit test with . Net Core 3.1 and using Xunit. It turns out I still don’t have a database and so I use the UseInMemoryDatabase. Turns out when I make the call from PROC, I get this mistake:

Unhandled method: Fromsqlonqueryable

I would like to know how I do to test proc with inmemory bank. I could not replace the FromSqlRaw for something that works. Check out my code as it is:

public IDeadlineCardGateway CreateRepository()
{
    var logger = Mock.Of<ILogger<DeadlineCardRepository>>();
    var serviceProvider = new ServiceCollection()
        .AddEntityFrameworkInMemoryDatabase()
        .BuildServiceProvider();
    
    var builder = new DbContextOptionsBuilder<DBContext>()
       .UseInMemoryDatabase(databaseName: "LogisticaDB")
       .UseInternalServiceProvider(serviceProvider);

    _dBContext = new DBContext(builder.Options);

    return new DeadlineCardRepository(logger, null, _dBContext);
}

and here’s the test method

[Fact(DisplayName = "GetDeadlineCard: Calculation of working days")]
public async void GetDeadlineCard_ValidCEP()
{
    const string CEP_INICIAL = "23000980";
    const string CEP_FINAL = "99999999";
    const int COD_PROD = 9;
    var repository = CreateRepository();
    var expectedResult = new DeadlineCard(
        "Teste", 
        CEP_INICIAL, 
        CEP_FINAL, 
        COD_PROD
    );

    _dBContext
        .Set<DeadlineCard>()
        .FromSqlRaw($"[dbo].[SPEG8160_CALCULAR_PRAZO_ENTREGA] {CEP_INICIAL}, {COD_PROD}", 
        expectedResult);

    var result = await repository
        .GetDeadlineCard(CEP_INICIAL, COD_PROD.ToString());
        
    Assert.Equal(expectedResult.Prazo, result.Prazo);
}

The error happens on this line:

var result = await repository.GetDeadlineCard(CEP_INICIAL, COD_PROD.ToString());

Does anyone know how I fix it?

I saw that the error is happening in the class to be tested and not in the test class. Below the class:

public class DeadlineCardRepository : RepositoryBase<DeadlineCard, int>, IDeadlineCardGateway    
{
    private readonly ILogger<DeadlineCardRepository> log;
    private readonly DBContext dBContext;

    public DeadlineCardRepository(ILogger<DeadlineCardRepository> log, IMapper mapper, DBContext dBContext) : base(log, dBContext)
    {
        this.dBContext = dBContext;
        this.log = log;
    }
    public async Task<DeadlineCard> GetDeadlineCard(string cep_inicial, string codigo_produto)
    {
        //log.LogDebug("Method DBRepository GetDeadlineCard called");
                
        var prazo = await dBContext.Set<DeadlineCard>().FromSqlRaw("SPEG8160_CALCULAR_PRAZO_ENTREGA {0}, {1}", cep_inicial, codigo_produto)
            .ToListAsync();
        
        if (prazo == null)
            return null;

        return  prazo.FirstOrDefault();

    }
}

Answer 1

The same problem contained in the OS question^En Entity Framework Core Fromsqlraw mock test cases, reports through the link that the in-memory database does not support pure SQL execution and the solution would be to use a local database for testing.

In another question from the OS^En Raw sql with EF Core and in-memory db Provider says: the in-memory database is a non-relational Nosql database and therefore does not accept pure SQL execution.

Finally your tests should be done in a local database that accepts pure SQL for unit tests, and when building this type of approach always use the SqlParameter as an example just below:

Stored Procedure:

CREATE PROCEDURE SP_PEOPLES_CREATE 
    @Name VARCHAR(50)
AS
BEGIN   
    SET NOCOUNT ON;
    INSERT INTO Peoples (Name) VALUES (@Name);
    SELECT * FROM Peoples WHERE Id = (SELECT CAST(SCOPE_IDENTITY() AS INT));
END
GO

Code:

Class:

public class People
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Method with Stored Procedure:

public People SPPeopleCreate(People p)
{
    var name = new SqlParameter("Name", p.Name); 
    var result = Set<People>().FromSqlRaw("SP_PEOPLES_CREATE @Name", name).ToList();
    if (result != null && result.Count == 1) return result[0];
    return null;
}

that is, never pass directly in your SQL may have problems Injection of SQL.

Maybe one solution you can follow is to use Sqlite in memory, example of the configuration:

public class SqliteInMemoryItemsControllerTest : 
    ItemsControllerTest, IDisposable
{
    private readonly DbConnection _connection;

    public SqliteInMemoryItemsControllerTest()
        : base(
            new DbContextOptionsBuilder<ItemsContext>()
                .UseSqlite(CreateInMemoryDatabase())
                .Options)
    {
        _connection = RelationalOptionsExtension.Extract(ContextOptions).Connection;
    }

    private static DbConnection CreateInMemoryDatabase()
    {
        var connection = new SqliteConnection("Filename=:memory:");

        connection.Open();
        
        return connection;
    }

    public void Dispose() => _connection.Dispose();
}

^{Source: Using Sqlite to test an EF Core application}