ASP.Net Linq to Entity: The underlying provider failed on Open

Question

I seem to be having a recurring problem. I get it all working and then this happens again a few days later. I havn't done anything to touch the entity framework or the database that is now failing. The instantiation of the Entity edmx is working but I get the following message when trying to execute a query to the database. The way I've solved it is by deleting the databases and clearing out the SQL MGMT Studio and adding them in again and refressing the edmx file.

I have 2 project: DataLayer where my entity framework definition exists, Business Logic and UI. I noticed there are some differences between the web.config between my DataLayer and the UI and don't know if that's a problem. The database was added to the App_Data folder in the DataLayer. The solution keeps adding a copy of the database to the AppData folder in the UI and addes a numberic extension to the filename in the Server Explorer( In the BL the filename is FCGuide.mdf, in the UI is was anmed FCGuide.mdf0 -- don't understand that).

Today I have deleted the database from the project and from the SQL SErver Management Studio and then tried adding it in again without a resolution. I have provided the web.config but not sure what else is needed to help with the troubleshooting. Thanks in advance.

WEB.CONFIG file from the UI:

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
    <appSettings>
        <add key="ChartImageHandler" value="storage=file;timeout=20;dir=c:\TempImageFiles\;" />
    </appSettings>
    <system.webServer>
        <handlers>
            <remove name="ChartImageHandler" />
            <add name="ChartImageHandler" preCondition="integratedMode" verb="GET,HEAD,POST"
                path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
        </handlers>
    </system.webServer>
    <system.web>
        <authorization>
            <allow roles="ADMIN" />
            <allow roles="MEMBER" />
            <allow roles="GUEST" />
            <allow roles="RESTAURANT" />
            <allow users="admin" />
            <allow roles="MEMBER" />
            <allow roles="GUEST" />
            <allow roles="GUEST" />
            <allow roles="ADMIN" />
            <allow users="admin" />
        </authorization>
        <roleManager enabled="true" />
        <authentication mode="Forms" />
        <httpHandlers>
            <add path="ChartImg.axd" verb="GET,HEAD,POST" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                validate="false" />
        </httpHandlers>
        <pages>
            <controls>
                <add tagPrefix="asp" namespace="System.Web.UI.DataVisualization.Charting"
                    assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
            </controls>
        </pages>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
  <connectionStrings>
    <add name="FCGuideEntities" connectionString="metadata=res://*/FCGuide.csdl|res://*/FCGuide.ssdl|res://*/FCGuide.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;attachdbfilename=|DataDirectory|\FCGuide.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Web Config file from the DataLayer:

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      </assemblies>
    </compilation>
  </system.web>
  <connectionStrings>
    <add name="FCGuideEntities" connectionString="metadata=res://*/FCGuide.csdl|res://*/FCGuide.ssdl|res://*/FCGuide.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;attachdbfilename=|DataDirectory|\FCGuide.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

What error are you getting? (Need, at least, type and message.) What is the data access code that's failing? — Richard, Dec 04 '11 at 16:57
Error: Entity exception was unyhandled by user code. The underlying provider failed on Open. — Susan, Dec 04 '11 at 16:59
The linq query was: var restList = (from RESTAURANT in db.RESTAURANTs.Include("CUISINE").Include("CITY") where RESTAURANT.REST_ID == restID select RESTAURANT).ToList(); — Susan, Dec 04 '11 at 17:00

score 0 · Answer 1 · answered Dec 04 '11 at 17:05

0

"The underlying provider failed on Open": this means the connection string didn't work to allow ADO.NET to open the database.

So the provider connection string:

data source=.;attachdbfilename=|DataDirectory|\FCGuide.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework

needs some work. If you create a test page that uses SQL Connection on that string can you open and perform a simple query?

(Ie. strip things down to as few moving parts as possible).

answered Dec 04 '11 at 17:05

Richard

106,783
21
203
265

Would this help explain why it works sometimes and then stops? – Susan Dec 04 '11 at 17:06
@Susan maybe, depending on the underlying problem. – Richard Dec 04 '11 at 17:46
I did a file compare on the web.config files and they were identical between my last version and the current version (the one that fails). It doesn't seem like it's a problem with the web.config. – Susan Dec 07 '11 at 00:19
@Susan I would be looking at the database with the error you have quoted. That's why I suggest a test page that strips everything right down to the basics. – Richard Dec 07 '11 at 07:45

ASP.Net Linq to Entity: The underlying provider failed on Open

1 Answers1