How to check LINQ query without database

Question

Note:

I am open to suggestions for a better heading for this question.

---

Story:

For those who have worked with the Entity Framework long enough, you should run into a problem when the generated IQueryable<T> cannot be executed by the database. This is because IQueryable<T> cannot be fully implemented, with the exception of the in-memory implementation, such as LINQ for objects. Things like C # method calls and using Single / SingleOrDefault as a non-final query operation can lead to crashes when sending to a real IQueryProvider (for example, for MSSQL or Oralce), but pass in unit tests.

The only way I currently know how to test these cases is to actually run the software. Although I agree that the software must be done as part of writing new queries (or new code in general), it would be helpful if these errors could be found using unit tests. The event that brought me here was the discovery of a new bug, due to which I am sure that the developer was an innocent and safe change. Moreover, a false sense of confidence was given by many passing unit tests.

---

Question:

Is it possible to verify that the created IQueryable<T> can be launched using a specific database technology (MSSQL, Oracle, ect ...) from unit tests?

---

Examples:

A request with a call to the C # method:

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

This will crash for the obvious reason that the database cannot 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 using a single operation as an incomplete request.

---

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

Answer 1

Having this request

 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 fix the SQL query, it will throw an exception.

Answer 2

You will have to mock properties like session.Query<A> and send the enumerable\queryable to your unit tests. In my experience, this is not enough to create and maintain such tests, especially when you have complex queries involving several tables and layers.

What I found useful (for checking actual queries) is more integration tests with frameworks such as specflow , with which you can easily set up test data and perform integration tests when testing software behavior from a higher level than modular tests, But, of course, they should work on a real database, so you would not want them to start with them.