CODEWITHSUNDEEP
c#sql-serverentity-frameworksqlite
musefan
musefan

Reputation: 48415

Using entity framework with both SQL Server and SQLite databases simultaneously

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()

Upvotes: 16

Views: 4586

Answers (3)

Hooman Bahreini
Hooman Bahreini

Reputation: 15559

You can achieve this easily using Entity Framework Code First. Code First approach does not use an .edmx file. Your entities (classes) are automatically mapped to DB tables.

You would define both connectionstrings in your app.config file and configure their providers. Now you can switch between DB's by passing the desired connectionstring to EF.

/* connection string for SQLite */
<add name="SQLiteConnection" connectionString="Data Source=MyDBName.sqlite" providerName="System.Data.SQLite" />

/* connection string for SQL Server */
<add name="SQLServerConnection" connectionString="Data Source=MyDB; Integrated Security=True" providerName="System.Data.SqlClient" />

See SQLite Code First and EF 6 Code First for more information.

Upvotes: 0

Phil Coulson
Phil Coulson

Reputation: 31

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.

Upvotes: 3

Daniel Lorenz
Daniel Lorenz

Reputation: 4336

Are you passing in the connection string or the name of the connection string in the <connectionStrings> app settings section? I believe the issue is as you describe. It defaults the provider to System.Data.SqlClient. If you want Sql Lite, you have to set the providerName on the <connectionString> and then send the name (attribute) of that <connectionString> to DbContext (It knows how to look that up automatically). It should then use that new providerName attribute instead of SqlClient.

Upvotes: 0

Related Questions