Презентация

EntityFramework Advanced Querying - Databases Advanced - Entity Framework - октомври 2016

 

Материали от темата

Съдържание на документа

Entity Framework

SoftUni Team

Technical Trainers

Software University

http://softuni.bg

Advanced Querying


Table of Contents

Executing Native SQL Queries

Selection with anonymous objects

Joining and Grouping Tables

Changing state of objects

Batch Update and Delete

2


Table of Contents

Stored procedures 

Loading types

Concurrency checks 

Cascade deletions

3


4



sli.do
#Entity

Questions


Executing Native SQL Queries

Parameterless and Parameterized


6

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





Example:





Executing Native SQL Queries

ctx.Database.SqlQuery<return-type>(native-SQL-query);

string query = "SELECT count(*) FROM dbo.Employees";

var queryResult = ctx.Database.SqlQuery<int>(query);

int customersCount = queryResult.FirstOrDefault();


7

Native SQL queries can also be parameterized:

Native SQL Queries with Parameters

var context = new SoftUniEntities();

string nativeSQLQuery =

    "SELECT FirstName + ' ' + LastName " +

    "FROM dbo.Employees WHERE JobTitle = {0}";



var employees = context.Database.SqlQuery<string>(

    nativeSQLQuery, "Marketing Specialist");

foreach (var emp in employees)

{

    Console.WriteLine(emp);

}

Parameter placeholder

Parameter value


8

Selection with anonymous objects


9

Using select, we can take only the data that we need and not all the information from the table. (less traffic) 





Why use select

 var employeesWithTown = context.Employees.Select(employee => new

            {

                EmployeeName = employee.Name,

                TownName = employee.Town.Name

            });


10

Data that is selected is not of the initial entity type, but of an anonymous type that is generated runtime. (a bit more expensive)

Data cannot be modified (updated, deleted), when selected, because we are not working with the actual object, but with a read-only “copy” of it.

Why not to use select 


Joining and Grouping Tables

Join and Group Data Using LINQ


12

Join tables in EF with LINQ / extension methods on IEnumerable<T> (like when joining collections)

Joining Tables in EF

var employees = 

  from employee 

    in softUniEntities.Employees

  join department 

    in softUniEntities.Departments

  on employee.EmployeeID 

    equals department.DepartmentID

  select new { 

    Employee = employee.FirstName, 

    JobTitle = employee.JobTitle, 

    Department = department.Name 

  };

var employees = 

  softUniEntities.Employees.Join(

    softUniEntities.Departments,

    (e => e.DepartmentID),

    (d => d.DepartmentID), 

    (e, d) => new {

      Employee = e.FirstName, 

      JobTitle = e.JobTitle, 

      Department = d.Name

    }

  );


13

Grouping also can be done by LINQ

The same ways as with collections in LINQ

Grouping with LINQ:





Grouping with extension methods:

Grouping Tables in EF

	

var groupedEmployees = 

  from employee in softUniEntities.Employees

  group employee by employee.JobTitle;

var groupedCustomers = softUniEntities.Employees

  .GroupBy(employee => employee.JobTitle);


Changing state of objects in EF


15

In Entity Framework, objects can be:

Attached to the object context (tracked object)

Detached from an object context (untracked object)

Attached objects are tracked and managed by the DbContext

SaveChanges() persists all changes in DB

Detached objects are not referenced by the DbContext

Behave like a normal objects, which are not related to EF



Attaching and Detaching Objects


16

When a query is executed inside an DbContext, the returned objects are automatically attached to it

When a context is destroyed, all objects in it are automatically detached

E.g. in Web applications between the requests

You might later on attach to a new context objects that have been previously detached

Attaching Detached Objects


17

When an object is detached?

When we get the object from a DbContext and then Dispose it

Manually: by set the EntryState to Detached

Detaching Objects

Employee GetEmployeeById(int id)

{

   using (var softUniEntities = new SoftUniEntities())

   {

      return softUniEntities.Employees

         .First(p => p.EmployeeID == id);

   } 

}

Now the returned employee is detached


18

When we want to update a detached object we need to
reattach it and then update it: change to Attached state

Attaching Objects

void UpdateName(Employee employee, string newName)

{

   using (var softUniEntities = new SoftUniEntities())

   {

      var entry = softUniEntities.Entry(employee);

      entry.State = EntityState.Added;

      employee.FirstName = newName;

      softUniEntities.SaveChanges();

   } 

}


19

Batch Update and Delete statements


20

Gives the ability to make a bulk delete of rows/entities by given criteria. 



Install EF.Extended as a NuGet package or simply run 
Install-Package EntityFramework.Extended



EntityFramework.Extended


21

Delete all users where FirstName matches given string



This results in making only one query for the deletion to the DB







Bulk delete

context.Users.Delete(u => u.FirstName == “Pesho");


22

Update all Employees with Name “Nasko” to “Plamen”





This results in making only on query for the update to the DB

Bulk update without prefilter: 

context.Employees.Update(

                t => t.Name == "Nasko",

                t => new Employee() {Name = "Plamen"});


23

Update all Employees’ age to 99 who have a name “Plamen”









This results in making only on query for the update to the DB like in the previous picture.



Bulk update with prefilter: 

IQueryable<Employee> employees = context.Employees

			.Where(employee => employee.Name == "Plamen");



context.Employees.Update(

		employees,

		employee => new Employee() {Age = 99});


24

Store procedures with EF


25

One we have a stored procedure on the server we can call it using the native way of sending queries to the server. We simple need to give the name of the stored proc and the parameters if needed. 

Example: 	

Calling a stored procedure 

CREATE PROCEDURE UpdateAge @param int

AS 

BEGIN

UPDATE Employees SET Age = Age + @param;

END

SqlParameter ageParameter = new SqlParameter("@age", SqlDbType.Int);

ageParameter.Value = 2;

            context.Database.ExecuteSqlCommand(

"UpdateAge @age", ageParameter);




26

Loadings


27

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by the use of the Include method











The Include with the lambdas is found in the System.Data.Entity



Eager loading 

IEnumerable<Town>  towns = context.Towns.Include("Employees");

OR

IEnumerable<Town> emp = context.Towns.Include(town => town.Employees);



            foreach (Town town in towns)

            {

                Console.WriteLine(town.Employees.Count);

            }


28

Lazy loading means delaying the loading of related data, until you specifically request for it.



There are some examples (like when using serialization) when we do not want lazy loading. Then we turn it off either by removing virtual or it can be removed for the whole project by:

Lazy loading

public CompanyContext()

            : base("name=CompanyContext")

        {

            Configuration.LazyLoadingEnabled = false;

        }


29

Even with lazy loading disabled it is still possible to lazily load related entities, but it must be done with an explicit call. To do so you use the Load method on the related entity’s entry.



Explicit loading 

 IEnumerable<Town> towns = context.Towns;

            foreach (Town town in towns)

            {

                context.Entry(town).Collection(st => st.Employees).Load();

		OR           .Reference(st => st.Country).Load();

                Console.WriteLine(town.Employees.Count);

            }


30

Concurrency


31

EF runs in optimistic concurrency
mode (no locking)

By default the conflict resolution
strategy in EF is "last wins"

The last change overwrites
all previous concurrent changes

Enabling "first wins" strategy for certain property in EF:

ConcurrencyMode=Fixed (in DB first project)

[ConcurrencyCheck] (in code first projects)

Optimistic Concurrency Control in EF


32

Last Wins – Example

var contextFirst = new SoftUniEntities();

var lastProjectFirstUser = contextFirst.Projects

    .OrderByDescending(p => p.ProjectID).First();

lastProjectFirstUser.Name = "Changed by the First User";



// The second user changes the same record

var contextSecondUser = new SoftUniEntities();

var lastProjectSecond = contextSecondUser.Projects

    .OrderByDescending(p => p.ProjectID).First();

lastProjectSecond.Name = "Changed by the Second User";



// Conflicting changes: last wins

contextFirst.SaveChanges();

contextSecondUser.SaveChanges();

Last wins: the second user overwrites the first user's changes


33

First Wins – Example

var contextFirst = new SoftUniEntities();

var lastTownFirstUser = contextFirst

    .Towns.OrderByDescending(

    t => t.TownID).First();

lastTownFirstUser.Name = "First User";



var contextSecondUser = new SoftUniEntities();

var lastTownSecondUser = contextSecondUser.Towns

    .OrderByDescending(t => t.TownID).First();

lastTownSecondUser.Name = "Second User";



contextFirst.SaveChanges();



contextSecondUser.SaveChanges();

First wins: the second user gets DbUpdateConcurrencyException


34

Cascade deletions


35

Required FK with cascade delete set to true, deletes everything related to the deleted property

Required FK with cascade delete set to false, throws exception (it cannot leave the navigational property with no value) 

Optional FK with cascade delete set to true, deletes everything related to the deleted property.

Optional FK with cascade delete set to false, sets the value of the FK to NULL

Cascade delete scenarios 


36

Two solutions with Fluent API:

Remove default cascade delete convention globally





Manually configure the relation

Solving Cascade Delete Issue with Fluent API

modelBuilder.Conventions

        .Remove<OneToManyCascadeDeleteConvention>();

modelBuilder.Entity<User>()

    .HasMany(u => u.Answers)

    .WithRequired(a => a.User)

    .WillCascadeOnDelete(false);


Advanced Querying in Entity Framework

https://softuni.bg/courses/


License

This course (slides, examples, demos, videos, homework, etc.)
is licensed under the "Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International" license

38

Attribution: this work may contain portions from

"Databases" course by Telerik Academy under CC-BY-NC-SA license


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/SoftwareUniversity

	Software University @ YouTube

	youtube.com/SoftwareUniversity

	Software University Forums – forum.softuni.bg