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
-
Define model
-
Code-first
-
Database-first
-
-
Express & execute query over
-
EF determines & executes SQL query
-
EF transforms selected results into .NET objects
-
Modify data and call “save changes”
-
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?
[Databases] Entity Framework
By telerikacademy
[Databases] Entity Framework
- 959