How To Use Entity Framework 4 With Visual Studio 2010 and SQL Server 2000

If you are unfortunate enough to have to support applications running on legacy SQL Server 2000 databases like me, you are probably aware of the limitation of Visual Studio 2010 and Entity Framework 4.0 for SQL Server 2000.

There are plenty of articles out there on work-arounds. I find this one the most direct and feasible. Granted, I have not looked into possible version issue that may result in doing it this way, but for the various applications on which I have worked, it is sufficiently elegant and functional.

This a work-around. Let me repeat, this is a work-around!.

Here are the steps to add a new Entity Framework 4 entity context to a Visual Studio 2010 project:

1. Download the base entity data model for SQL Server 2000 file and save it to the project folder to which you wish to add the SQL 2000 entity context. Do NOT add it to your project yet.
2. Rename the EDMX file to the name of the data context (e.g. MyDataModel.edmx).
3. Open the EDMX file in a text editor.
4. Find/replace all references to %DB_NAME% with your own value (e.g. SomeDatabaseName).
5. Add this new connection string section to your App or Web.config and find/replace with your own values:

   <connectionStrings>
     <add name="%DB_NAME%Entities" connectionString="metadata=res://*/%DB_NAME%.csdl|res://*/%DB_NAME%.ssdl|res://*/%DB_NAME%.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=%DB_HOST_NAME%;Initial Catalog=%DB_NAME%;Persist Security Info=True;User ID=%DB_USER%;Password=%DB_USER_PWD%;MultipleActiveResultSets=False&quot;" providerName="System.Data.EntityClient" />
   </connectionStrings>

   Please note: the connection string name MUST MATCH the EntityContainer section name you specified in the EDMX file.

6. After the .edmx and web.config files configured, add them to the project and verify the new entity data source appears after refreshing the Data Source manager window.
7. If the new entity data source appears, open the .edmx file in design view and refresh the design model.