In an ASP.NET Core MVC web application, I need to test my database, so I have created a project that contains the DbContext. Now I need to test this database (DbContext), although I have read some articles that states that testing is better to be performed using an in-memory database.

But I need to use my actual database because it has lots of data that will be difficult to seed in the unit test.

To test the database, I have written this test class:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using WERP.DataAccess.Data;

namespace TestDbContext
{
    [TestClass]
    public class UnitTest1
    {
        const string CONN_STRING =
            @"Server=.\SQLEXPRESS;Database=WERP;Trusted_Connection=True;TrustServerCertificate=True";

        private ApplicationDbContext _dbContext;

        [TestInitialize]
        public void Setup()
        {
            var serviceProvider = new ServiceCollection()
                .AddEntityFrameworkSqlServer()
                .BuildServiceProvider();

            var builder =
                new DbContextOptionsBuilder<ApplicationDbContext>();

            builder.UseSqlServer(CONN_STRING).UseInternalServiceProvider(serviceProvider);
            _dbContext = new ApplicationDbContext(builder.Options);
        }

        [TestMethod]
        public void TestMethod1()
        {
            Assert.IsNotNull(_dbContext); // ok
            Assert.IsNotNull(_dbContext.Currencies); //ok
            Assert.IsNotNull(_dbContext.Currencies.FirstOrDefault()); //Instance Failure error
        }
    }
}

But I am getting an error in the unit test result which is “Instance Failure”. What am I doing wrong? And how to fix that, to be able to test the database?

The Error Stack Trace:

TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
SqlInternalConnectionTds.ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
<12 more frames...>
<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
Enumerator.MoveNext()
Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
lambda_method171(Closure, QueryContext)
QueryCompiler.Execute[TResult](Expression query)
EntityQueryProvider.Execute[TResult](Expression expression)
UnitTest1.TestMethod1() line 34
RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)