16

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

Improve this question
14
  • did u consider localdb? blogs.msdn.com/b/sqlexpress/archive/2011/07/12/… Commented Oct 24, 2013 at 14:07
  • @jenson-button-event: Just had a look at it and it seems localdb requires installation on the client. The reason I am currently looking into SQLite is because I need a single file that can easily be moved from machine to machine Commented Oct 24, 2013 at 14:13
  • i think you'll need to show more of the inner workings of your partial class here. obviously, there's an explicit type being expressed, rather than an inteface onto the sqlconnection Commented Oct 24, 2013 at 14:13
  • @jimtollan: It's not going to help. The partial is purely a single constructor overload that allows the connection string to be passed to the base DbContext constructor. By default there is no auto-gen overload that allows this, that is why I created the partial Commented Oct 24, 2013 at 14:15
  • @musefan a total guess here to be honest but I would imagine the entire Application Domain would be tied to a particular database implementation and it will not be possible to swap and change in-process due to the metadata etc that EF statically stores within the AppDomain. Maybe ... can't really be sure without reading a lot of source code! Commented Oct 24, 2013 at 14:37

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

2 Comments

Thanks for your answer. I have two point that needs Some clarification. First point is that who can project has two 2 app files, this is new to me. Second point is when inserting or reading data from database, how would entity framework decide which database to use (sqlite or sql server). I think it would be helpful if you create a simple winfrom app that contain such solution
Let's say you want to use SQLite in your Dev environment and SQL Server in your Test Environment, then you would put SQLite config in app.dev.config and SQL Server config in app.test.config... you need to learn about app.con file... The connectionstring has a ProviderName... so in the example above, if you pass the SQLiteConnection to EF, it would know that it has to use System.Data.SQLite Provider.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.