How can I force Entity Framework code-first to use SQL Server CE when SQL Server Express is installed?

Question

I would like to use EF code first with SQL Server CE 4 from an ASP.NET MVC project but instead of my database being created in the App_Data folder, I get a new database in the SQL Server Express instance installed on my workstation.

How can I get the DB created in the App_Data folder instead?

web.config:

 <connectionStrings>
    <add name="NerdDinners" connectionString="Data Source=|DataDirectory|NerdDinners.sdf" 
         providerName="System.Data.SqlServerCe.4.0"/>
  </connectionStrings>

Context class:

public class NerdDinners : DbContext
{
    public DbSet<Dinner> Dinners { get; set; }
    public DbSet<RSVP> RSVPs { get; set; }
}

Edit 1
I tried stopping SQL Server Express thinking that might force the use of SQL Server CE but it resulted in a ProviderIncompatibleException with an inner exception of

{"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)"}

So it seems my setup wants to use SQL Server Express.

Edit 2 It was suggested that I explicitly pass the connection string name to the DbContext so I changed my context class to the following:

  public class NerdDinners : DbContext
    {
        public NerdDinners() : base("NerdDinners") { }

        public DbSet<Dinner> Dinners { get; set; }
        public DbSet<RSVP> RSVPs { get; set; }
    }

In the DinnersController, this is how the context is instantiated:

public class DinnersController : Controller
    {
        private NerdDinners db = new NerdDinners();

        //
        // GET: /Dinners/

        public ViewResult Index()
        {
            return View(db.Dinners.ToList());
        }

After making these changes, the DB is still being created in SQL Server Express so it appears that my connection string is being ignored.

you have it configured as above and don't get the sdf file appearing in bin/debug? — Adam Tuliper
– Adam Tuliper, Commented Mar 1, 2012 at 20:47
@Adam Tuliper - That is correct. I get a new DB in my local instance of SQL Server Express. — DaveB
– DaveB, Commented Mar 1, 2012 at 20:51
no other connect strings at all? also is sqlce v 4 installed? — Adam Tuliper
– Adam Tuliper, Commented Mar 1, 2012 at 20:53
@Adam Tuliper - Correct, no other connection strings. I have "System.Data.SqlServerCe.dll", "System.Data.SqlServerCe.Entity.dll" and "amd64" and "x86" subirectories in my bin folder. — DaveB
– DaveB, Commented Mar 1, 2012 at 21:00
you dont have to initialize it with the connect string HOWEVER - you do have to stick to a naming convention. It must match the name of your context class. If the context class is in another project, then you need to use the full namespace of it. If this is all in the same project, you do not have to specify the connect string name and it will default. — Adam Tuliper
– Adam Tuliper, Commented Mar 1, 2012 at 21:44

DaveB · Accepted Answer · 2012-03-06 00:50:55Z

1

There are TWO web.config files in ASP.NET MVC. The connection string needs to go in the one off the root of the site and not the one in the views directory (where I had mine). Once I put the connection string in the web.config in the root of the project my SQL Server CE database was created using code first and working fine!

answered Mar 6, 2012 at 0:50

DaveB

9,5584 gold badges43 silver badges68 bronze badges

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

1 Comment

Adam Tuliper Over a year ago

didnt even think you would've added that there as its pretty much there to configure whats allowed for your views only. that would explain why it wouldnt find it!! : )

Adam Tuliper · Accepted Answer · 2012-03-01 23:34:03Z

0

Download EFProfiler's trial, see if you can see the name of the connect string.

Ensure the rules above are followed. If you want to send me a sample project, I'll check it out for you but mine works ok not specifying a connect string and using sql ce. Download my demo project at the link above - did it work? If not I'd question the CE binaries.

Is your context class in the same project? Is your connect string named the same as your context class (it should be)

In other words, you your web.config you have NerdDinners, it your context class actually named NerdDinners, if not it needs to be or change the id in the connect string.

answered Mar 1, 2012 at 23:34

Adam Tuliper

30.2k4 gold badges56 silver badges71 bronze badges

Collectives™ on Stack Overflow

How can I force Entity Framework code-first to use SQL Server CE when SQL Server Express is installed?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related