C#: Testing Entity Framework FromSql to ensure proper syntax

@ivan-stoev has answered your question as to why your '.FromSql' statement does nothing - i.e. the query is never actually materialized. But to try and add some additional value, i'll share my Unit Test setup as it works well for me. Of course, YMMV.

1. Create a reusable class to handle generic In-memory database creation and easy population of tables with test data. NB: this requires the Nuget packages:

• ServiceStack.OrmLite.Core
• ServiceStack.OrmLite.Sqlite

I am using OrmLite as it allows for mocking and unit testing by providing a non-disposing connection factory which I can neatly inject into the Test classes via Dependency Injection:

/// <summary>
    /// It is not possible to directly mock the Dapper commands i'm using to query the underlying database. There is a Nuget package called Moq.Dapper, but this approach doesnt need it.
    /// It is not possible to mock In-Memory properties of a .NET Core DbContext such as the IDbConnection - i.e. the bit we actually want for Dapper queries.
    /// for this reason, we need to use a different In-Memory database and load entities into it to query. Approach as per: https://mikhail.io/2016/02/unit-testing-dapper-repositories/
    /// </summary>
    public class TestInMemoryDatabase
    {
        private readonly OrmLiteConnectionFactory dbFactory =
            new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);

        public IDbConnection OpenConnection() => this.dbFactory.OpenDbConnection();

        public void Insert<T>(IEnumerable<T> items)
        {
            using (var db = this.OpenConnection())
            {
                db.CreateTableIfNotExist<T>();
                foreach (var item in items)
                {
                    db.Insert(item);
                }
            }
        }
    }

2. A 'DbConnectionManager<EFContext>' class to provide the wrapper to the database connection using the EF Context you will already have created. This grabs the database connection from the EF Context and abstracts away the opening/closing operations:

public class DbConnectionManager<TContext> : IDbConnectionManager<TContext>, IDisposable
            where TContext : DbContext
        {
            private TContext _context;
    
            public DbConnectionManager(TContext context)
            {
                _context = context;
            }
    
            public async Task<IDbConnection> GetDbConnectionFromContextAsync()
            {
                var dbConnection = _context.Database.GetDbConnection();
    
                if (dbConnection.State.Equals(ConnectionState.Closed))
                {
                    await dbConnection.OpenAsync();
                }
    
                return dbConnection;
            }
    
            public void Dispose()
            {
                var dbConnection = _context.Database.GetDbConnection();
    
                if (dbConnection.State.Equals(ConnectionState.Open))
                {
                    dbConnection.Close();
                }
            }
        } 

Accompanying injectable Interface for the above:

public interface IDbConnectionManager<TContext>
        where TContext : DbContext
    {
        Task<IDbConnection> GetDbConnectionFromContextAsync();

        void Dispose();
    }

3. In your .NET Project Startup class, register this interface with the inbuilt DI container (or whatever one you're using):

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped(typeof(IDbConnectionManager<>), typeof(DbConnectionManager<>));
}

4. Now our Unit Test class looks like this:

/// <summary>
    /// All tests to follow the naming convention: MethodName_StateUnderTest_ExpectedBehaviour
    /// </summary>
    [ExcludeFromCodeCoverage]
    public class ProductTests
    {
        //private static Mock<ILoggerAdapter<Db2DbViewAccess>> _logger;
        //private static Mock<IOptions<AppSettings>> _configuration;
        private readonly Mock<IDbConnectionManager<Db2Context>> _dbConnection;

        private readonly List<Product> _listProducts = new List<Product>
        {
            new Product
            {
                Id = 1,
                Name = "Product1"
            },
            new Product
            {
                Id = 2,
                Name = "Product2"
            },
            new Product
            {
                Id = 3,
                Name = "Product3"
            },
        };

        public ProductTests()
        {
            //_logger = new Mock<ILoggerAdapter<Db2DbViewAccess>>();
            //_configuration = new Mock<IOptions<AppSettings>>();
            _dbConnection = new Mock<IDbConnectionManager<Db2Context>>();
        }

        [Fact]
        public async Task GetProductAsync_ResultsFound_ReturnListOfAllProducts()
        {
            // Arrange
            // Using a SQL Lite in-memory database to test the DbContext. 
            var testInMemoryDatabase = new TestInMemoryDatabase();
            testInMemoryDatabase.Insert(_listProducts);

            _dbConnection.Setup(c => c.GetDbConnectionFromContextAsync())
                .ReturnsAsync(testInMemoryDatabase.OpenConnection());

            //_configuration.Setup(x => x.Value).Returns(appSettings);

            var productAccess = new ProductAccess(_configuration.Object); //, _logger.Object, _dbConnection.Object);

            // Act
            var result = await productAccess.GetProductAsync("SELECT * FROM Product");

            // Assert
            result.Count.Should().Equals(_listProducts.Count);
        }
    }

Notes on the above:

• You can see i'm testing a 'ProductAccess' Data Access class which wraps my database calls but that should be easy enough to change for your setup. My ProductAccess class is expecting other services such as logging and Configuration to be injected in, but i have commented these out for this minimal example.
• Note the setup of the in-memory database and populating it with your test list of entities to query is now a simple 2 lines (you could even do this just once in the Test class constructor if you want the same test dataset to use across tests):

var testInMemoryDatabase = new TestInMemoryDatabase(); testInMemoryDatabase.Insert(_listProducts);