Entity Exception : the underlying provider failed to open

Question

iv'e got a wcf service host hosting a service on IIS7,

iv'e added a database to it's App_Data folder ,

the service is referenced to a DAL project

which holds an Entity Framework model generated from my DB ( The DB from the WCF Service Host )

i keep getting the above entity exception with this inner message :

{"An attempt to attach an auto-named database for file C:\\Users\\eranot65\\Documents\\Visual Studio 2010\\Projects\\CustomsManager\\WcfManagerServiceHost\\App_Data\\CustomesDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

iv'e copied the connection string from DAL/app.config to WcfManagerServiceHost/Web.config

   add name="CustomesDBEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\eranot65\Documents\Visual Studio 2010\Projects\CustomsManager\WcfManagerServiceHost\App_Data\CustomesDB.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True'" providerName="System.Data.EntityClient"      

this happens when i try to use my data source entity model:

   public List<Employee> GetEmployees()
    {
        List<Employee> employees = null;
        using (CustomesDBEntities entites = new CustomesDBEntities())
        {
            employees = entites.Employees.ToList<Employee>();
        }
        return employees;
    }

it doesn't seem as if the DB is in use some where else ,

(1) how can i check if some other process is holding a handle to my DB ?

(2) in ideas this happens ?

Answer 1

I would consider checking one of two things:

1. Create a connection to your SQL Express, either in VS server explorer, or by using the SQL management studio, and verify you do not already have a database by that name attached to your server.

2. Move your project from it's current location to somewhere on the disk which is not user-specific (meaning not on the desktop, documents etc..), for example - c:\temp, c:\projects... The reason for that is that you are running a web application, and in case you run it in IIS, the identity of the worker process is a special identity other than yours which might not have permissions to access the database file since it is located in a private folder of your user

Answer 2

Most likely the problem is that you are opening the database with Visual Studio and your application at the same time. The connection string explicitely configures AttachDbFilename=... AttachDBFilename spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. In single user mode, only one application can open the MDF at a time.

Answer 3

well the answer is always simpler then you would think

all i ended up doing is changing to automatically generated connection string

generated by the EntityFramework model , to a connection string to locate the DB in my App_Data folder

my original connection string :

connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='Data
Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\eranot65\Documents\Visual Studio 2010\Projects\CustomsManager\WcfManagerServiceHost\App_Data\CustomesDB.mdf"
;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True'" providerName="System.Data.EntityClient"    

my edited connection string :

connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;
provider connection string='Data 
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CustomesDB.mdf;Integrated Security=True;;User Instance=True;MultipleActiveResultSets=True'"