Connect to the Data Layer

The Glue: Object-Relational Mapping

A very successful bridge between relational db's and object oriented software.

Map table rows to objects in memory

All objects

Text

Add references or pointers

ORM

  • Create a model class for each table
  • Map data types and values between SQL and C#
  • Add a reference member variable for every FK and link it to the actual object it references
    • This is the "to 1" relationship
  • And then add the coolest feature ...
    • the "to many" relationship
  • We call these last two "Navigation Properties"

Showing "to many" property

Text

With 2 more bids, and collection1 has type ICollection<Bid>

Matching model class (simplified)

Matching context class (simplified)

At runtime the ORM will

  • Map table rows to objects
  • Build object graph in memory that matches the live db (efficiently!)
  • fetch (SELECT) data when necessary
  • write new attribute values when changes to existing objects are made (UPDATE)
  • add new records when objects are created (INSERT)
  • remove records when objects are removed from the object graph (DELETE)
  • When in C#, think of the DB as an object graph
  • Query the object graph using references, attributes and LINQ expressions
  • Use navigation properties to “follow” relationships
  • Edit, add or delete objects THEN TELL IT TO SAVE CHANGES

Entity Framework

Entity Framework (EF) is an object-relational mapper (ORM) that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

Entity Framework Core

We're using Entity Framework Core. EF Core 9 (EF9) was released in 11/24. EF Core 10 (EF10) will be released in 11/25

Others ORMs?

Benefits?

  • Makes things easy, can focus on business logic and good design
  • Efficient for the programmer
  • OOP is nice
  • Supports code re-use and easier maintenance
  • Reinforces abstraction layer between Logic and Data tiers
  • Usually prevents SQL Injection
  • DRY: models are defined in one place

Drawbacks?

  • Yet another dependence, potential vender lock-in
  • Having to learn it
  • NOT always efficient for speed and memory
  • Application developer has less control
  • Choosing eager vs. lazy loading
  • N+1 problem

Entity Framework Development Workflows Video

A couple demonstrations

Demo: Basic setup

 

Form a connection, generate models and view data; use Dependency Injection

Connection String

 

  • A string that tells your application how to find and access a database.
  • If you use more than one database you'll have and use more than one connection string.
  • These are named and their value follows a small domain specific language syntax.
  • They usually contain secrets and need to be secured.

DbContext subclass

  • This is our access to db tables
  • Each table "is-a" DbSet data structure
  • DbSet is an IQueryable

Start using the Repository and/or Service pattern

 Demo: Use a good design

Text

Text

Break Dependency on DbContext

LINQ

Language Integrated Query

  • Built-in to the language
  • Supported by the framework for local objects and remote data connections
  • Type safe
  • Operate on IEnumerable<T> and/or IQueryable
  • Found in System.Linq and System.Linq.Expressions
  • LINQ-to-Objects
  • LINQ-to-SQL
  • LINQ-to-XML

Sequences and Operators

Using Fluent Syntax

Chaining Query Operators

Title Text

Types

  • Lambda expressions
  • IEnumerable<T> (local queries)
  • IQueryable<T> (interpreted queries)

DbSet

When using LINQ-to-SQL

  • C# is translated into SQL
  • partially at compile time (to an intermediate language)
  • partially at runtime (using actual parameters e.g.)
  • and is then sent to the DBMS over the network

Use LINQPad

Things to pay attention to:

DON'T USE QUERY SYNTAX IN THIS CLASS

How to Implement Functionality in a Web Application?

Take a critical look at your favorite web applications.

Ask yourself how they do it? How does the data model support the features?

Typical activities

  • Browsing
  • Typing/selecting and then expecting something new
  • Modifying something that's already there
  • Get rid of something

Hypothetical: Blog site

  • Write a new blog post
  • Edit a post
  • Search for a post then view it
  • Search for a post by one user then view it ordered most recent first
  • Comment on a post
  • Like a post
  • Landing page that shows snippets of 10 most recent posts

More Demo's

Implement CRUD

Use the Repository pattern for good design and reusability

Sequence Diagrams for CRUD

Use it

A reminder of the POST problem

A Problem

The Problem with POST

Wikipedia

The Solution

// GET: GuestController/Create
public IActionResult Create()
{
    return View();
}

// POST: GuestController/Create
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(Guest guest)
{
    if(ModelState.IsValid)
    {
        // add new object to repository/database
        // sends a 302 Found (despite 303 See Other being the correct answer here)
        return RedirectToAction("Thanks");
    }
    return View(guest);
}

public IActionResult Thanks()
{
    return View();
}

Entity Framework Quirks

A good resource: Learn Entity Framework Core

Conventions

Eager vs. Lazy Loading

Reverse engineered models

Found many errors (data types, constraints) when using SQLite.

 

But rarely with SQL Server

Can manually configure with Fluent API

Concurrency issues

Migrations

We will use migrations for authentication (ASP.NET Core Identity). But they're not frequent.