How to access real DataBase in unit testing?

Question

I want to get data from my actual database in unit testing. I have a table named person and I have data in that table. Now, I want to get data from that table in unit testing. Below is the code which I am using to fetch data from the DB.

public class PersonAppService_Tests: AppTestBase
{
private readonly IRepository<Person> _PersonRepositroy;
private readonly IUnitOfWorkManager _unitOfWorkManager;

public PersonAppService_Tests()
        {
            
            _PersonRepositroy = Resolve<IRepository<Person>>();
            _unitOfWorkManager = Resolve<IUnitOfWorkManager>();

        }
public async Task<List<PersonListDto>> GetAll()
        {
           List<PersonListDto> a = new List<PersonListDto>();
            using (var uow = _unitOfWorkManager.Begin())
            {
                var test = from Person in _PersonRepositroy.GetAll()
                           select new PersonListDto
                           {
                               Id = Person.Id,
                               Text = Person.Text,
                               Code = Person.Code,                               
                               TenantId = Person.TenantId
                           };
                a = test.ToList();//No Data is apppearing, event though database has the data
                await uow.CompleteAsync();
            }

            
        }

}

using this code I get null data as I have data in my DB.

I am using asp.net Core (3.0) +Entity Framework 5.0

Answer 1

I am using in-memory Db but for some scenarios I want to access the real Db is it possible to access real Db in unit testing?

Generally, we are not suggesting to test actual database queries or updates with the Unit Test. Instead we use the EF in-memory database when unit testing something that uses DbContext. In this case using the EF in-memory database is appropriate because the test is not dependent on database behavior.

If you still want to use the actual database, you could refer to the following code to use the same connection string in the Unit Test application and the actual application.

public class UnitTest1: IDisposable
{
    BlogDBContext _context;
    private PostRepository repository;
    public static DbContextOptions<BlogDBContext> dbContextOptions { get; }
    //using the same connection string
    public static string connectionString = $"Server=(localdb)\\mssqllocaldb;Database=UnitTestBlogDB;Trusted_Connection=True;MultipleActiveResultSets=true";

    public UnitTest1()
    {
        var serviceProvider = new ServiceCollection()
            .AddEntityFrameworkSqlServer()
            .BuildServiceProvider();

        var builder = new DbContextOptionsBuilder<BlogDBContext>(); 
        builder.UseSqlServer(connectionString)
                .UseInternalServiceProvider(serviceProvider); 
        _context = new BlogDBContext(builder.Options); 

        repository = new PostRepository(_context);

    }
    [Fact]
    public void Test1()
    { 
        var data1 = repository.GetCategories().Result;

        var data = _context.Category.ToList();

    }

The result like this: