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.

Answer 1

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.