EF Core inmemory as a serious option?

So.. I have this use case in which a memory database could be a solution. So I wondered if maybe the inmemory option of EF Core could be used for that.

I know that everywhere it states fairly clearly that this is meant for testing only.. but if it works it works right..?

So the first thing I wanted to know was weather it would index on large tables. Otherwise it would be useless for me in this case.

I created this object:

public class TestModelNoIndex
{
    public int TestModelNoIndexId { get; set; }

    public int TestModelValue { get; set; }

}

And this object:

public class TestModelIndexed
{
    public int TestModelIndexedId { get; set; }

    public int TestModelValue { get; set; }

}

I created two DbContexts one for inmemory and one for sql, in each DbContext I override the OnModelCreating, like this:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestModelIndexed>().HasIndex(b => b.TestModelValue);
        base.OnModelCreating(modelBuilder);
    }

On Startup I create a million of my test objects like this:

        for (int i = 0; i < 1000000; i++)
        {
            int value = 1000000 - i;

            _memctx.TestNoIndex.Add(new Models.TestModelNoIndex { TestModelValue = value });
            _memctx.TestIndexed.Add(new Models.TestModelIndexed { TestModelValue = value });

        }

And now for the test I query the sql context and the memory context for some random values:

            _context.TestNoIndex.Single(s => s.TestModelValue == 767673);
            _context.TestIndexed.Single(s => s.TestModelValue == 767673);

The results for the sql context are as expected different for the indexed one and the none indexed one.

DB No index result: 1166ms DB Indexed result: 21ms

The memory context showed almost identical times for the indexed and the non indexed one.

MEM No index result: 298ms MEM Indexed result: 252ms

So clearly the inmemory option does not do any indexing. But the most important observation is the difference between the sql result and the memory result. The indexed sql one is more than 10 times faster.

Conclusion: The inmemory option is really only for testing, the sql context (indexed) performs way better.