Code first with the existing DB

Question

I am fairly new to Entity Framework and MVC. I can use the Entity Data Model Wizard (EDMW) on an existing SQL server database and automatically create the necessary classes (context, models,etc). Then pass the model (db.Employees.tolist()) to a view from the controller. Everything works fine and I can see the list of all the employees on the browser from the company.employees table.

However, I want to write the code myself without the help of EDMW or other tools to better understand the entity/MVC/database interactions. So I wrote the following code but can't seem to get the table displayed on the browser. I know I am missing something.

SQL SERVER: Schema.tablename (Acme.Employees)

public class AcmeContext : DbContext 
{   
   public virtual DbSet<Employee> Employees { get; set; } 
}

public class Employee 
{
   public int customerid {get; set;}
   public string name {get;set;}
   public string state {get;set;}
}


public ActionResult list()
{
   var db = new AcmeContext();
   return view(db.Employees.tolist());
}

<connectionStrings> 
  <add  
    name="AcmeContext"  
    connectionString="data source=localhost;initial catalog=Acme;integrated security=True;"  
    providerName="System.Data.SqlClient" /> 
</connectionStrings>

What else is needed?

Also, how is this done in the real world when the DB already exists? Does the developer use the EDM wizard or manually code everything the wizard does in Visual Studio or other IDE?

a few things. You have no employees added, your database would be empty. You should declare a `[Key]` on your POCO. What error are you getting? — Jonesopolis, Sep 05 '14 at 02:46
@Jonesy where would I add the employees? I have the [Key] attribute above the customerid but the code snippet above doesn't show it. I actually don't see any errors. The browser renders the view with nothing. — bnsffan, Sep 05 '14 at 03:22

score 0 · Accepted Answer · answered Sep 05 '14 at 04:01

By default when you are using Entity Framework code first, the schema is dbo you can change schema by using using fluent API or Data Anotation

Fluent API :

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>().ToTable("Employees", "Acme");
}

Data Anotations

[Table("Employees", Schema = "Acme")]
public class Employee 
{
    ...
}

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

You don't have a construction calling your connection string

public class AcmeContext : DbContext {

public AcmeContext() :base(nameOrConnectionString: "AcmeContext"){}

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

public virtual DbSet Employees { get; set; } }

Your question already answered here. Please refer to this topic Code-first vs Model/Database-first

edited May 23 '17 at 11:49

Community

1
1

answered Sep 05 '14 at 04:31

bl2b

123
4

I thought the EF selects the correct connectionstring based on "name=AcmeContext" in the web.config, AcmeContext being the class name. I will try your suggestion though. – bnsffan Sep 05 '14 at 14:24

score 0 · Answer 3 · answered Sep 07 '14 at 06:50

I solved my problem.

The EF created a table dbo.employees instead of using the acme.employees. All along it was looking at the employee table in the dbo schema. Of course there is no data in that table and thus the blank web page. I added some data and now can see it on the page.

What I still don't know is the data annotation that Aiska Hendra suggested didn't work earlier.

Code first with the existing DB

3 Answers3