CODEWITHSUNDEEP
entity-framework-5
Eric Logsdon
Eric Logsdon

Reputation: 95

entity Framework 5 changing providers at runtime

I have an application that runs at several client sites. I have to support different DBMS at the locations (SQL Server, DB2, Oracle). I am converting the application from VC++ 6.0 with an ODBC based data layer to Visual Studio 2012 and would like to use Entity Framework (database first). I am having troubles changing the database provider at runtime in my sample application. I changed the connect string in the app.config from a SQL Server to DB2 connect string and changed the default connection factory. Now when I run the program I can connect to the database (at least there is no error) but when I iterate over the linq results I get the exception:

Unable to cast object of type 'IBM.Data.DB2.DB2Connection' to type 'System.Data.SqlClient.SqlConnection'

Here is the program code:

    private void btnList_Click(object sender, EventArgs e)
    {
        using (var ListBill = new LB402_TestEntities())
        {
            var queryGroups = from Groups in ListBill.LB_Group
                                  select Groups.GroupName;

            foreach (string name in queryGroups)
            {
                lbGroups.Items.Add(name);
            }
        }
    }

The modifed portions of the app.config are:

<defaultConnectionFactory type="IBM.Data.DB2.Entity.DB2ConnectionFactory, EntityFramework" />

<add name="LB402_TestEntities" connectionString="metadata=res://*/LB402.csdl|res://*/LB402.ssdl|res://*/LB402.msl;provider=IBM.Data.DB2;provider connection string=&quot;Server=db210:50000;Database=LISTBILL;uid=uuuuu;pwd=ppppp;&quot;" providerName="System.Data.EntityClient" />

From my searching and reading it seems like I should be able to do this, but I am obviously missing something.

Upvotes: 2

Views: 1574

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

Changing connection string is not enough. EDMX file consist of three part and one is provider specific (that is the .ssdl reference from connection string). You need to have this part for every database you need to support and you need to have EF provider for every such database. The problem is that EDMX designer does not support modelling for multiple providers. So you must either have separate EDMX for every database = huge duplicity or you must start maintaining SSDL files for other databases manually (it is XML).

You should make some small Proof-of-concept with code first mapping and DbContext API because it doesn't have these obstacles - SSDL is generated at runtime from code mapping and provider specified in connection string.

Upvotes: 2

Related Questions