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)
  • Generate model from database using Entity Framework Power Tool

    The software I am using are:

    • Microsoft Visual Studion 2013 Premium
    • Microsoft SQL Server 2012 Enterprise
    • Entity Framework 6.0.1
    • Entity Framework Power Tools 1.0
    • Windows Server 2008 R2

    Create Database Objects

    I created a test database with a table HR.Employees as the following in my local Microsoft SQL 2012 instance (full T-SQL script is here):

    CREATE TABLE HR.Employees
    (
      empid           INT          NOT NULL IDENTITY,
      lastname        NVARCHAR(20) NOT NULL,
      firstname       NVARCHAR(10) NOT NULL,
      title           NVARCHAR(30) NOT NULL,
      titleofcourtesy NVARCHAR(25) NOT NULL,
      birthdate       DATETIME     NOT NULL,
      hiredate        DATETIME     NOT NULL,
      address         NVARCHAR(60) NOT NULL,
      city            NVARCHAR(15) NOT NULL,
      region          NVARCHAR(15) NULL,
      postalcode      NVARCHAR(10) NULL,
      country         NVARCHAR(15) NOT NULL,
      phone           NVARCHAR(24) NOT NULL,
      mgrid           INT          NULL,
      CONSTRAINT PK_Employees PRIMARY KEY(empid),
      CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid)
        REFERENCES HR.Employees(empid),
      CONSTRAINT CHK_birthdate CHECK(birthdate <= CURRENT_TIMESTAMP)
    );
    

    Reversed Engineering DB Objects to Classes

    I created a simple C# console project called ‘EmployeeConsole’ in visual studio, which will create two files: Program.cs and App.config. Right Click on the project in Solution Explorer, and choose ‘Entity Framework – Reversed Engineering Code First’. Then connect to the instance (local) and database (test), and generate the model from test database for table ‘HR.Employees’.

    What Does Entity Framework Power Tool Do?

    1. Visual Studio first will install Entity Framework (runtime) into the project by creating packages.config as the following:

    <?xml version="1.0" encoding="utf-8"?>
    <packages>
      <package id="EntityFramework" version="6.0.1" targetFramework="net45" />
    </packages>
    

    2. Create model: a database context (testContext.cs)

    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using EmployeeConsole.Models.Mapping;
    
    namespace EmployeeConsole.Models
    {
        public partial class testContext : DbContext
        {
            static testContext()
            {
                Database.SetInitializer(null);
            }
    
            public testContext()
                : base("Name=testContext")
            {
            }
    
            public DbSet Employees { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new EmployeeMap());
            }
        }
    }
    

    3. Create model: a employees class in the model (Employee.cs)

    using System;
    using System.Collections.Generic;
    
    namespace EmployeeConsole.Models
    {
        public partial class Employee
        {
            public Employee()
            {
                this.Employees1 = new List();
            }
    
            public int empid { get; set; }
            public string lastname { get; set; }
            public string firstname { get; set; }
            public string title { get; set; }
            public string titleofcourtesy { get; set; }
            public System.DateTime birthdate { get; set; }
            public System.DateTime hiredate { get; set; }
            public string address { get; set; }
            public string city { get; set; }
            public string region { get; set; }
            public string postalcode { get; set; }
            public string country { get; set; }
            public string phone { get; set; }
            public Nullable mgrid { get; set; }
            public virtual ICollection Employees1 { get; set; }
            public virtual Employee Employee1 { get; set; }
        }
    }
    

    4. Create ORM: mapping POJO Employees class to physical table (EmployeeMap.cs) using Fluent API

    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.ModelConfiguration;
    
    namespace EmployeeConsole.Models.Mapping
    {
        public class EmployeeMap : EntityTypeConfiguration
        {
            public EmployeeMap()
            {
                // Primary Key
                this.HasKey(t => t.empid);
    
                // Properties
                this.Property(t => t.lastname)
                    .IsRequired()
                    .HasMaxLength(20);
    
                this.Property(t => t.firstname)
                    .IsRequired()
                    .HasMaxLength(10);
    
                this.Property(t => t.title)
                    .IsRequired()
                    .HasMaxLength(30);
    
                this.Property(t => t.titleofcourtesy)
                    .IsRequired()
                    .HasMaxLength(25);
    
                this.Property(t => t.address)
                    .IsRequired()
                    .HasMaxLength(60);
    
                this.Property(t => t.city)
                    .IsRequired()
                    .HasMaxLength(15);
    
                this.Property(t => t.region)
                    .HasMaxLength(15);
    
                this.Property(t => t.postalcode)
                    .HasMaxLength(10);
    
                this.Property(t => t.country)
                    .IsRequired()
                    .HasMaxLength(15);
    
                this.Property(t => t.phone)
                    .IsRequired()
                    .HasMaxLength(24);
    
                // Table & Column Mappings
                this.ToTable("Employees", "HR");
                this.Property(t => t.empid).HasColumnName("empid");
                this.Property(t => t.lastname).HasColumnName("lastname");
                this.Property(t => t.firstname).HasColumnName("firstname");
                this.Property(t => t.title).HasColumnName("title");
                this.Property(t => t.titleofcourtesy).HasColumnName("titleofcourtesy");
                this.Property(t => t.birthdate).HasColumnName("birthdate");
                this.Property(t => t.hiredate).HasColumnName("hiredate");
                this.Property(t => t.address).HasColumnName("address");
                this.Property(t => t.city).HasColumnName("city");
                this.Property(t => t.region).HasColumnName("region");
                this.Property(t => t.postalcode).HasColumnName("postalcode");
                this.Property(t => t.country).HasColumnName("country");
                this.Property(t => t.phone).HasColumnName("phone");
                this.Property(t => t.mgrid).HasColumnName("mgrid");
    
                // Relationships
                this.HasOptional(t => t.Employee1)
                    .WithMany(t => t.Employees1)
                    .HasForeignKey(d => d.mgrid);
    
            }
        }
    }
    

    5. Update App.config to include entity framework config and SQL connection string.

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
      </configSections>
      <connectionStrings>
        <add name="testContext" connectionString="Data Source=(local);Initial Catalog=test;Integrated Security=True;MultipleActiveResultSets=True"
          providerName="System.Data.SqlClient" />
      </connectionStrings>
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
      </startup>
      <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="v11.0" />
          </parameters>
        </defaultConnectionFactory>
        <providers>
          <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        </providers>
      </entityFramework>
    </configuration>
    

    Utilize your model

    The following is a simple program to display a list of employee records by using EmployeeConsole.Models which was defined in testContext.cs.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using EmployeeConsole.Models;
    
    namespace EmployeeConsole
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                using (var db = new testContext())
                {
                    // Get all the rows into var query by using LINQ if you want
                    //var query = from el in db.Employees orderby el.firstname select el;
    
                    // Get all the rows into var query by using SQL
                    var query = db.Employees.SqlQuery("select * from hr.employees");
    
                    // Display Listing Header
                    Console.WriteLine("LIST OF EMPLOYEES WITH PHONES:");
    
                    // just display firstnamelastnamephone
                    foreach (var item in query)
                    {
                        Console.WriteLine("{0}\t{1}\t{2}", item.firstname, item.lastname, item.phone);
                    }
    
                    Console.WriteLine("Press any key to exit...");
                    Console.ReadKey();
                }
            }
        }
    }
    

    Limitation on Processing Raw T-SQL queries

    What if when the SQL query changed to the following:

                select * from employees
                select firstname,lastname,phone from hr.employees
    

    Both are valid T-SQL queries, they won’t work from the model generated by Entity Framework because the model mapping has the following:

                this.ToTable("Employees", "HR");
                this.Property(t => t.empid).HasColumnName("empid");
    

    The execution will complain either no schema object ’employees’ in the first case or requiring ’empid’ in the latter.

    To use the model generated, it’s better to use either LINQ-TO-ENTITY or ENTITY-SQL. There are differences between Entity SQL and T-SQL.

    Further Reading: ADO.NET Entity Framework & Entity SQL

    T-SQL: OVER clause

    OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

    The following is the script to show the usage of OVER clause (more in my T-SQL Script List).

    -- Step 1: Open a new query window to the TSQL2012 database
    USE TSQL2012;
    GO
    
    -- Step 2: Creating Views for OVER clause
    
    -- Setup views for demo
    IF OBJECT_ID('Production.CategorizedProducts','V') IS NOT NULL DROP VIEW Production.CategorizedProducts
    GO
    CREATE VIEW Production.CategorizedProducts
    AS
        SELECT  Production.Categories.categoryid AS CatID,
    			Production.Categories.categoryname AS CatName,
                Production.Products.productname AS ProdName,
                Production.Products.unitprice AS UnitPrice
        FROM    Production.Categories
                INNER JOIN Production.Products ON Production.Categories.categoryid=Production.Products.categoryid;
    GO
    IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
    GO
    CREATE VIEW Sales.CategoryQtyYear
    AS
    SELECT  c.categoryname AS Category,
            SUM(od.qty) AS Qty,
            YEAR(o.orderdate) AS Orderyear
    FROM    Production.Categories AS c
            INNER JOIN Production.Products AS p ON c.categoryid=p.categoryid
            INNER JOIN Sales.OrderDetails AS od ON p.productid=od.productid
            INNER JOIN Sales.Orders AS o ON od.orderid=o.orderid
    GROUP BY c.categoryname, YEAR(o.orderdate);
    GO
    
    -- Step 3: Using OVER with ordering
    -- Rank products by price from high to low
    SELECT CatID, CatName, ProdName, UnitPrice,
    	RANK() OVER(ORDER BY UnitPrice DESC) AS PriceRank
    FROM Production.CategorizedProducts
    ORDER BY PriceRank;
    
    -- Rank products by price in descending order in each category.
    -- Note the ties.
    SELECT CatID, CatName, ProdName, UnitPrice,
    	RANK() OVER(PARTITION BY CatID ORDER BY UnitPrice DESC) AS PriceRank
    FROM Production.CategorizedProducts
    ORDER BY CatID;
    
    -- Step 4: Use framing to create running total
    -- Display a running total of quantity per product category.
    -- This uses framing to set boundaries at the start
    -- of the set and the current row, for each partition
    SELECT Category, Qty, Orderyear,
    	SUM(Qty) OVER (
    		PARTITION BY category
    		ORDER BY orderyear
    		ROWS BETWEEN UNBOUNDED PRECEDING
    		AND CURRENT ROW) AS RunningQty
    FROM Sales.CategoryQtyYear;
    
    -- Display a running total of quantity per year.
    SELECT Category, Qty, Orderyear,
    	SUM(Qty) OVER (
    		PARTITION BY orderyear
    		ORDER BY Category
    		ROWS BETWEEN UNBOUNDED PRECEDING
    		AND CURRENT ROW) AS RunningQty
    FROM Sales.CategoryQtyYear;
    
    -- Show both side-by-side per category and per-year
    
    SELECT Category, Qty, Orderyear,
    	SUM(Qty) OVER (PARTITION BY orderyear ORDER BY Category	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalByYear,
    	SUM(Qty) OVER (PARTITION BY Category ORDER BY OrderYear	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalByCategory
    FROM Sales.CategoryQtyYear
    ORDER BY Orderyear, Category;
    
    -- Step 5: Clean up
    IF OBJECT_ID('Production.CategorizedProducts','V') IS NOT NULL DROP VIEW Production.CategorizedProducts
    IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
    GO
    

    T-SQL: Common Table Expression

    A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

    A CTE can be used to create a recursive query, substitute a view etc. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

    The following are some of testing examples. (see my T-SQL script List for other examples):

    -- Step 1: Open a new query window to the TSQL2012 database
    USE TSQL2012;
    GO
    -- Step 2: Common Table Expressions
    -- -- Select this query and execute it to show CTE Examples
    WITH CTE_year AS
    	(
    	SELECT YEAR(orderdate) AS orderyear, custid
    	FROM Sales.Orders
    	)
    SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
    FROM CTE_year
    GROUP BY orderyear;
    
    -- Step 3 Recursive CTE 
    WITH EmpOrg_CTE AS
    (SELECT empid, mgrid, lastname, firstname --anchor query
    	FROM HR.Employees
    WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees
    
    UNION ALL
    SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
    	FROM EmpOrg_CTE AS parent
    	JOIN HR.Employees AS child
    	ON child.mgrid=parent.empid
    )
    SELECT empid, mgrid, lastname, firstname
    FROM EmpOrg_CTE;
    

    T-SQL: ISNULL, COALESCE, and NULLIF

    The following test demonstartes the usage of ISNULL, COALESCE, and NULLIF of T-SQL. For the complete list of my other tests and the creation scripts of TSQL2012 database, please refer to my T-SQL Script List.

    -- Step 1: Open a new query window to the TSQL2012 database
    USE TSQL2012;
    GO
    
    -- Step 2: Select and execute the following query to illustrate
    -- The ISNULL function
    SELECT custid, city, ISNULL(region, 'N/A') AS region, country
    FROM Sales.Customers;
    
    -- Step 3: Select and execute the following query to illustrate the
    -- COALESCE function
    SELECT custid, country, region, city,
    country + ',' + COALESCE(region, ' ') + ', ' + city as location
    FROM Sales.Customers;
    
    -- Step 4: Select and execute the following queries to illustrate the
    -- NULLIF function
    
    -- First, set up sample data
    CREATE TABLE dbo.employee_goals(emp_id INT , goal int, actual int);
    
    GO
    -- Populate the sample data
    INSERT INTO dbo.employee_goals
    VALUES(1,100, 110), (2,90, 90), (3,100, 90), (4,100, 80);
    
    -- Show the sample data
    SELECT emp_id, goal, actual
    FROM dbo.employee_goals;
    
    -- Use NULLIF to show which employees have actual
    -- values different from their goals
    SELECT emp_id, NULLIF(actual,goal) AS actual_if_different
    FROM dbo.employee_goals;
    
    -- Step 5: Clean up demo table
    DROP TABLE dbo.employee_goals;