Working With Model in Entity Framework

In the previous post, I showed how to reversed-engineer an entity framework model from a Microsoft SQL Server 2012 database, and query the model in a C# console program. In the following, I will expand it further on how to work on the model generated to perform the standard CRUD (Create, Read, Update, Delete) operations.

Model

The model generated in the last post was in a file (testContext.cs) where the database context (testContext) was derived from DbContext, in which the property Employees was exposed as an entity (DbSet). In the simple RDBMS terms, the physical database (test) is mapped to DbContext (testContext) while a physical table (employees) was mapped to an entity DbSet (Employees). The entity model is like a conceptual model in the database design process.

Little notes on database design process
In a normal database design process, we design a conceptual model first by using designer like Visual Studio EF Designer or Oracle SQL developer/modeler, which will produce an ER-Diagram (Entity-Relationship Diagram) as a result. From the model, we can generate the physical model which is a set of DDLs, to run on a specific RDBMS like Oracle, MS SQL Server, etc. and produce a database.

In our previous example, we reversed the design process from bottom up, and generated the conceptual model into Entity Framework in Visual Studio from the physical database structures in Microsoft SQL Server (Database: test; Table: HR.Employees). This process is called reversed engineering.

namespace EmployeeConsole.Models
{
    public partial class testContext : DbContext
    {
        ......
        public DbSet<Employee> Employees { get; set; }
        ......
    }
}

Work on Model

To work on the model, the first is to include your model, then instantiate a dbContext so that the CRUD ops can be performed within the instance lifetime.

Here is the code snippets. Refer to last post for details.

using EmployeeConsole.Models;
...
      using (var db = new testContext())
          {
             // TODO YOUR CRUD Ops
          }

How to Read (Find) a Record

            using (var db = new testContext())
            {

                var myemp = db.Employees.Find(3);
                Console.WriteLine("Locate and Display empid=3");
                Console.WriteLine("EmpID\t{0}",myemp.empid);
                Console.WriteLine("FName\t{0}", myemp.firstname);
                Console.WriteLine("LName\t{0}", myemp.lastname);
                Console.WriteLine("HDate\t{0}", myemp.hiredate);
                Console.WriteLine("Phone\t{0}", myemp.phone);
                Console.WriteLine("Country\t{0}", myemp.country);
                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

How to Update a Record

            using (var db = new testContext())
            {

                // Find the record first
                var myemp = db.Employees.Find(3);
                Console.WriteLine("EmpID\t{0}",myemp.empid);
                Console.WriteLine("Firstname\t{0}", myemp.firstname);
                Console.WriteLine("Lastname\t{0}", myemp.lastname);

                // Update DbSet in memory
                myemp.lastname = "Smith";

                // Write to db
                db.SaveChanges();
                
                // db.SaveChanges is the same as the following RAW T-SQL
                //db.Database.ExecuteSqlCommand("update hr.employees set lastname='Smith' where empid=3");

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

How to Add a New Record

            using (var db = new testContext())
            {
                var newemp = new Employee();
                newemp.firstname = "Adam";
                newemp.lastname = "Smith";
                newemp.title="Economist";
                newemp.titleofcourtesy="";
                newemp.birthdate=Convert.ToDateTime("01/01/1980");
                newemp.hiredate=Convert.ToDateTime("01/01/2001");
                newemp.address="16 Bay Street";
                newemp.city="Toronto";
                newemp.region="Mr.";
                newemp.postalcode="B2C 2V7";
                newemp.country="CANADA";
                newemp.phone="(416) 822-2288";

                // Add to DbSet
                db.Employees.Add(newemp);
                // Write to db
                db.SaveChanges();

                // db.SaveChanges is the same as the following RAW T-SQL
                //db.Database.ExecuteSqlCommand("insert into hr.employees (..) values (...)");

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

How to Delete a Record

            using (var db = new testContext())
            {
                // identify the record first, (empid=10) is the new record inserted in last code snippet.
                var empToBeDeleted=db.Employees.Find(10);

                // remove from DbSet
                db.Employees.Remove(empToBeDeleted);

                // Write to db
                db.SaveChanges();

                // db.SaveChanges is the same as the following RAW T-SQL
                //db.Database.ExecuteSqlCommand("delete from hr.employees where empid=10");

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

Further Reading

  • Working with Entity Data from MSDN
  • Working with Objects
  • Working with Data (Entity Framework Tutorial)