Multiple property Where clause not working with WebApplicationFactory and InMemory database

[justinkauai]

If I use the following Query statement Query.Where(x => x.PropOne == propOne && x.PropTwo == propTwo); I get a null result when using WebApplicationFactory in my tests when the table has a row satisfying the where clause.

If I use FirstOrDefault(Expression<Func<T, bool>> predicate) directly from EF core with the same predicate FirstOrDefault(x => x.PropOne == propOne && x.PropTwo == propTwo) I get the result I expect.

The issue is only isolated to using WebApplicationFactory and a inMemory database. The specification where clause works in the my App(InMemory and Sql). Or in my tests when I switch the WebApplicationFactory to use a SQL database.

[fiseni]

That's a strange one. While testing directly from EF Core, can you test it this way:

Where(x => x.PropOne == propOne && x.PropTwo == propTwo).FirstOrDefault()

This is what happens in our evaluation. Do you get the expected result in this way? For the Where we're not doing anything special in this package, it's quite simple implementation. We just store the expression, for an example

Expression<Func<T, bool>> myExpression = x => x.PropOne == propOne && x.PropTwo == propTwo;

and then during the evaluation we do the following:

dbContext.Set<T>().AsQueryable().Where(myExpression).ToListAsync();

The only difference is that you're evaluating the expression under FirstOrDefault, and in our case it happens under Where.

Quite frankly it seems like some subtle bug with InMemory. I just noticed we're using EF Core 3.1.4, we should update that to the latest version.

[justinkauai]

Thanks for the quick response!

Directly from EFCore Where(x => x.PropOne == propOne && x.PropTwo == propTwo).FirstOrDefault() and FirstOrDefault(x => x.PropOne == propOne && x.PropTwo == propTwo) produce the same result yielding the correct row.

I have tested the multiple where clause functionality without a database similar to the tests below https://github.com/ardalis/Specification/blob/master/ArdalisSpecification/tests/Ardalis.Specification.UnitTests/SpecificationEvaluatorGetQuery.cs

and it seems to be working correctly.

Its only when the inputQuery in the evaluator.GetQuery is from a InMemory db that I encounter the no results issue.

I am on 3.1.5.

[fiseni]

Oh ok, when you said it's working correctly when you test directly from EF Core, I assumed you're not using Specifications at all in that case. What I meant is if you test with InMemory for the following cases, do you have any difference:

dbContext.MyDbSet.Where(x => x.PropOne == propOne && x.PropTwo == propTwo).FirstOrDefault();
dbContext.MyDbSet.FirstOrDefault(x => x.PropOne == propOne && x.PropTwo == propTwo);

Anyhow it's interesting case, and we'll take a look into this.

Thanks for raising the issue.

[justinkauai]

Sorry I read your reply too fast.

See above.

I am not seeing any difference in putting the predicate in the Where vs FirstOrDefault working directly with EFCore InMemory.

Seems isolated to Specification and InMemoryDb.

Thanks for taking a look! Really like how the Specification library decouples ef from my domain core!

[fiseni]

Thank you @justinkauai

Well, the only difference I see (code wise), is the AsQueryable() casting. We should test this on different SDKs, right now we're on .NET Standard 2.0. We might update the EntityFramework package to .NET Standard 2.1 or Core, but we should analyze if we're affecting anyone with that.

[fiseni]

It seems this has to do with "In-Memory DB" limitations. We knew the limitations for Include, and that's why we're using real DB in our integrations tests. But there are much more limitations as well, and it seems doesn't behave well on top of IQueryable. Shortly said, the following statements in some cases might yield unexpected results.

dbContext.MyDbSet.Where(x => x.PropOne == propOne && x.PropTwo == propTwo);
dbContext.MyDbSet.AsQueryable().Where(x => x.PropOne == propOne && x.PropTwo == propTwo);

The whole concept of this package revolves around the concept of IQueryable, so unfortunately we won't be able to get rid of that. I personally, always tend to use real DB for my tests.

https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/#approach-3-the-ef-core-in-memory-database

[justinkauai]

humm in my test i am getting a result for dbContext.MyDbSet.AsQueryable().Where(x => x.PropOne == propOne && x.PropTwo == propTwo)

just not with using specification.

[fiseni]

Any chance you can share your specifications and the tests? I got intrigued now.

[justinkauai]

` private readonly WebTestFixture _factory; public SampleControllerTests(WebTestFixture factory) { _factory = factory; var optionsBuilder = new DbContextOptionsBuilder(); optionsBuilder.UseInMemoryDatabase("InMemoryDb"); _dbContext = new ApplicationDbContext(optionsBuilder.Options);

        // Run this if you've made seed data or schema changes to force the container to rebuild the db
        // _dbContext.Database.EnsureDeleted();

        // Note: If the database exists, this will do nothing, so it only creates it once.
        // This is fine since these tests all perform read-only operations
        _dbContext.Database.EnsureCreated();

    }

    private int _testId = 123;
    private Guid _testGuid = new Guid("11111111-1111-1111-1111-111111111111");

    private class TestItem
    {
        public int Id { get; set; }
        public Guid PropOne { get; set; }
        public Guid PropTwo { get; set; }
    }

    private class ItemSpecification : Specification<TestItem>
    {
        public ItemSpecification(Guid propOne, Guid propTwo)
        {
            Query.Where(x => x.PropOne == propOne && x.PropTwo == propTwo);
        }
    }

    [Fact]
    public async void SpecificationMatchesEf()
    {
        _dbContext.TestItems.Add(new TestItem
        {
            Id = _testId,
            PropOne= _testGuid,
            PropTwo = _testGuid,

        });
        _dbContext.SaveChanges();
        var spec = new ItemSpecification(_testGuid, _testGuid);

        var evaluator = new SpecificationEvaluator<TestItem>();
        var testItems = _dbContext.Set<TestItem>().AsQueryable();
        var resultFromSpec = evaluator.GetQuery(testItems, spec);

        var resultFromEf = _dbContext.TestItems.AsQueryable().Where(x => x.PropOne == _testGuid &&  x.PropTwo == _testGuid);

        Assert.True(resultFromSpec.Any());
        Assert.NotNull(resultFromEf);
    }

    [Fact]
    public void SpecificationWorksWithoutEf()
    {
        var spec = new ItemSpecification(_testGuid, _testGuid);

        var evaluator = new SpecificationEvaluator<TestItem>();
        var result = evaluator.GetQuery(GetTestListOfItems().AsQueryable(), spec).FirstOrDefault();

        Assert.Equal(_testId, result?.Id);
    }

    private List<TestItem> GetTestListOfItems()
    {
        return new List<TestItem>
        {
            new TestItem{ Id = 1, PropOne = new Guid(), PropTwo = new Guid()},
            new TestItem{ Id = 2, PropOne = new Guid(), PropTwo = new Guid()},
            new TestItem{ Id = _testId, PropOne = _testGuid, PropTwo = _testGuid},
            new TestItem{ Id = 3, PropOne = new Guid(), PropTwo = new Guid()}
        };
    }

}

}`

[fiseni]

Do anything fails on your side? Everything passes here.

Thank you for your patience, btw :)

[justinkauai]

Yeah the resultFromSpec is null. Are u on the latest efcore?

Does it still work on your end if PropTwo is a nullable guid? Or if the primary key Id is a guid?

And thanks for your patience. This has been a head scratcher!

[fiseni]

Nope, I'm getting data. I'm debugging now

Could you please add one more case as following.

var resultFromSpec = evaluator.GetQuery(testItems, spec);
var resultFromEf = _dbContext.TestItems.AsQueryable().Where(x => x.PropOne == _testGuid && x.PropTwo == _testGuid);
var resultFromEf2 = _dbContext.Set<TestItem>().AsQueryable().Where(x => x.PropOne == _testGuid && x.PropTwo == _testGuid);

Assert.True(resultFromSpec.Any());
Assert.NotNull(resultFromEf);
Assert.NotNull(resultFromEf2);

If resultFromSpec fails on your side, then resultFromEf2 should fail too, since that's literally the same. Other than that, please try with EF Core 3.1.6. I'm on that version.

[ShadyNagy]

@justinkauai I think it will be better if you upload this test project to your Github so we can clone and work on the issue. Maybe there is something you configured and @fiseni not.

[justinkauai]

i think i might of found the issue buried in my test database seed on one of the included entities. thanks for bearing with me!

What are the issues with Include and InMemory out of curiosity. The specification in my app actually has an Include and a ThenInclude. Looks like its Including the related entities correctly in InMemory now(very cool).

i think i'll end up using a mixture of InMemory and SqlServer in my tests. So far unless I am missing something I will only need SqlServer for db Key violations etc.

[fiseni]

You might face various issues, it depends for what and how you using it actually. First of all, In-Memory DB is not a relational database, so it doesn't support relational behaviors. You shouldn't be surprised that related entities are loaded. Since it's in memory operation (and the way the seeding of data is done), objects are in memory and the data is always loaded, even in the cases when you don't expect them to be. That's the issue with Includes, and you may get fake green results (it depends what you're testing). Use In-Memory DB when database is irrelevant to the test, it's not the main focus. Tests which are not dependent on database behavior. Otherwise, you should be cautious. Oh, and if you want to test actual db queries, then definitely don't use it.