ORM Technologies and Entity Framework EF Entity Framework

ORM Technologies and Entity Framework (EF) Entity Framework, Db. Context, CRUD Operations, Code First, Migrations Svetlin Nakov Inspiration Manager Software University http: //softuni. bg

Table of Contents 1. ORM Technologies – Basic Concepts 2. Entity Framework – Overview 3. Database First with EF 4. Reading Data and CRUD operations with EF 5. Code First with EF § Domain Classes and Db. Context 6. Migrations in EF 2

Introduction to ORM Technologies What is Object-Relational Mapping (ORM)?

ORM Technologies § Object-Relational Mapping (ORM) is a programming technique for automatic mapping data and database schema § Map relational DB tables to classes and objects § ORM creates a "virtual object database" § Used from the programming language (C#, Java, PHP, …) § ORM frameworks automate the ORM process § A. k. a. Object-Relational Persistence Frameworks 4

ORM Mapping – Example Relational database schema ORM Framework ORM Entities (C# classes) 5

Entity Framework (EF) Object-Relation Persistence Framework for. NET

Overview of EF § Entity Framework (EF) is the standard ORM framework for. NET § Maps relational database to C# object model § Powerful data manipulation API over the mapped schema § CRUD operations and complex querying with LINQ § Database first approach: from database to C# classes § Code first approach: from classes to DB schema § Visual Studio generates EF data models § Data mappings consist of C# classes, attributes and XML 7

EF: Basic Workflow 1. Define the data model (use a DB visual designer or code first) 2. Write & execute query over IQueryable 3. EF generates & executes an SQL query in the DB 8

EF: Basic Workflow (2) 4. EF transforms the query results into. NET objects 5. Modify data with C# code and call "Save Changes" 6. Entity Framework generates & executes SQL command to modify the DB 9

Database First with Entity Framework and Visual Studio Live Demo

Entity Framework – Components § The Db. Context class § Db. Context holds the DB connection § Holds and Db. Set<T> for the entity classes § Provides LINQ-based data access ( through IQueryable) § Provides API for CRUD operations § Entity classes § Hold entities (objects with their attributes and relations) § Each database table is typically mapped to a single C# entity class 11

Reading Data with LINQ Query § We can also use extension methods for constructing the query using (var context = new Soft. Uni. Entities()) { var employees = context. Employees This is called projection. Select(c => c. First. Name). Where(c => c. Job. Title == "Design Engineering"). To. List(); } This is called To. List() method § Find element by id executes the SQL query selection using (var context = new Soft. Uni. Entities()) { var project = context. Projects. Find(2); Console. Write. Line(project. Name); } 12

Creating New Data § To create a new database row use the method Add(…) of the corresponding collection: var project = new Project() Create a new { project object Name = "Judge System", Start. Date = new Date. Time(2015, 4, 15) }; Mark the object for inserting context. Projects. Add(order); context. Save. Changes(); This will execute an SQL INSERT § Save. Changes() method executes the SQL insert / update / delete commands in the database 13

Cascading Inserts § We can also add cascading entities to the database: Employee employee = new Employee(); employee. First. Name = "Petya"; employee. Last. Name = "Grozdarska"; employee. Projects. Add(new Project { Name = "Soft. Uni Conf" } ); soft. Uni. Entities. Employees. Add(employee); soft. Uni. Entities. Save. Changes(); § This way we don't have to add Project individually § They will be added when the Employee entity (employee) is inserted to the database 14

Updating Existing Data § Db. Context allows modifying entity properties and persisting them in the database § Just load an entity, modify it and call Save. Changes() § The Db. Context automatically tracks all changes made on its entity objects Employees employee = soft. Uni. Entities. Employees. First(); employees. First. Name = "Alex"; context. Save. Changes(); This will execute an SQL SELECT to load the first order an SQL UPDATE 15

Deleting Existing Data § Delete is done by Remove() on the specified entity collection § Save. Changes() method performs the delete action in the database Employees employee = soft. Uni. Entities. Employees. First(); Mark the entity for deleting at the next save soft. Uni. Entities. Employees. Remove(employee); soft. Uni. Entities. Save. Changes(); This will execute the SQL DELETE command 16

Native SQL Queries var context = new Soft. Uni. Entities(); Parameter string native. SQLQuery = placeholder "SELECT First. Name + ' ' + Last. Name " + "FROM dbo. Employees WHERE Job. Title = {0} "; var employees = context. Database. Sql. Query< string>( native. SQLQuery, "Marketing Specialist"); foreach (var emp in employees) { Console. Write. Line(emp); } Parameter value Return type 17

CRUD Operations with EF Live Demo

"Code First" Approach in EF From Classes to DB Schema

Database First in EF § Create database schema and generate C# code (models) from it DB EDMX Model Domain Classes 20

Code First in EF Db. Context & Model. Builder Domain classes Custom Configuration DB As needed 21

Domain Classes (Models) § Bunch of normal C# classes (POCO) § May hold navigation properties public class Post. Answer Primary key { public int Id { get; set; } public string Content { get; set; } Foreign key public int Post. Id { get; set; } public virtual Post { get; set; } } Navigation property Virtual for lazy loading 22

Domain Classes (Models) (2) § Another example of domain class (model) public class Post { This prevents public Post() Null. Reference. Exception { this. Answers = new Hash. Set<Post. Answer>(); } Navigation property public virtual ICollection<Post. Answer> Answers { get; set; } } public Post. Type { get; set; } Enumeration 23

Defining the Db. Context Class using System. Data. Entity; using Code. First. Models; Put all entity classes as Db. Sets public class Forum. Context : Db. Context { public Db. Set<Category> Categories { get; set; } public Db. Set<Post> Posts { get; set; } public Db. Set<Post. Answer> Post. Answers { get; set; } public Db. Set<Tag> Tags { get; set; } } 24

CRUD Operations with EF Code First var db = new Forum. Context(); var category = new Category { Name = "Database course" }; db. Categories. Add(category); var post = new Post(); post. Title = "Homework Deadline"; post. Content = "Please extend the homework deadline"; post. Type = Post. Type. Normal; post. Category = category; post. Tags. Add(new Tag { Text = "homework" }); post. Tags. Add(new Tag { Text = "deadline" }); db. Posts. Add(post); db. Save. Changes(); 25

"Code First" Approach in EF Live Demo

Using Code First Migrations in EF

Code First Migrations in Entity Framework § Enable Code First Migrations § Open Package Manager Console § Run Enable-Migrations command § -Enable. Automatic. Migrations for auto migrations 28

Using Code First Migrations in EF Live Demo

ORM Technologies and Entity Framework (EF) ? s n o i t s e u Q ? ? ? https: //softuni. bg

Free Trainings @ Software University § Software University Foundation – softuni. org § Software University – High-Quality Education, Profession and Job for Software Developers § softuni. bg § Software University @ Facebook § facebook. com/Software. University § Software University @ You. Tube § youtube. com/Software. University § Software University Forums – forum. softuni. bg