Mocking EF's ExecuteSqlCommand using NUnit

Question

I'm using Entity framework core. I was referring to one of the code suggested here: https://stackoverflow.com/a/41515103/9766215

public void SaveOrUpdate(MyEntity entity)
{
    var sql =  @"MERGE INTO MyEntity
                USING 
                (
                   SELECT   @id as Id
                            @myField AS MyField
                ) AS entity
                ON  MyEntity.Id = entity.Id
                WHEN MATCHED THEN
                    UPDATE 
                    SET     Id = @id
                            MyField = @myField
                WHEN NOT MATCHED THEN
                    INSERT (Id, MyField)
                    VALUES (@Id, @myField);"

    object[] parameters = {
        new SqlParameter("@id", entity.Id),
        new SqlParameter("@myField", entity.myField)
    };
    context.Database.ExecuteSqlCommand(sql, parameters);
}

I need to mock ExecuteSqlCommand but this extension method is not allowed to be overridden by Moq setup. I don't want to mock the whole method SaveOrUpdate. Please suggest if there's any way to mock or setup it for unit testing using any Database context methods. I'm using UseInMemoryDatabase for unit testing database context.

Answer 1

You will need to mock your method not the EF core implementation. You can make your mehtod virtual and mock it's implementation with Moq:

public virtual void SaveOrUpdate(MyEntity entity)
{
    var sql =  @"MERGE INTO MyEntity
                USING 
                (
                   SELECT   @id as Id
                            @myField AS MyField
                ) AS entity
                ON  MyEntity.Id = entity.Id
                WHEN MATCHED THEN
                    UPDATE 
                    SET     Id = @id
                            MyField = @myField
                WHEN NOT MATCHED THEN
                    INSERT (Id, MyField)
                    VALUES (@Id, @myField);"

    object[] parameters = {
        new SqlParameter("@id", entity.Id),
        new SqlParameter("@myField", entity.myField)
    };
    context.Database.ExecuteSqlCommand(sql, parameters);
}

and then mock the implementation of this method.

Have a look at the following posts :

https://entityframework.net/knowledge-base/26014969/how-to-moq-entity-framework-sqlquery-calls

How to Moq Entity Framework SqlQuery calls

Answer 2

I really like the suggestion here:Moq Entity Frameworks ExecuteSQLCommand. That is not really mocking the entire function but the ExecuteSqlCommand, by adding a wrapper in your context:

So in your context class

public class MyContext : DbContext
{
    public virtual int ExecuteSqlCommand(string sql, params object[] parameters)
    {
        return Database.ExecuteSqlCommand(sql, parameters);
    }
    public virtual int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
    {
        return Database.ExecuteSqlCommand(transactionalBehavior, sql, parameters);
    }

    // The rest of the implementation

So now you can mock the context, and since the functions are vitrual, you can mock them too. You will only need to change your code like this:

public void SaveOrUpdate(MyEntity entity)
{
    var sql =  @"MERGE INTO MyEntity
                USING 
                (
                   SELECT   @id as Id
                            @myField AS MyField
                ) AS entity
                ON  MyEntity.Id = entity.Id
                WHEN MATCHED THEN
                    UPDATE 
                    SET     Id = @id
                            MyField = @myField
                WHEN NOT MATCHED THEN
                    INSERT (Id, MyField)
                    VALUES (@Id, @myField);"

    object[] parameters = {
        new SqlParameter("@id", entity.Id),
        new SqlParameter("@myField", entity.myField)
    };
    context.ExecuteSqlCommand(sql, parameters);
}