Entity framework 4.1 won't connect to SQL Server after using Database First approach

Question

I've created a new MVC 3 website using EF 4.1 with DB first approach. I've created the edmx file and then the DBContext (the new 4.1 feature) classes. all went well.

Then, I've created new controllers using the automatic creation for DBContext. All wen't great.

Now, that I'm trying to fire up the website it won't connect to the connection string itself created.

Here is the connection string:

<add name="PizzaByMeEntities"
     connectionString='metadata=res://*/Models.PizzaByMeModel.csdl|
                                res://*/Models.PizzaByMeModel.ssdl|
                                res://*/Models.PizzaByMeModel.msl;
                       provider=System.Data.SqlClient;
                       provider connection string="data source=(local);
                                                   initial catalog=PizzaByMe;
                                                   integrated security=True;
                                                   pooling=False;
                                                   multipleactiveresultsets=True;
                                                   App=EntityFramework;"'
     providerName="System.Data.EntityClient" />

When I fire up the website I get:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

The strange part is that if I remove the connection string - I still get the exact same error. I guess that the EF just can't find the Connection String even when it's there.

Any ideas?

Thanks!

EDIT:

My DBContext is as follows:

public partial class PizzaByMeEntities : DbContext
{
    public PizzaByMeEntities()
        : base("name=PizzaByMeEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public DbSet<X> Xs{ get; set; }
    public DbSet<Y> Ys{ get; set; }
    public DbSet<Z> Zs{ get; set; }
}

EDIT 2:

the working connection string is this:

  <add name="PizzaByMe" connectionString="Data Source=(local);initial catalog=PizzaByMe;integrated security=True;pooling=False;multipleactiveresultsets=True;" providerName="System.Data.EntityClient" />

Though to make it work - I just used direct ADO.NET connection.

First thing I'd suggest is to verify that this connection string is ok (the provider connection string, not the whole entity one). Maybe dump that into a test page or app around a simple SqlConnection/SqlCommand/SqlDataReader, and see if it connects. If not, then your problem is there. If it's ok, then at least you know the problem is with EF or your code, not your database connection string. — Joe Enos
– Joe Enos, Commented Aug 11, 2011 at 20:08
OK, hmm. When I was using the Express SKU my data source was a named thing, something like "data source=.\SQLEXPRESS;" - with the full-up SQL Server, my datasource now is something like "data source=localhost" - don't know if that is helpful at all, but my connection string was definitely different. Perhaps it would work with the named instance. Just a thought. — itsmatt
– itsmatt, Commented Aug 11, 2011 at 20:54

Slauma · Accepted Answer · 2011-08-11 20:21:31Z

3

Your derived DbContext class must have the same name as your connection string if you have only implemented a default constructor (or no constructor at all)

public PizzaByMeEntities : DbContext
{
    public PizzaByMeEntities()
    {
        // ...
    }
}

If your context has another name then you must specify the name of the connection string in the constructor:

public PizzaByMeContext : DbContext
{
    public PizzaByMeContext() :
        base("name=PizzaByMeEntities")
    {
        // ...
    }
}

Here are details about connections and connection strings with DbContext: http://blogs.msdn.com/b/adonet/archive/2011/01/27/using-dbcontext-in-ef-feature-ctp5-part-2-connections-and-models.aspx

Edit: Another guess

The connection string must be in the web.config of your MVC app, not in an app.config of a class library project where you possibly have your EF model and DbContext. Is it?

edited Aug 11, 2011 at 20:21

answered Aug 11, 2011 at 20:01

Slauma

177k60 gold badges407 silver badges424 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Slauma Over a year ago

@roman: Do you have your EF model and context in a separate project (class library?) in your solution? If yes, did you copy the connection string into the web.config of your MVC app?

Roman Over a year ago

No. they are both in the same project, so they share the connection string.

Roman Over a year ago

as to your question. it's definitely in the web.config - just where the EF put it by itself. strange thing this is.

Roman · Accepted Answer · 2011-08-11 21:24:15Z

0

Phewwww.... figured it out!!!

The Context object that the controller created (when you use new context) it created the object without the constructor that chooses the connection string. So it tried to use some non-existing connection string.

For conclusion - any object deriving from DBContext should use a constructor that would specify the connection string.

Thanks guys for helping me out :))

answered Aug 11, 2011 at 21:24

Roman

4,52114 gold badges60 silver badges81 bronze badges

1 Comment

Slauma Over a year ago

Huh? So, is there a second constructor in your context which takes one or more parameters? Did the DbContext generator create it or did you write it by hand?

Collectives™ on Stack Overflow

Entity framework 4.1 won't connect to SQL Server after using Database First approach

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related