Switching entity framework model from SQL Server to DB2

Question

First - if anyone has an entity model with DB2 working, can you please post it? And now for the explanation:

I have a development environment that has a SQL Server database (just for development) and uat/production environments with a DB2 database (more or less the same db, just on a different platform)

Using Visual Studio 2010 I can successfully generate an entity framework model against the SQL Server database, but not against DB2. I have DB2 9.7 client as well as the 'IBM Database Add-ins for Visual Studio' installed which allows me to create connections to the DB2 database and connect to it via Server Explorer, but when I try to generate a model from DB2 Visual Studio hangs and eventually I get an empty model.

Since I can generate a model from SQL Server, I am wondering if it is an easy switch to just change the web.config to point to DB2 instead of SQL Server when my environment switches.

Is anyone out there using entity framework with DB2, and if so can you please post your web.config so I can try just switching providers?

See http://stackoverflow.com/questions/985912/using-linq-to-entity-framework-with-db2 and http://stackoverflow.com/questions/2722906/how-to-get-the-ibm-db2-provider-to-work-with-entity-framework-4-0 — David, Aug 22 '12 at 17:03
not helpful at all. I have already seen these and they have nothing to do with my problem. I can connect to DB2 but like it says in my question it hangs then gives me an empty data model. If you are using DB2 with entity framework can you please post your web.config so I can see how to reconfigure to hook up to DB2? — Mario, Aug 22 '12 at 19:09
If anyone cares, I believe that one of the reasons db2 generation fails is because the db2 databases are very old (pre 9.4 which I think is when they added the .net provider). They have been upgraded but I believe some of the schema was left in the pre-9.4 way, therefore breaking the .net provider. I think it breaks when looking at certain keys and such, but for all the CRUD operations it should work. Which is double the reason to just switch once I have generated. Can anyone help me switch to DB2? — Mario, Aug 22 '12 at 19:37

Mario · Accepted Answer · 2012-08-30T20:58:52.520

Two changes are necessary to switch your entity model to connect to DB2. Depending on what version of what you have this obviously may change. .edmx change:

<!-- SQL SERVER <Schema Namespace="Models.myDb2Model.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008"                  xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">-->
                <Schema Namespace="Models.myDb2Model.Store" Alias="Self" Provider="IBM.Data.DB2"          ProviderManifestToken="DB2, 09.01.0005, 0, 0" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">

And here is the web.config change:

 <!-- SQL SERVER<add name="db2Connection" 
     connectionString="metadata=res://*/Models.db2Entities.csdl|res://*/Models.db2Entities.ssdl|res://*/Models.db2Entities.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=mySqlServerDb;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" /> -->

  <add name="db2Connection" 
       connectionString="metadata=res://*/Models.db2Entities.csdl|res://*/Models.db2Entities.ssdl|res://*/Models.db2Entities.msl;provider=IBM.Data.DB2;provider connection string=&quot;Database=myDb2Db;User ID=myUserId;Password=MyPassword;Server=myServerName:myPort;DBName=myDbName;ConcurrentAccessResolution=Currentlycommitted;CurrentPackageSet=myCurrentPackageSet&quot;" 
       providerName="System.Data.EntityClient" />

Edit: obviously your machine needs to be set up to connect to DB2, have the proper client and database add ins for VS.

Switching entity framework model from SQL Server to DB2

1 Answers1