Using Entity framework with SQL Server 2012 (local *.mdf database)

Question

I am trying to create a database in an MVC project. Code-First approach. I wrote my models and a DbContext database. I am having trouble making the connection to the database. My suspect that the problem is in my connection string but I am not sure. the database I want will consist of two tables, which are defined as follows:

public class EmployeeRequest
{
    [Key]
    public int EmployeeRequestId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string UserName { get; set; }
    public int ExtNumber { get; set; }
    public bool isProcessed { get; set; }
    public virtual ICollection<ChangeOrder> ChangeOrders { get; set; }
}

and

public class ChangeOrder
{
    [Key]
    public int ChangeOrderId { get; set; }
    public short Operation { get; set; } //0 - Add, 1- delete
    public int TargetExt { get; set; }
    public short Status { get; set; } //0- Pending, 1- Approved, 2- Denied
    public DateTime DtRequested { get; set; }
    public DateTime DtProcessed { get; set; }
    public DateTime DtChangesApplied { get; set; }
    public int EmployeeRequestID { get; set; }
    public virtual EmployeeRequest Request { get; set; }
 }

The DbContext class is:

public class RequestsContext : DbContext
{
    public DbSet<EmployeeRequest> EmployeeRequests { get; set; }
    public DbSet<ChangeOrder> ChangeOrders { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

Connection string:

<add name="RequestsContext" 
     providerName="System.Data.SqlClient" 
     connectionString="Data Source=(LocalDb);Database=Requests.mdf;Trusted_Connection=True"/>

The error I get says that the connection could not be made to the host.

I am nor sure why this is happening. Any Ideas?

As crazy as this sounds, try changing your Data Source to be `(localdb)\v11.0` instead of just `(localdb)`. — Steven V, Jul 29 '13 at 20:39
ok those parameters have fixed something, but now I get an error saying "Cannot attache the file path\to\App_Data\Requests.mdf as database 'Requests.mdf" — laitha0, Jul 29 '13 at 20:42
That's an interesting one. Try changing the Database setting from `Requests.mdf` to `Requests` and see if that does something. — Steven V, Jul 29 '13 at 20:44
using this connectionString="Data Source=(localdb)\v11.0;Database=Requests.mdf" a credential form pops up, with a server field set as (localdb)\v11.0, Database set as Requests.mdf, and it asks me to either use windows authentication or SQL Server authentication. I tried both but neither is working. Nothing changes if I use Requests instead of Requests.mdf — laitha0, Jul 29 '13 at 20:49

score 1 · Answer 1 · answered Jul 29 '13 at 20:54

1

Give the connection string to the DbContext base.

public class RequestsContext : DbContext
{
    public DbSet<EmployeeRequest> EmployeeRequests { get; set; }
    public DbSet<ChangeOrder> ChangeOrders { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }

    public RequestsContext ()
    : base("RequestsContext")
    {}

}

answered Jul 29 '13 at 20:54

Martijn van Put

3,293
18
17

This made it stop complaining about credintials, but still no *.mdf file in App_data. What do I need to do to generate that. I already generated two controllers for the models and that went fine this time – laitha0 Jul 29 '13 at 20:58
When you use a .mdf the connection string is wrong. Remove .mdf or use the AttachDbFilename property. Check the connection string for database file attaching on: http://www.connectionstrings.com/sql-server-2012 – Martijn van Put Jul 29 '13 at 21:00
ill try that once I deal with the routing problem that I just discovered!! – laitha0 Jul 29 '13 at 21:08
Yes, I am not sure what solved it exactly but it worked when I did this http://stackoverflow.com/questions/11278114/enable-remote-connections-for-sql-server-express-2012. check out the solution with the most votes. Also you need to enable NETWORK SERVICES from IIS management tool – laitha0 Jul 30 '13 at 18:41
Glad it works, but what connection string are you using? without or with .mdf? – Martijn van Put Jul 30 '13 at 19:11
I couldnt get it to work with .mdf, I am using SqlServer Compact for now, Ill figure out how to convert .sdf to .mdf when I move it to production, but at least I have something working now for development. – laitha0 Jul 30 '13 at 19:28
also make sure you have teh appropriate defaultConnectionfactory and – laitha0 Jul 30 '13 at 19:29
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/34485/discussion-between-martijn-van-put-and-user2247823) – Martijn van Put Jul 30 '13 at 19:30

score 1 · Answer 2 · answered Jul 29 '13 at 20:55

1

Please check the application has access to .mdf file location. If your application resides in "myDocuments" etc type of folders, the application may not have access to it by default.

answered Jul 29 '13 at 20:55

testuser

668
10
23

I still do not have the *.mdf file, From what I understood I believe that it should generate that for me when everything is good – laitha0 Jul 29 '13 at 20:56

Using Entity framework with SQL Server 2012 (local *.mdf database)

2 Answers2

Linked