Using entity framework with both SQL Server and SQLite databases simultaneously

Question

I have a C# .Net 4.0 console application for testing purposes (using VS 2012). My aim is to be able to create a single Entity Framework .edmx file that can be used on both an MS SQL Server database, and an SQLite database. Basically, I want to use the same entity model classes and collections for querying, but easily be able to switch between the two different databases at will.

So far I have created my .edmx file by connection to MS Server database and adding my single test table (called Contact). With this I can then use the following code to get data from my table:

var db = new DataAccess.ContactTestEntities();
foreach (var contact in db.Contacts)
    Console.WriteLine("" + contact.ID + ". " + contact.FirstName + " " + contact.LastName);

Now, I want to be able to use the same code but connect to an SQLite database instead. I have written a partial class that allows my to change the connection string on contruction like so:

var db = new DataAccess.ContactTestEntities("MY SQLITE CONNECTION STRING");

It works fine in that respect except when trying to query the database I get this error:

Unable to cast object of type 'System.Data.SQLite.SQLiteConnection' to type 'System.Data.SqlClient.SqlConnection'.

I have tried to find a solution to this but have hit a dead end and I am struggling to find the next step to take.

So that is my question: How can I get past this problem? Or is there another approach I can take to get the same desired results?

---

Stack Trace for above exception:

at System.Data.SqlClient.SqlCommand.set_DbConnection(DbConnection value) at System.Data.Common.Utils.CommandHelper.SetStoreProviderCommandState(EntityCommand entityCommand, EntityTransaction entityTransaction, DbCommand storeProviderCommand) at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Data.Entity.Internal.Linq.InternalQuery1.GetEnumerator()
at System.Data.Entity.Internal.Linq.InternalSet1.GetEnumerator()
at System.Data.Entity.Infrastructure.DbQuery1.System.Collections.Generic.IEnumerable.GetEnumerator() at SQLiteTest.Program.ReadFromSqlite() in c:\Development\Projects\Test Applications\SQLiteTest\SQLiteTest\Program.cs:line 82 at SQLiteTest.Program.ReadTests() in c:\Development\Projects\Test Applications\SQLiteTest\SQLiteTest\Program.cs:line 63 at SQLiteTest.Program.ProcessMenu() in c:\Development\Projects\Test Applications\SQLiteTest\SQLiteTest\Program.cs:line 36 at SQLiteTest.Program.Main(String[] args) in c:\Development\Projects\Test Applications\SQLiteTest\SQLiteTest\Program.cs:line 14 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

Answer 1

Create another class for dbcontext object with sqlite dbcontext and use the same model class. Simple reason is that default entity works with sqlserver db. To use sqlite you have to use sqlite dbcontext.