Using Entity Framework as a Web Part's Data Source in Sharepoint 2010

Question

I have a very simple web part. I have a single grid view, which I am populating using linq to entities (or at least that's what I want to do). The Entity Data Model .edmx file is located in the same project as the web part, and everything looks to be in working order. When I debug the project, it blows up on the entity model constructor with the error message:

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

My connection string in the App.Config is as follows:

<add name="MyDBEntities" connectionString="metadata=res://*/MyDBEntityModel.csdl|res://*/MyDBEntityModel.ssdl|res://*/MyDBEntityModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

The constructor:

public MyDBEntities() : base("name=MyDBEntities", "MyDBEntities")

So, from what I've read elsewhere, my problem is that SharePoint can't see my connection string. Which means, that the App.Config from my project isn't actually getting loaded into SharePoint when I run/debug the project. If that's the case, then how I do set my project up in Visual Studio 2010 to ensure SharePoint picks up the App.Config in addition to the master SharePoint config file. If I have to manually copy the connection string, is there a "best practice" procedure for doing so? Are SharePoint Web Parts combined with the Entity Framework just not ready for prime time?

Answer 1

The SharePoint Tools for Visual Studio 2010 have come along way and will automatically make many of the necessary entries into web.config. Unfortunately, they won't make Entity Framework entries for you. To do this, you'll need to write a feature receiver for your web part project that adds the EF connection string.

The SharePoint API has an object named SPWebConfigModification. You should write a FeatureActivated event that uses this class to make your modification to web.config and then a FeatureDeactivating event that removes the modification.

-Greg

Answer 2

I was wrestling with a the same exception in a SharePoint 2010 WebPart, and I've finally got it working, but here are two important things that I learned along the way.

1. You must use a farm solution rather than a sandbox solution. The reason for this is, sandbox solutions do not have access to data outside the site collection. A more meaningful exception would have been helpful to solve this quickly, but I was receiving the exception as above.

2. Your connection string must be in the web.config of the Web Application that you install your WebPart on. It is not added automatically when you install your WebPart, so you must either update the web.config the way Greg lists above, or edit it manually. It sits in C:\inetpub\wwwroot\wss\VirtualDirectories{WebApplicationName}\web.config