SQL Server Express connection string for Entity Framework Code First

Question

I am working in Visual Web Developer 2010 Express, and using the Entity Framework code-first CTP. I am able to do this with the new SQL Server CE but I am unable to find a connection string to work with SQL Server Express.

This one, using the SQL Server CE beta, works fine (database is created and recreated on model changes).

<add name="TrempimModel"      connectionString="data source=|DataDirectory|TrempimModel.sdf"      providerName="System.Data.SqlServerCe.4.0" /> 

This one, which I copied from the aspnetdb connections string,

<add name="TrempimModel"      connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;      AttachDBFilename=|DataDirectory|TrempimModel.mdf;User Instance=true"      providerName="System.Data.SqlClient" /> 

Gives the following message on startup:

Unable to complete operation. The supplied SqlConnection does not specify an initial catalog

So how to make it work with SQL Server Express?

Answer 1

The problem with your connection string here is:

<add name="TrempimModel"      connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;                        AttachDBFilename=|DataDirectory|aspnetdb.sdf;                        User Instance=true"      providerName="System.Data.SqlClient" /> 

You're basically defining what "server" you're connecting to - but you're not saying what database inside the file to connect to. Also - the file extension for SQL Server Express database files is .mdf (not .sdf - that's SQL Server Compact Edition) - you need to take that into account, too! (was a typo, according to comment by OP).

You need to define an extra database=.... (or Initial Catalog=.....) in your connection string:

<add name="TrempimModel"      connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;                        database=YourDatabaseName;                        AttachDBFilename=|DataDirectory|aspnetdb.mdf;                        User Instance=true"      providerName="System.Data.SqlClient" /> 

Then it should work just fine.

For more background and tons of samples, check out the ConnectionStrings web site.