同时使用实体框架与SQL Server和SQLite数据库 [英] Using entity framework with both SQL Server and SQLite databases simultaneously

问题描述

我有一个C＃.Net 4.0控制台应用程序进行测试（使用VS 2012）。我的目标是能够创建一个可以在MS SQL Server数据库和SQLite数据库上使用的单个实体框架.edmx文件。基本上，我想使用相同的实体模型类和集合进行查询，但是很容易就可以在两个不同的数据库之间切换。

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.

到目前为止，我已经创建我的.edmx文件通过连接到MS Server数据库并添加我的单个测试表（称为Contact）。这样，我可以使用以下代码从我的表中获取数据：

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

现在，我想要使用相同的代码，而是连接到SQLite数据库。我写了一个部分类，允许我修改连接字符串，如下所示：

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:

无法将类型为System.Data.SQLite.SQLiteConnection的对象转换为键入System.Data.SqlClient.SqlConnection。

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:

在System.Data.SqlClient.SqlCommand.set_DbConnection（DbConnection
值）在
System.Data.Common.Utils。在
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand
entityCommand，CommandBehavior行为）$ b $中的CommandHelper.SetStoreProviderCommandState（EntityCommand
entityCommand，EntityTransaction entityTransaction，DbCommand
storeProviderCommand） b System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute在
System.Data.Objects.ObjectQuery 中的[TResultType]（ObjectContext
上下文，ObjectParameterCollection parameterValues）1.GetResults（Nullable 1
forMergeOption）
System.Data.Objects.ObjectQuery 1.System.Collections.Generic.IEnumerable< T> .GetEnumerator（）
在System.Data.En tity.Internal.Linq.InternalQuery 1.GetEnumerator（）

在System.Data.Entity.Internal.Linq.InternalSet 1.GetEnumerator（）<

System.Data.Entity.Infrastructure.DbQuery 1.System.Collections.Generic.IEnumerable.GetEnumerator（）
在SQLiteTest.Program.ReadFromSqlite （）在c：\Development\Projects\Test
应用程序\SQLiteTest\SQLiteTest\Program.cs：行82在
SQLiteTest.Program.ReadTests（）在c：\开发\Projects\Test
应用程序\SQLiteTest\SQLiteTest\Program.cs：第63行
SQLiteTest.Program.ProcessMenu（）在c：\Development\Projects\Test
应用程序\SQLiteTest\SQLiteTest\Program.cs：第36行
SQLiteTest.Program.Main（String [] args）在c：\Development\Projects\Test
Applications\SQLiteTest\SQLiteTest\Program.cs：第14行
System.AppDomain._nExecuteAssembly（运行时汇编程序集，String []
args）
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly（）

在System.Threading.ExecutionContext.RunInternal（ExecutionContext
executionContext在
System.Threading.ExecutionContext.Run（ExecutionContext
executionContext，ContextCallback callback，Object state，Boolean
preserveSyncCtx）在$ b处，ContextCallback回调，Object状态，Boolean
preserveSyncCtx） $ b System.Threading.ExecutionContext.Run（ExecutionContext
executionContext，ContextCallback callback，Object state）at
System.Threading.ThreadHelper.ThreadStart（）

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

推荐答案

您是否在< connectionStrings> 应用设置部分？我相信这个问题正如你所描述的那样。它将提供程序默认为System.Data.SqlClient。如果你想要Sql Lite，你必须在< connectionString> 上设置providerName，然后发送< connectionString> ; 到DbContext（它知道如何自动查看）。然后应该使用新的providerName属性而不是SqlClient。

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.

这篇关于同时使用实体框架与SQL Server和SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！