Entity Framework
Telerik Academy Alpha

 

Databases

 Table of contents

What is ORM?

 ORM Technologies

  • Object-Relational Mapping (ORM) is a programming technique for automatic mapping and converting data

    • Between relational database tables and object-oriented classes and objects

  • ORM creates a "virtual object database"

    • Which can be used from within the programming language, e.g. C# or Java

  • ORM frameworks automate the ORM process

    • A.k.a. object-relational persistence frameworks

 ORM Frameworks

  • ORM frameworks typically provide the following functionality:

    • Creating object model by database schema

    • Creating database schema by object model

    • Querying data by object-oriented API

    • Data manipulation operations

      • CRUD – create, retrieve, update, delete

  • ORM frameworks automatically generate SQL to perform the requested data operations

 ORM Frameworks

  • Database and Entities mapping diagrams for a subset of the Northwind database

 ORM Frameworks

  • Built-in ORM tools in .NET Framework and VS

    • Entity Framework (LINQ-to-Entities)

    • LINQ-to-SQL

    • Both combine entity class mappings and code generation, SQL is generated at runtime

  • Third party ORM tools

    • NHibernate – the old daddy of ORM

    • Telerik OpenAccess ORM

Entity Framework Overview

 Entity Framework

  • Entity Framework (EF) is a standard ORM framework, part of .NET

    • Provides a run-time infrastructure for managing SQL-based database data as .NET objects

  • The relational database schema is mapped to an object model (classes and associations)

    • Visual Studio has built-in tools for generating Entity Framework SQL data mappings

      • Data mappings consist of C# classes and XML

    • A standard data manipulation API is provided

 Features

  • Maps tables, views, stored procedures and functions as .NET objects

  • Provides LINQ-based data queries

    • Executed as SQL SELECT on the database server 

  • Built-in CRUD operations

    • Create / Read / Update / Delete

  • Creating or deleting the database schema

  • Tracks changes to in-memory objects

 Workflow

  1. Define model

    • Code-first

    • Database-first

  2. Express & execute query over

  3. EF determines & executes SQL query

  4. EF transforms selected results into .NET objects

  5. Modify data and call “save changes”

  6. EF determines & executes SQL query

 EF Components

  • The DbContext class

    • DbContext holds the database connection and the entity classes

    • Provides LINQ-based data access

    • Implements identity tracking, change tracking, and API for CRUD operations

  • Entity classes

    • Each database table is typically mapped to a single entity class (C# class)

Reading Data with EF

 The DbContext class

  • Тhe DbContext class is generated by the Visual Studio designer

  • DbContext provides:

    • Methods for accessing entities (object sets) and creating new entities (Add() methods)

    • Ability to manipulate database data though entity classes (read, modify, delete, insert)

    • Easily navigate through the table relationships

    • Executing LINQ queries as native SQL queries

    • Create the DB schema in the database server

 Reading Data with LINQ Query

  • Executing LINQ-to-Entities query over EF entity:

using (var context = new NorthwindEntities())
{
  var customers = 
    from c in context.Customers
    where c.City == "London"
    select c;
}

  • Customers property in the DbContext:

public partial class NorthwindEntities : DbContext
{
  public IDbSet<Customer> Customers { get; set; }
}

 Reading Data with Fluent API

  • We can also use extension methods (fluent API) for constructing the query

using (var context = new NorthwindEntities())
{
  var customerPhoness = context.Customers
      .Select(c => c.Phone)
      .Where(c => c.City == "London")
      .ToList(); // Executes the query
}

  • Find element by id

using (var context = new NorthwindEntities())
{
  var customer = context.Customers.Find(2);
  Console.WriteLine(customer.ContactTitle);
}

 Logging the Native SQL Query

  • To print the native database SQL commands executed on the server use the following:

var query = context.Countries;
Console.WriteLine(query.ToString());

  • This will print the SQL native query executed at the database server to select the Countries

    • Can be printed to file using StreamWriter class instead of Console class

CRUD with EF

 Creating New Data

  • To create a new database row use the method Add(…) of the corresponding collection:

// Create new order object
Order order = new Order()
{
  OrderDate = DateTime.Now, ShipName = "Titanic",
  ShippedDate = new DateTime(1912, 4, 15),
  ShipCity = "Bottom Of The Ocean"
};

// Mark the object for inserting
context.Orders.Add(order);
context.SaveChanges();

  • SaveChanges() method call is required to post the SQL commands to the database, using a transaction

 Cascading Inserts

  • We can also add cascading entities to the database:

Country spain = new Country();
spain.Name = "Spain";
spain.Population = "47 770 100";

spain.Cities.Add(new City { Name = "Barcelona" });
spain.Cities.Add(new City { Name = "Madrid" });

countryEntities.Countries.Add(spain);
countryEntities.SaveChanges();

  • This way we don't have to add each City individually

    • They will be added when the Country entity (Spain) is inserted to the database

 Deleting Data

  • Delete is done by Remove() on the specified entity collection

  • SaveChanges() method performs the delete action in the database

Order order = northwindEntities.Orders.First();

// Mark the entity for deleting on the next save
northwindEntities.Orders.Remove(order);

northwindEntities.SaveChanges();

Executing Native SQL

 Executing Native SQL Queries

  • Executing a native SQL query in Entity Framework directly in its database store:
     

string query = "SELECT count(*) FROM dbo.Customers";
var queryResult = ctx.Database.SqlQuery(query);
int customersCount = queryResult.FirstOrDefault();

  • Examples are shown in SQL Server but the same can be done for any other database

 Executing Native SQL Queries

  • Native SQL queries can also be parameterized:

NorthwindEntities context = new NorthwindEntities();
string nativeSQLQuery =
  "SELECT FirstName + ' ' + LastName " +
  "FROM dbo.Employees " +
  "WHERE Country = {0} AND City = {1}";

string[] parameters = { country, city };
string parametarizedQuery = string.Format(nativeSQLQuery, parameters);

var employees = context.Database.SqlQuery(parametarizedQuery );
foreach (var emp in employees)
{
  Console.WriteLine(emp);
}

Questions?

Made with Slides.com