How to validate LINQ queryable without database

Question

Note:

I'm open to suggestions on a better title for this question.

Background:

For anyone who has worked with Entity Framework long enough, you're bound to run into an issue where the generated IQueryable<T> is unable to be executed by the database. This stems from the fact that IQueryable<T> is impossible to completely implement except for an in memory implementation such as LINQ to objects. Things such as C# method calls and using Single/SingleOrDefault as a non-final query operation can cause failures when sent to a real IQueryProvider(such as for MSSQL or Oralce), but pass in unit tests.

The only way I currently know how to test for these cases is to actually run the software. While I agree that the software should definitely be done as part of writing new queries (or new code in general), it would be helpful if these kinds of bugs could be found using unit tests. The event that led me here was discovering a new bug from what I'm sure the developer though was a innocent and safe change. Even further, a false sense of confidence was given by the multitude of passing unit tests.

Question:

Is it possible to validate that the IQueryable<T> produced can in fact be run on a specific database technology (MSSQL,Oracle, ect...) from unit tests?

Examples:

Queryable with C# method call:

var result = (
    from a in session.Query<A>
    where a.Field == SomeFunction(a)
    select a
    ).ToList();

This will fail for the obvious reason of the database not being able to execute C# code.

Queryable with Single

var result = (
    from a in session.Query<A>
    where a.Field == session.Query<B>().Single().Field
    select a
    ).ToList();

This will fail due to usage of single as a non-final query operation.

There are other cases, but I think the above two examples describe what I'm trying to be able to detect.

We use "unit tests" that require a clean database setup. Our test setup inserts test data and the teardown deletes it. They aren't true unit tests as they require database connectivity, but they aren't quite full functional tests either. — GendoIkari, May 18 '16 at 19:01
@mhand I don't know the answer. But your question is the best well-prepared one I have ever seen in SO. Hopefully this question could give your some idea http://stackoverflow.com/questions/6599805/unit-test-iqueryable — Yang You, May 19 '16 at 01:53
@mhand And also have a look at this http://www.codeproject.com/Tips/1036630/Using-Effort-Entity-Framework-Unit-Testing-Tool — Yang You, May 19 '16 at 01:57
We have repository pattern in top of entity framework and `assert the query and logic is valid`.http://joel.net/repository-and-unit-of-work-for-entity-framework-ef4-for-unit-testing — Eldho, May 19 '16 at 14:34

score 2 · Answer 1 · answered May 19 '16 at 14:24

2

Having this query

var query = 
    from a in session.Query<A>
    where a.Field == session.Query<B>().Single().Field
    select a;

execute

query.ToString();

if it cannot be translated to correct SQL query, it will throw the exception.

answered May 19 '16 at 14:24

Sergey L

1,402
1
9
11

Interesting approach. The main issue I see with this is that it requires access to the `IQueryable` instance. I suppose I was kind of hoping for an implementation of `IQueryProvider` which could check the queryable as it's being produced – mhand May 19 '16 at 19:11
It does not run actual query if it is your concern here. – Sergey L May 20 '16 at 09:28

score 0 · Answer 2 · answered May 18 '16 at 19:03

You'll have to mock properties like session.Query<A> and send an enumerable\queryable collection in your unit tests. In my experience the benefit of doing this is not enough to create and maintain these kind of tests, specially when you have complicated queries involving multiple tables and layers.

What I found useful though (for testing the actual queries) is more integration tests with frameworks like specflow, with which you can easily set up test data and execute integration tests while testing software behavior from a higher level than unit tests. But of course these should work on the real database, so you might not want them to begin with.