How to Connect to SQL Server 2008 R2 Using EF and MVC 3

I am simply trying to learn MVC 3 and following a book from Wrox. Things were fine until I tried to execute a small exercise. I am getting an error which is very common. I have tried everything but could't find what the problem is. Can somebody help me out.

My configuration.

1. installed VS201 and then used WebPlatForm Installer to install SP1. Which did installed SP1 and installed MVC3 templates and framework as well on my machine. So far so good.

2. After that I installed SQL Server 2008 R2 Developer Edition, again so far so good.

I followed step by step the exercise and found an error which is becoming a nightmare for me. the error is mentioned below.

System.Data.ProviderIncompatibleException was unhandled by user code
  Message=The provider did not return a ProviderManifestToken string.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection)
       at System.Data.Entity.ModelConfiguration.Utilities.DbConnectionExtensions.GetProviderInfo(DbConnection connection, DbProviderManifest& providerManifest)
       at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
       at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
       at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
       at System.Data.Entity.Internal.Linq.InternalSet`1.Include(String path)
       at System.Data.Entity.Infrastructure.DbQuery`1.Include(String path)
       at System.Data.Entity.DbExtensions.Include[T](IQueryable`1 source, String path)
       at System.Data.Entity.DbExtensions.Include[T,TProperty](IQueryable`1 source, Expression`1 path)
       at MvcMusicStore.Controllers.StoreManagerController.Index() in c:\users\uk\documents\visual studio 2010\Projects\MvcMusicStore\MvcMusicStore\Controllers\StoreManagerController.cs:line 21
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
  InnerException: System.Data.SqlClient.SqlException
       Message=A network-related or instance-specific error occur开发者_高级运维red 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)
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=20
       LineNumber=0
       Number=-1
       Server=""
       State=0
       StackTrace:
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
            at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
            at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
            at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
            at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
            at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
            at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
            at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
            at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
            at System.Data.SqlClient.SqlConnection.Open()
            at System.Data.SqlClient.SqlProviderServices.UsingConnection(SqlConnection sqlConnection, Action`1 act)
            at System.Data.SqlClient.SqlProviderServices.UsingMasterConnection(SqlConnection sqlConnection, Action`1 act)
            at System.Data.SqlClient.SqlProviderServices.GetDbProviderManifestToken(DbConnection connection)
            at System.Data.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection)
       InnerException: 

As a solution. I have checked the SQL Server Browser Service is enabled. Also I executed the PortyQry.exe from Microsoft to find out if SQL Server is responding. and received the below response.

Attempting to resolve name to IP address...


Name resolved to x.x.x.x

querying...

UDP port 1434 (ms-sql-m service): LISTENING or

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName MVC3Machine
InstanceName MSSQLSERVER
IsClustered No
Version 10.50.1600.1
tcp 1433

 ♦  ♦

==== End of SQL Server query response ====

Also here is the connection string

<add name="MusicStoreDB" connectionString="Server=(local); Database=MusicStore; Trusted_Connection=true; Integrated Security=True" providerName="System.Data.SqlClient" />

Edit1: I am not using any database to build my Model. I am trying to use Code First approach of EF.

Edit 2:

using System.Data.Entity;

namespace MvcMusicStore.Models
{
    public class MvcMusicStoreContext : DbContext
    {
        // You can add custom code to this file. Changes will not be overwritten.
        // 
        // If you want Entity Framework to drop and regenerate your database
        // automatically whenever you change your model schema, add the following
        // code to the Application_Start method in your Global.asax file.
        // Note: this will destroy and re-create your database with every model change.
        // 
        // System.Data.Entity.Database.SetInitializer(new System.Data.Entity.DropCreateDatabaseIfModelChanges<MvcMusicStore.Models.MvcMusicStoreContext>());

        public DbSet<Album> Albums { get; set; }

        public DbSet<Genre> Genres { get; set; }

        public DbSet<Artist> Artists { get; set; }
    }
}

Anybody can suggest me something to try...

Thanks

---

just replace your conn string with this and if you have copied MvcMusicStore.mdf files delete those from App_Data folder hope it will work

<clear />

<add name="MusicStoreEntities" connectionString="data source=.\SQLEXPRESS1;Integrated Security=SSPI;Initial Catalog=MvcMusicStore;AttachDBFilename=|DataDirectory|MvcMusicStore.mdf;User Instance=true" providerName="System.Data.SqlClient" />

---

By default, EFCF will try to make a database from your model on your local ./SQLEXPRESS If you want to specify a different connection string in your web.config you have to tell EF the name of your connection string in the constructor of your database context like below:

public class MvcMusicStoreContext: DbContext 
{
    //Add the name of your database connection to the base DbContext calss
    public MvcMusicStoreContext() : base("MusicStoreDB")
    {}

    public DbSet<Album> Albums { get; set; }

    public DbSet<Genre> Genres { get; set; }

    public DbSet<Artist> Artists { get; set; }
}

Your connection string:

<add name="MusicStoreDB" connectionString="Data Source=localhost;Initial Catalog=MusicStoreDB;Integrated Security=True" providerName="System.Data.SqlClient"/>

---

Your connection string for EF needs to be an EntityConnection Connection String.

Check your app.config in the project where you created the Entity Framework edmx (if you used a database to create the model), and a connection string should be in there.

Here is some sample code to create the EF Connection String:

efName is the name of your edmx file. Example: Test.edmx - efName = "Test"

    private static string GetEntityFrameworkConnectionString(string connectionString, string efName)
    {
        var entityBuilder = new EntityConnectionStringBuilder();
        entityBuilder.Provider = "System.Data.SqlClient";
        entityBuilder.ProviderConnectionString = connectionString;
        entityBuilder.Metadata = "res://*/" + efName + ".csdl|res://*/" + efName + ".ssdl|res://*/" + efName + ".msl";
        return entityBuilder.ToString();
    }

---

For SQL Server 2008 R2 Developer Edition you can use the following connection stirng for your pc
<add name="MvcMusicStoreContext" connectionString="Data Source=MVC3Machine; Initial Catalog=MusicStoreDB; Integrated Security=SSPI;" providerName="System.Data.SqlClient" />