CODEWITHSUNDEEP
c#asp.net-mvcunit-testingmocking
Fábio
Fábio

Reputation: 89

How do I Unit test a sql view using SQLite

I am using linq to nhibernate on my project, and I'm writing unit tests using Mock and SQLLite. The domain object is mapped to the sql view using fluent nhibernate.

The problem is that this domain object uses the sql view has source, and this view has data from many tables, and only MsSQL knows that. I don't know how to Mock this.

How do I Unit test a sql view using Mock and SQLite?

»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»

EDIT:

Sorry, my question wasn't very clear. Let me rephrase it.

I'm using nhibernate, and writing unit tests using SQLLite. In some cases my domain objects are mapped to views instead of tables, and so I have configured the fluent nhibernate mappings so that the corresponding tables are not generated in the SQLite database schema.

This works in the development environment because the views are created manually afterwards in a MsSQL database, but I can't test the corresponding repositories in unit tests because they use SQLite.

How do I Unit test a sql view using SQLite?

Upvotes: 1

Views: 3349

Answers (3)

KarlZ
KarlZ

Reputation: 170

Headline: I use the following pattern so that I can pass a context (auto generated thanks to EF Core Power Tools) into my repository and test that logic. In my context I have:

public virtual DbSet<MyDomainObject> MyDomainObjectDbSet { get; set; }
public IQueryable<MyDomainObject> MyDomainObjects => MyDomainObjectDbSet.AsQueryable();

I use the tests to test the business/data access logic, not the persistence tier. In-memory Sqlite has been great for that when working with Tables. I can populate the table with data. But you cannot add records to a view (that I've found). In a table it's easy to do that and then call .SaveChangesAsync(). Sqlite is a database. Now in my unit test I use a Mock, something like this:

[Theory, AutoMoq] // Using AutoFixture and xUnit
public void Test(Mock<IMyContext> context)
{
    // Setup
    context.Setup(c => c.MyDomainObjects).Returns(_domainObjects); // Just a queryable of MyDomainObject
    var repository = new MyRepository(context.Object);

    // Act
    var actual = repository.SomethingWithLogic();

    // Assert - an appropriate assert.
}

In this way I could test my business logic that uses a View. I did run a unit test that runs locally against an SQL Server database that verified MyDomainObjects does return all the items in MyDomainObjectDbSet.

Here is _domainObjects:

IQueryable<MyDomainObject> _domainObjects = new List<MyDomainObject>
    {
        new MyDomainObject { Property1 = "Item1" },
        new MyDomainObject { Property1 = "Item2" }
    }.AsQueryable();

Upvotes: 1

F&#225;bio
F&#225;bio

Reputation: 89

I've found a solution.

I've changed the mappings, so that in unit testing it generates the mapping for the domain object, creating a table in the SQLite instead of a view. With this I can create repositories of that object in the testing environment.

Upvotes: 0

duffymo
duffymo

Reputation: 308998

I've never understood people who want to test their persistence tier and ask how to mock it. What's the point of testing the database if you eliminate it from the test?

If you're trying to test a database, don't mock it. Once you've done that to your satisfaction, and you want to move on to services that use the database, then I think it's appropriate to mock the database (because you've already tested it).

With that said, there are special considerations for database testing:

  1. Making sure that you have the test data you need present.
  2. Keeping real data safe.
  3. Having the test database in a same state before and after the tests are run.

Upvotes: 7

Related Questions