Efcore: Guidance for unit testing when FromSql<> is used.

[dazinator]

We have unit tests that use the InMemory provider.

However, a few of our objects under test are calling the FromSql() method to materialise objects from stroed procedures.

When using the InMemoryProvider this obviously doesn't work.

One solution i can think of is for the object under test to use the Repository pattern - so we can swap EF7 out entirely at test time, for an InMemory repository. However I think it would be good if the InMemory provider had a way to handle FromSql at test time, so you could map an abitrary set of results when needed?

[ErikEJ]

The InMemory Provider is not a relational provider, use SQLite InMemory or localdb

[dazinator]

SqlLite sounds like a good shout - thanks 👍

[dazinator]

I don't think this really solves the issue though.. we use InMemoryProvider during testing, because we want to eliminate the need for having an external database asset to manage. This means we can run unit tests on any machine quite easily, with minimal setup.

Using sqllite provider at test time isn't an option if the subject under test is calling FromSql<> and passing in a variant of sql like for example, Postgres. (Because you use Postgres in production) or executing a sproc.

So the only reliable way to get this to work is to not use the InMemory provider, and use the full blown relational provider - which brings us back to square one - managing the external database at test time.

If there was some mechanism to mock or map FromSql<> and still use the InMemory provider I think that is what we would like to do.

[rowanmiller]

@dazinator yep using InMemory or SQLite in-memory mode are all an approximation for running against a real database. In general, SQLite in-memory is a decent approximation for another relational database, but it's never going to be identical. Even if it supports the same features, there is no guarantee it will behave exactly the same as the database provider your application ultimately runs against.

Where you are using something like raw sql to stored procedures, you'll need to either run against the database that supports them, or introduce a layer of abstraction so that you can create a test double for testing.

[dazinator]

or introduce a layer of abstraction so that you can create a test double for testing.

Yup thats what we did. Have made a note: don't call FromSql<> directly!

[dazinator]

@camainc
I can't share my work as it's my employers but the basic idea is just create an interface, and have all the classes that need too, call a method on that, instead of ef's FromSql method directly. Whether you have a single generic method on that interface, or individual methods representing different queries you want to execute - is up to you. Now at test time, you can mock that interface to return whatever data you need when its method/s are called. Your default implementation could just defer to Ef's FromSql method. Sorry I can't share code. It's very basic stuff nothing complicated.

[camainc]

Thanks for responding. I deleted my comment because I realized it was a stupid question. I did just what you suggested as soon as I deleted the comment.

Thanks again!