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

musefan
  • 47,875
  • 21
  • 135
  • 185
  • did u consider localdb? http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx – jenson-button-event Oct 24 '13 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 – musefan Oct 24 '13 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 – jim tollan Oct 24 '13 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 – musefan Oct 24 '13 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! – qujck Oct 24 '13 at 14:37
  • Do you have the sqlite EF provider installed? Have you managed to get a context working with sqlite by itself? Also, can you post the stack trace for the exception you got? – Mansfield Oct 24 '13 at 14:48
  • @Mansfield: yes the provider is installed, and I can get it working fine if I generate an edmx directly from an SQLite database file. Stack trace edited in if that helps anything – musefan Oct 24 '13 at 15:02
  • @musefan is this code first or db first? There is a property in the xml file I have generated for an SQL Server edmx specifying that the provider is `System.Data.SQLClient`. Search for `Provider="System.Data.SqlClient"` in your xml file, and then see what that property is in your SQLite version. Perhaps that has something to do with it... – Mansfield Oct 24 '13 at 15:56
  • @Mansfield: I am doing it with DB first. I have seen that provider property you talk about and I would agree that this is part of the problem. However, I cannot find a way to change that on the fly - there is no property relating to it (like there is with connection string). I wouldn't be adverse to using multipl edmx files providing I could hotswap them as required on the fly. I am not sure if these are pre-compiled as part of the build process though? This is a very tricky subject :( – musefan Oct 24 '13 at 16:07
  • @musefan Indeed. They are pre-compiled as part of the build, but if you chose which one to use programatically it shouldn't actually blow up until you use the wrong one, which you can code to avoid. I don't know enough about the internals of the entity framework to suggest a better solution at this point. My guess is that it was not designed for your requirements and that using multiple contexts would be best solution. – Mansfield Oct 24 '13 at 17:11
  • @musefan You're only option is, change to code-first, have no edmx file. – Kind Contributor Oct 10 '16 at 10:03
  • Did you find a solution? – Ahmed Shamel Apr 14 '20 at 20:38
  • @AhmedShamel. Nope never found solution. Didnt need to do it in the end. – musefan Apr 14 '20 at 21:20
  • I will start bounty because I need a solution for same problem. – Ahmed Shamel Apr 14 '20 at 21:47

3 Answers3

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.

Phil Coulson
  • 31
  • 2
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.

Daniel Lorenz
  • 4,178
  • 1
  • 32
  • 39
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.

Hooman Bahreini
  • 14,480
  • 11
  • 70
  • 137
  • 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 – Ahmed Shamel Apr 15 '20 at 23:16
  • 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](https://stackoverflow.com/questions/13043530/what-is-app-config-in-c-net-how-to-use-it) 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. – Hooman Bahreini Apr 15 '20 at 23:25