Database: Read, Modify, Write Pattern

Applies: When updating a record or item in a database

1. Read item, remember the version number of the record (Vo)
2. Validate if the update is allowed
3. Update the record, and increment the version number (Vi)
4. Replace the record in the data store only if the version in the data store is Vo
5. If update fails, go to step 1

Entity State Management by Entity Framework's DbContext

DbContext is used to query from a database and group together changes that will then be written back to the store as a unit.

The general pattern of usage is:

using (ctx = new MyDbContext())
{
    // manipulate domain/model objects
    // issue queries
    ctx.SaveChanges();   // if making changes to db (i.e. not just querying)
}

DbContext tracks state of entities and applies appropriate changes when SaveChanges() is called.

Let's say we have Customer class (POCO, domain/model object) that maps to a Customer table.

In the MyDbContext, we make the context aware of Customer.

public IDbSet<Customer> Customers {get; set; }

To add a new Customer

var customer = new Customer();
ctx.Entry(customer).State = EntityState.Added;
ctx.SaveChanges();

Lookup and update a Customer

var existingCustomer = ctx.Customers.Find(customerId);
existingCustomer.Zip = 22222;
ctx.SaveChanges();

Lookup and update a Customer using an untracked object

var existingCustomer = ctx.Customers.Find(customerId);
passedInUntrackedCustomer.Id = existingCustomer.Id;
ctx.Entry(existingCustomer ).State = EntityState.Detached;
ctx.Entry(passedInUntrackedCustomer).State = EntityState.Modified;
ctx.SaveChanges();

Start tracking an untracked Customer object

ctx.Entry(passedInUntrackedCustomer).State = EntityState.Unchanged;
// any changes to passedInUntrackedCustomer from this point
// will be saved
ctx.SaveChanges();

Update a Customer without looking up first

var existingCustomer = new Customer() { Id = customerId };
ctx.Entry(existingCustomer).State = EntityState.Modified;
ctx.SaveChanges();

Lookup, update a Customer attribute but then cancel changes

var existingCustomer = ctx.Customers.Find(customerId);
existingCustomer.Zip = 22222;
ctx.Entry(existingCustomer).State = EntityState.Unchanged;
ctx.SaveChanges();  // does not save changes to Zip

Delete a Customer

var existingCustomer = new Customer() { Id = customerId };
ctx.Entry(existingCustomer).State = EntityState.Deleted;
ctx.SaveChanges();

Manipulate Navigation Property or Navigation Collection property

Just like we did above, through the Customer object we can manipulate states of the entities referenced from Customer. For example, we can add a new Category to the Category lookup table while at the same time reference that Category (reference navigation property) from Customer. Similarly, if the Customer has one to many relationship with Addresses, we can add a new Address to the Addresses attribute (collection navigation property) of Customer and have EF store that into the Addresses table.

Using Entity Framework with pre-existing database tables

Given

• A pre-existing database called Inventory
• A table named Product in the database

Objective

Access the Product table using entity framework

Steps

1. Create a console application
2. Install EntityFramework package using Nuget
3. Add the connection string to App.config

<connectionStrings>

    <add name="InventoryDbContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=Inventory;Integrated Security=True;Pooling=False"

         providerName ="System.Data.SqlClient"/>

  </connectionStrings>

Code

// Class for accessing the rows in the table

// EF would normally look for "Products" table

// Tell EF to look for "Product" table

[Table("Product")]      

public class Product

{

  public int Id { get; set; }

  public string PartNumber { get; set; }

  public string PartName { get; set; }

}

// database context for access

public class InventoryDbContext : DbContext

{

  static InventoryDbContext()

  {

    // Turn off EF's database initialization (our database is pre-existing)

    Database.SetInitializer<InventoryDbContext>(null);

  }

  // Set with access to rows in the Product table

  public IDbSet<Product> Products { get; set; }

}

class Program

{

  

  static void Main(string[] args)

  {

    using (var ctx = new InventoryDbContext())

    {

      var products = ctx.Products.ToList();

      products.ForEach(p => 

        Console.WriteLine("{0}: {1}, {2}", p.Id, p.PartNumber, p.PartName));

    }

    Console.ReadKey();

  }

}

Output

1: 0001, Widget
2: 0002, Binder
3: 0003, Marker

Entity Framework: Customizations

Fix up a POCO upon load by EF

Implement handler for DbContext.ObjectMaterialized Event

Entity Framework: Optimizations

Inspect Queries

Use SQL Profiler to see the queries executed by Entity Framework. Optimize DbContext and LINQ queries.

Bulk Updates and Queries

Entity framework can be slow for bulk updates and query. To optimize:
1) Turn off Auto Detect Changes property on DbContext (Can explicitly let EF know what has changed). EF can tie Sprocs to insert, update, delete (instead of generating and executing SQL)
2) Referenced (foreign key) object via an Id property, instead of navigation property (a full instance of referenced object).
3) Do the optimization on the DB Server (views, stored procs, ...)

Connection Resiliency

• Retry connection upon transient connection failure
• Use EF class DbExecution strategy: SqlAzureExecutionStrategy, DefaulSqlExecutionStrategy (does nothing). Retry n times, specify max retry period.
• Configured via DbConfiguration-derived class

Entity Framework: CRUD

Unit of Work

DbContext is used to create a Unit of Work. DbContext keeps track of changes in the unit of work and performs the DB updates transactionally.

using (var ctx = new DbContext())
{
   // Unit of Work
   // EF can track changes via proxies (proxy knows when object changed).
   // Proxy auto gen by EF that looks data object.
   // Used to track changes on a field by field basis
   // .SaveChanges() checks changes and executes appropriate SQL against the DB

}

Storage: Entity Framework - Leveraging EF in a multi-tiered enterprise application

EF is not a data access layer but rather a tool that helps build a data access layer with less code. The following diagram depicts how EF can be utilized in a multi-tiered enterprise application. Each box can be thought of a separate layer/tier (or at a minimum, a separate Visual Studio project).

• Repository creates and uses EF DbContext. It may create a new DbContext for each request (disconnected client) or create, keep and use a singleton instance (connected client) of a DbContext . DbContext tracks changes. If changes are made to POCOs that are not monitored by DbContext (via DBSet), EF must be apprised of these changes via the EF API. If the domain is large, multiple domain-specific Repositories may be implemented.
• Repository abstracts the Storage API. Behind the scenes, it can use a persistence scheme other than EF, if it decides to do so later. DTOs (Data Transfer Objects) are persistence-technology independent data structures (POCOs) that shuttle the data between the Domain Model and the Repository.
• Domain Model uses the Storage API exposed by the Repository. The Repository can be "mocked" to facilitate unit testing of the Domain Model without having to create a real SQL Server database.
• Domain Model has its own set of POCOs that form the Data Contracts for the application. These are not the same as DTOs (we should be able to change the DTOs without breaking the Application API). The Domain Model exposes its functionality through a well-defined API and Data Contracts.
• Clients (Background daemon, WPF, ASP.NET,...) use the Domain Model and are decoupled from the SQL Server Database, EF, Repository, and DTOs. The clients are only dependent on the Data Contracts and Application API exposed by the Domain Model.

Entity Framework: Code and Database Separate Approach

The use of EF designers is optional. You can totally design your database and code separately and independently. You are not required to use any EF designers to reap most of the benefits of the Entity Framework. Here are the general steps for  the Code and Database Separate Approach.

1. Database: Create and optimize your database as you would normally.
2. .NET: Create POCO classes (not interfaces) that correspond to database objects. Foreign key references become collections of referenced objects in the POCO instance.
3. Create a DBContext derived class. Add DBSet<POCO_EntityType> members to DBContext for each DB table/view.
4. Use methods of DBContext to hydrate DBSets and perform LINQ queries (rich, intuitive, composable, and type safe; paging is supported via Skip and Take) against the DBSets. EF generates and executes appropriate SQL against the database.
5. You can provide hints to EF, if the default (convention-based) mapping of POCOs to DB objects used by EF is not what you expect. This can be done via attributes (data annotations) or via code.
6. If you modify the database, simply make corresponding changes to your POCO classes.

Quick Start

If you already have a database and want to create POCO classes from the database, use the Entity Framework Power Tools. Once POCO classes are created fix up the namespaces, move the classes to appropriate project, and make future changes manually, in a controlled fashion. 

Entity Framework: Calling Stored Procs (Code First)

• You can tell EF to use your stored procedures to handle updates, deletes and inserts when you call your DbContext object SaveChanges method (instead of generated SQL)
• Entity properties are mapped to parameters of stored procedures
• EF creates a command to invoke the mapped stored procedure
• Reverse engineer from existing Db does not pull in Stored Procs

Stored Proc Mapping Conventions

Shortcut: Stored procedures can be generated stored using Code First migrations

protected override void OnModelCreating(DBModel Builder modelBuilder)

{

  modelBuilder.Entity<Customer>().MapToStoredProcedures();

  base.OnModelCreating(modelBuilder);

}

<EnityType>_<Operation>

Example:

Customer_Insert

Customer_Update

Customer_Delete

• Parameters for sp named same as entity properties
• Insert stored procedure returns the new Id of added entity
• 
  

Custom Stored Proc Mapping Conventions

• SP Name (HasName)
• Parameters (Parmeter())
• FK Parameters (Parameter() and Navigation())
• Result Field Names (Result())

protected override void OnModelCreating(DBModel Builder modelBuilder)

{

  modelBuilder.Entity<Customer>().MapToStoredProcedures(

s=>

  s.Insert(i => i.HasName("CustomerINSERT")

  .Parameter(c => c.Name, "CustName"))

  // pulling foreign key out of navigation property

  .Parameter(c => c.Category.Id, "CategoryId")

  // pulling value from another entity that contains a collection of customers

  // Customer has not property corresponding to Segment

  .Navigation<CustomerSegment>(s => s.Customers, c=> c.Parameter(c => c.Id)) 

  .Result(r => r.Id, "NewCustomerId")

)

    .Update(u => u.HasName("CustomerUPDATE"))

    .Delete(d => d.HasName("CustomerDELETE"))

);

  base.OnModelCreating(modelBuilder);

}

Invoking any Stored Procedure

Shortcut: Use the EF Designer: Just select Add | New Item and, in the Add New Item dialog, pick the ADO.NET Entity Data Model. Copy the generated code to your DbContext.

In DbContext:

public virtual int UpdateStats(Nullable<int> CountyId, string Segment)

{

  var CountryIdParameter = CountyId.HasValue ?

    new ObjectParameter("CountyId", CountyId) :

    new ObjectParameter("CountyId", typeof(int));

    

  var SegmentParameter = Segment!= null ?

    new ObjectParameter("Segment", Segment) :

    new ObjectParameter("Segment", typeof(string));

    

  return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction(

    "UpdateCountrySegment", CountryIdParameter , SegmentParameter );

}

Entity Framework: Usage Patterns

Database First

• Use EF Designer to reverse engineer a database into a model (code classes).
• Use the Designer to keep the model in sync with the database.
• Visually change mapping, and other properties

Model First

• Start with a blank model in the Entity Framework Designer
• Build out the model and generate the POCO classes and database from generated code

Code First

• Start coding your own classes. (Or to expedite use Entity Framework Power Tools to generate classes from an existing db.)
• Db scripts can be generated from these classes with EF tools

Code and Database Separate

• No EF designer is involved
• Database: Design your database independently as usual
• Code: Create POCO classes to interact with the database
• See: Entity Framework: Code and Database Separate Approach

Entity Framework: What is it?

• Entity Framework (EF) in an ORM (Object Relational Mapping) framework, similar to Hibernate/NHibernate. EF6 is open source and is distributed as complete Nuget package. Predecessors to EF include DataSet (ADO.Net), and LINQ to SQL (only worked with SQL Server). EF has a provider model and works with any relational database with valid EF provider.
• EF takes data out of the database and transfers data to class objects used in an application. EF takes modified data out of class objects and saves it into database columns. These class objects serve as data containers and are no more than POCOs (Plain old C# objects).
• EF figures out the mapping between the POCO properties and DB table columns. EF keeps track of changes made to POCO instances.  EF also handles entity relationships and foreign keys. You can also query DB Views and invoke DB Stored Procedures.
• EF provides robust designers to create data models and to automatically generate POCO classes. EF even generates database scripts for initial database creation and subsequent migrations/updates. 
• The use of EF designers is optional. You can totally design your database and code separately and independently.

Installing

Package Console Manager (Nuget)

install-package entityframework [-version 6.0.0]

uninstall-package entityframework

update-package entityframework

EF 6 Assemblies

• EntityFramework
• EntityFramework.SqlServer (other providers)
• System.ComponentModel.Annotations

EF Configuration

• App.Config has a section for the EF
• Defaults for the connection factory can be set in this section