SQL Queries in Entity Framework Core

SQL Queries in Entity Framework Core

Executing SQL Queries in EF Core Executing Stored Procedures in EF Core Combining SQL Queries with LINQ Queries

Combining SQL Queries with LINQ Queries

We have seen different ways of executing LINQ queries and ways to execute SQL queries from EF Core DBContext in isolation, now we shall see on how to combine SQL Queries with LINQ queries and execute them as a single query.

Let us get the list of Employees from the Employee table using SQL query and use the Include extension method to get the related department details as follows.

Executing Stored Procedures in EF Core

In the previous post we saw on how to execute SQL Queries from EF Core context, in this post we shall see on how to execute stored procedures using EF Core. Let us use the same Employee table, and create the following stored procedure to get the list of employees.

Executing SQL Queries in EF Core

So far we have seen different ways of writing LINQ queries using Entity Framework Core, but sometimes we cannot express certain complex scenarios as LINQ queries or the LINQ query might not be as efficient as the SQL query, in those cases we will have to execute SQL queries directly.

Entity Framework Core allows us to execute SQL queries against the underlying database with some restrictions. To execute SQL queries directly we need to use the FromSql extension method with the context object as follows.

Tracking and No Tracking in Entity Framework Core

Tracking and No Tracking in Entity Framework Core

Tracking queries in Entity Framework Core No-tracking queries in Entity Framework Core Tracking vs No-Tracking queries

Tracking vs No-Tracking queries

Entities retrieved using Entity Framework Core LINQ queries have tracking enabled by default, this allows us to make changes to the entity and save them back to the database using SaveChanges.

Tracking can be disabled at a query level by adding AsNoTracking extension method to the query. For queries which retrieve large amount of read-only data it is recommended to disable tracking to improve performance. Disabling tracking helps us to gain some performance and also save on memory since we don’t have to save tracking information in the cache.

To disable tracking at the context level we need to set NoTracking to the QueryTrackingBehavior property as follows.

No-tracking queries in Entity Framework Core

Entities retrieved using Entity Framework Core have tracking enabled by default, however there are some situations in which we will not need tracking. Let us say we need to load a Grid in the UI with a large amount of data which will not be updated in this case we will not need tracking since the data is read-only.

Tracking queries in Entity Framework Core

Entities retrieved using Entity Framework Core LINQ queries have tracking enabled by default, this allows us to make changes to the entity and save them back to the database using SaveChanges. When we execute a query in Entity Framework Core the resultant object is stored in a cache which allows us to make changes and use the object to update the database.

Entity Framework Core Eager, Explicity and Lazy Loading

Entity Framework Core Eager, Explicity and Lazy Loading

EFCore - Loading Dependent table data Eager Loading in Entity Framework Core Explicit loading in Entity Framework Core Lazy loading in Entity Framework Core Lazy loading Performance issues

Lazy loading Performance issues

Lazy loading allows us to reduce the size of the Main entity object in a LINQ query which has references to related entity objects. When the query executes it fetched the data of only the main entity object, data for the related objects are loaded only when we access the Navigational property from the main table.

With this approach we might end us firing multiple SQL queries to the underlying database and eventually bring down the performance. Let us assume we have a Grid in the View which displays a list of employees and each item has a details link which will display employee data and its related entity department’s data, then when the page loads a SQL query related to the employee’s entity will execute and get the list of employees. Later when the user clicks on each employee details link separate SQL queries will get executed to get each employees department data as follows.

Lazy loading in Entity Framework Core

In Lazy Loading the data from the Main table gets loaded first in the initial load, but the data from the related tables are not loaded, they get loaded only when we access the Navigational property from the main table. To perform Lazy loading we need to enable Lazy Loading while initializing the DBContext.

Lazy Loading was introduced in EF Core 2.1. To enable lazy loading we need to install the Microsoft.EntityFrameworkCore.Proxies package and enable it with a call to UseLazyLoadingProxies.

Let us start by installing the Microsoft.EntityFrameworkCore.Proxies package.

Explicit loading in Entity Framework Core

Explicit loading is a kind of lazy loading but the dependent objects are not loaded automatically, but we will have to explicitly load the dependent objects whenever needed. We have to use the Load() method to load related entities explicitly.

The initial data is loaded using a regular context query, the related data is loaded either using Reference for a single object or a Collection to load multiple related objects. Let us take the same Employee Department structure. First let us load the Employee details and later load the corresponding Department details using explicit loading.

Following are the LINQ queries to load employee and related department details using explicit loading.

Eager Loading in Entity Framework Core

Eager loading loads the data from the main table and the related / dependent tables together during the initial load. To perform eager loading we use the Include() method. Let us consider our sample tables Employee and Department to implement Eager loading.

The Employee table which has a Foreign Key DepartmentId referencing to the Department table. We will load the data from the Employee table and also load the corresponding data from the Department table using Eager Loading.

EFCore - Loading Dependent table data

When we Generate the Model classes using Entity Framework Core it creates Navigational properties for the Model classes which have a Foreign Key relation. Using these Navigational properties we can load dependent data in different ways.

In our previous examples we have seen the Employee table has Foreign Key relation with the Department table and the Country table, when we generate the Model classes using Entity Framework Core the Employee class will have reference to the related classes, the Employee model class will be as follows.

Asp.Net Core WebAPI with EF Core Joining Tables

Asp.Net Core WebAPI with EF Core Joining Tables

Custom Models in Asp.Net Core EF Core Joining multiple tables EF Core Joining tables with Include EF Core select specific columns in a table EF Core joining tables using Include and selecting specific columns

EF Core joining tables using Include and selecting specific columns

In the previous posts we saw on how to join tables using Include and how to select specific columns form a table. In this post we will try to combine both of them, we will join tables using Include and select specific columns from the joined tables.

We will join the Employee table with Department and Country tables, select specific columns from each of the table and populate the below Model class.

EF Core select specific columns in a table

In the previous posts for CRUD operations we saw on how to select all the column values from a context table, sometimes we will not need all the columns from a table instead we will need only specific columns from a table. In these cases we can use a Select () clause to filter specific columns from a table. In this post we shall see on how to select only the Id, Name and Age properties from the employee table.

The employee table has the following columns. 

EF Core Joining tables with Include

In the previous pose we saw on how to join tables using the join keyword. Entity Framework core also provides an alternate way to join tables using the Include clause.  To use include for joining the tables we need to set the Primary Key, Foreign Key relation in the Database. 

In the sample we have an Employee table which has a Foreign Key DepartmentId referencing to the Department table. We will join these tables using Entity Framework Core.

Asp.Net EF Core Joining multiple tables

In the previous post we creates a custom model class EmployeeDetail, in this post we shall see on how to use Entity Framework Core to JOIN the Employee, Department and Country tables and populate data into the custom Model class EmployeeDetail.

We have 3 tables, the Employee table is the main table which has related data in the Department and Country tables, DepartmentId and CountryId are the Foreign Keys in the employee table.

Entity Framework Core LINQ Queries Overview

Entity Framework Core LINQ Queries Overview

Entity Framework Core LINQ Queries Entity Framework Core Select all rows from a Table Entity Framework Core Select one row from a Table Entity Framework Core Where clause (Filter rows based on conditions) Entity Framework Core sub-query (Filter rows based on results from another table) Entity Framework Core ORDER BY (Sorting) Entity Framework Core GROUP BY (Grouping)