How do I Unit test a sql view using SQLite

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.

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();