Can I verify the construction of a LINQ query without executing it

Question

I heard that when testing EF you need to use integration testing with a live database due to the differences between LINQ to Objects and LINQ to Entities providers.

Why can't we unit test build a query without actually executing it? I would think that you attach your IQueryable to the LINQ to Entities provider as soon as possible and make sure that the SQL is generated correctly (using something like ToTraceString, which does not fulfill the actual query).

I present the code in these lines (this request will work fine in L2O, but not in L2E):

<Test()> _ Public Sub Query_Should_Build_Against_L2E() Dim testQuery = From d In myDb Where d.Status = CType(Status.Ready, Integer) testQuery.SetQueryProvider("L2E") Assert.DoesNotThrow(testQuery.ToTraceString()) End Sub 

EDIT

I tried to implement the GertArnold solution as follows:

 Dim context As New Context("Data Source=fakedbserver;Initial Catalog=fakedb;Persist Security Info=True;") Dim result = context.myTable.Where(Function(d) d.Status=True) 

This causes a ProviderIncompatibleException message with the message "An error occurred while retrieving vendor information from the database. It could be caused by the Entity Framework using the wrong connection string. Check the internal exceptions for more details and make sure the connection string is correct." Here's the full chain of exceptions:

System.Data.ProviderIncompatibleException: An error occurred while retrieving provider information from the database. This may be caused by the Entity Framework using the wrong connection string. Check the internal exceptions and verify that the connection string is correct.

System.Data.ProviderIncompatibleException: the provider did not return the ProviderManifestToken string.

System.InvalidOperationException: This operation requires a connection to the master database. The connection to the master database could not be created because the original database connection was open and the credentials were removed from the connection string. Install unopened connection.

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not available. Verify the instance name is correct and configure SQL Server to connect remotely. (provider: Named Pipes provider, error: 40 - Could not open SQL Server connection)

Answer 1

attach your IQueryable to the LINQ to Entities provider

Question: what is IQueryable ? The interface itself is nothing. When working with EF, you are dealing with classes that implement IQueryable , but also do much more. In fact, they are fully equipped to interact with the context and the query provider, which is already attached under the hood. IQueryable.IQueryProvider is a IQueryable.IQueryProvider -only property, so it is set by a factory that creates a specific implementation of IQueryable .

So your testQuery will be ObjectQuery because it is taken from myDb , which cannot be anything other than the EF context.

Why can't we unit test build a query without actually executing it?

Isn't that a question? In fact, I’m even wondering if you want another query provider for this. I think you would like the EF query provider to act this way, otherwise there is no guarantee that it will work the same with EF.

Anyway, you can create a context with the name of the fake database in the connection string (to make sure it is not connected) and check the validity ((ObjectQuery)testQuery).ToTraceString() . This is normal if you do not go through testQuery . I can imagine that in such a test there is some value when requests are stacked in complex execution paths. (But I would rather avoid this).

Answer 2

Short answer

I managed to create a DbContext layout that "connects" to the correct EF provider without any actual db connections. This ensures that the LINQ test works in an integration scenario with your real database. Currently, it only applies to MS SQL Server, but it is very easy to extend to other EF providers. I have posted the full code below, I would be very happy to hear reviews about this.

EDIT I ​​moved the code to the GPL CodePlex project .

Long answer

After looking at the GertArnold answer and rethinking the whole problem (and thinking extensively about the sources), I came up with what I think is a viable solution. The problem is that the default setting for DbContext is:

• Use the default SqlConnectionFactory to create the database connection requested by the nameOrConnectionString property of the nameOrConnectionString constructor
• Create a database if it does not exist (because it uses CreateDatabaseIfNotExists as the default context initializer)
• Open a database connection to confirm availability (using DbConnection.Open and DbConnection.State )
• Request a database to confirm that the requested model exists in it (using System.Data.Entity.Internal.InternalContext.QueryForModel , which in turn creates an active connection command to query the db for the model)
• Create a System.Data.Common.DbProviderServices and System.Data.Common.DbProviderManifest object that together provide support for translating expressions into SQL .

The first four of these steps (which require an actual db connection) can create a new IDatabaseInitializer that does nothing in the InitializeDatabase method and then register for the context type being tested with Database.SetInitializer .

Step 5, on the other hand, is the reason that we need a real DbContext to select at all. Without DbProviderServices and DbProviderManifest, EF would have no idea how to convert expressions. I was able to use reflection to get internal SqlProviderManifest and SqlProviderServices and give them work (I can even specify which version of SQL server to use per year).