646 886-6928 info@vironit.com

Effective Work with Entity Framework Core

10.06.2019 Ruslan Y.
Effective Work with Entity Framework Core

Entity Framework is an Object Relational Mapping (ORM) solution for the .NET framework that allows mapping code objects to database tables. Entity Framework is an add-on ADO.NET provides easy and efficient access to data.

Entity Framework is used very often when building a data access layer for projects on .NET. During its development, ORM EF has grown significantly in performance and capabilities, as well as its cross-platform implementation of EF Core for use within the .NET Core platform.

As we all know, data processing can take considerable time when working with large data sets, so we need to use a tool such as EF in order for the system to respond as quickly as possible to users.

Let’s begin to analyze the performance issues with the Entity Framework and work out how to improve the performance with the Entity Framework.

Bulk Insert, Update and Delete Data

Sometimes we need to send large data sets to the database for processing, for example, to import data: articles from blogs, reference books, etc. Consider the optimization process in the case of bulk data insertion. Bulk data insertion requires a lot of overhead from the Entity Framework and this slows down the running time of the application. We can significantly optimize the execution time of such an operation by using the extension methods from the nuget of the EFCore. BulkExtensions package. The EFCore.BulkExtensions package also contains useful synchronous and asynchronous versions of methods for bulk updating, deleting and reading data: BulkRead, BulkUpdate, BulkDelete, and their asynchronous versions.

The following is an example of inserting 800 thousand rows into a database without optimization:

1

The operation of inserting 800 thousand rows into the database without optimization takes 2 minutes 38 seconds 890 milliseconds.

The following is an example of inserting 800 thousand rows into a database using the optimization of the insert operation:

2

The operation of inserting 800 thousand rows into the database using the BulkInsert() method takes 10021 ms or 10 seconds and 21 milliseconds.

In general, the execution time of the data extraction code in the above example decreased by 15.85 times.

Using AsNoTracking ()

By default, the EF includes a change tracking mechanism for entities derived from the database. We can override this behavior in order to improve performance since change tracking is not always required by the logic of the application. For example, in cases when we need to display some data for the first time in a visual component of a table or widget. In such cases, the data is used only for reading, and we do not need to track changes to this data. To disable the tracking mechanism when retrieving data, we can use the AsNoTracking () method. Increased performance in the use of this method is particularly noticeable on large amounts of data. Examples of scripts for working with large amounts of data: importing data from a database to a CSV file, obtaining data for analysis, processing, and reporting.

Below is an example showing the difference before and after optimization using the AsNoTracking() method.

3

A query on a sample of 800 thousand rows before optimization works out for 8992 ms.

4

A query on a sample of 800 thousand rows after optimization works for 3170 ms.

In general, the execution time of the data extraction code in the above example decreased by 2.83 compared with the previous version of the code which does not use the AsNoTracking() method.

If we don’t want to call AsNoTracking() every time in the request, we can disable the change tracking mechanism at the context instance level, for example:

_context.ChangeTracker.QueryTrackingBehavior =

QueryTrackingBehavior.NoTracking;

Using pagination

Often in the application, we need to display some information to the user in a compact, tabular form, the amount of which can sometimes be tens of thousands or even millions of records. Examples of such data include information about products of a large online trading platform, information about stocks or millions of records for logging systems. Obtaining such a large amount of data even considering optimization when using the AsNoTracking() method reduces performance and may cause a timeout for a request from a client application. For this, we can use the pagination mechanism, which is easily implemented in LINQ using the Skip() and Take() methods.

An example implementation using the LINQ methods is shown below:

// Code removed for brevity

_context.People

.OrderBy(p => p.PersonId)

.Skip((page - 1) * pageSize)

.Take(pageSize)

// Code removed for brevity

The LINQ query described above will return only the data for the requested page, which is determined by the page number and page size. This approach provides a significant increase in performance and makes a quick response of the system to the user during page-by-page navigation.

Projections

In LINQ queries, we often use the projection mechanism using the Select() method. In such scenarios, we often convert the entity objects into a view model for further use of the view model data in user interface elements. EF Core by default provides a mechanism for tracking changes when projecting to a complex or anonymous type.

Below is a sample code in which the change tracking mechanism is enabled for the Person property for each projected object in the ProductViewModel model:

// Code removed for brevity

 context.Product

   .AsNoTracking()

   .Select(item =>

   new ProductViewModel

   {

       Product = item,

       NoOfProducts = item.Comments.Count

  }).ToListAsync());

// Code removed for brevity

The data in the view model does not need to track changes, so we need to form projections in such a way as to avoid the overhead costs associated with tracking changes. In order to avoid such behavior, we can replace the use of the property of a complex type with the necessary properties of simple types:

// Code removed for brevity

context.Products

.AsNoTracking()

.Select(item =>

new ProductViewModel

{

ProductName = item.Name,

ProductDescription = item.Description,

NoOfProducts = item.Products.Count

}).ToListAsync());

// Code removed for brevity

In the example above, we replaced the Products property with the ProductName and ProductDescription properties of the string type, and now when this projection is executed, the data will not be tracked by EF.

Asynchronous operations

The use of asynchronous operations in ASP.NET MVC provides application scalability by supporting parallel execution of multiple requests without blocking a thread that can run some logic for a long time.

Consider the basic steps of the ASP.NET request processing pipeline:

– The request received from the client goes to the ASP.NET processing pipeline and the executable stream is allocated.

– The thread will be busy with work until the end of execution

– If the action method performs a certain task for a long time in a synchronous manner, then the executable thread will be blocked until the task is completed. If all threads in the thread pool are blocked, the application will not be able to process new incoming requests.

– If the action method performs a certain task for a long time in an asynchronous manner, then the executing thread will be released before running the asynchronous code and returned to the thread pool for use by new requests

– After executing the logic of the action method, a new thread will be allocated from the thread pool, which will generate a response and complete the execution of the request

An example of an asynchronous action method is shown below:

    public async Task<IActionResult> Index()

     {

         var products = await _productRepository.GetAsync(

         new GetAllProductsQuery(_context)

         {

             IncludeData = true

         });

         // Code removed for brevity

     }

At the repository level, the GetAsync method is represented as follows:

     public async Task<IEnumerable<Product>> GetAsync<T>(T query)

     {

         return await query.HandleQueryAsync();

     }

The logic for running a query at the data context level is as follows:

     public class GetAllProductsQuery : QueryBase,

     IGetAllProductsQuery<GetAllProductsQuery>

     {

         // Code removed for brevity

         public async Task<IEnumerable<Product>> HandleQueryAsync()

         {

             var products = IncludeData

             ? await Context.Products

             .Include(p => p.Category).Include(p => p.TagProducts)

             .ToListAsync()

             : await Context.Products

             .ToListAsync();

         }

         // Code removed for brevity

     }

In the example described above, the code executed in an asynchronous manner increases the scalability of the application by using the asynchronous method ToListAsync(). Also in EF since version 6.0 there are other methods that support asynchronous execution:

ForEachAsync – asynchronous processing of elements of the set

AllAsync – whether all elements of the set correspond to the given condition

AnyAsync – whether at least one element of the set meets the specified condition

AverageAsync – calculation of the average value for a set of elements.

ContainsAsync – whether the specified element exists in the element set

CountAsync – getting the size of a set of elements

FirstAsync – getting the first element of the set

FirstOrDefaultAsync – getting the first element of a set or a default value

LoadAsync – explicitly loading data into the cache

MaxAsync – getting the maximum value from a set of elements

MinAsync – getting the minimum value from a set of elements

SingleAsync – getting one item from a set of items

SingleOrDefaultAsync– getting one item from a set of items or a default value

SumAsync – calculating the sum of the values of a set of elements

SaveChangesAsync – save results for operations that modify data in a database

ExecuteSqlCommandAsync – execute SQL commands in a database.

The methods listed above return an object of type Task or Task <T>Using transactions with asynchronous operations.

Using transactions with asynchronous operations

Entity Framework Core supports performing asynchronous operations within a transaction. The following is an example of use:

// Code removed for brevity

         var transactions = new TransactionScope();

         try

         {

                transactions.Transactions.Add(_context.

                Database.BeginTransaction());

             await _productRepository.ExecuteAsync(

             new CreateProductCommand(_context)

             {

                 Title = product.Title,

                 Name = product.Name,

                 CategoryId = product.CategoryId,

                 TagIds = product.TagIds

             });

             transactions.Commit();

         catch (Exception exception)

         {

                transactions.Rollback();

             ExceptionDispatchInfo.Capture

                (exception.InnerException).Throw();

         }

         return RedirectToAction("Index");

     }

     // Code removed for brevity

The N+1 Select issue

The N + 1 sampling issue occurs when using lazy loading and can be described in the following steps:

  1. EF receives a collection of entities for further enumeration of the received objects.
  2. During iteration, the navigation property is used; when it is read, N queries to the database are performed to get the value of this property for each element of the collection being iterated.
  3. Total queries to the database, we do N + 1, N – the number of queries executed during the iteration and one query performed when receiving a collection of objects.

Below is an example showing the issue:

  var people = await _context.People

                               .AsNoTracking()

                               .ToListAsync();

  var peopleViewModel = new List<PersonViewModel>();

  people.ForEach(p =>

  {

    peopleViewModel.Add(new PersonViewModel

    {

        Person = p,

        NoOfComments = p.Comments != null ?

         p.Comments.Count : 0

});

         });

In the described example, in order to get the number of comments for each record, a database query will be executed, which entails performance overhead. To improve performance in such cases, we can use eager loading using the Include() method.

The Include() method allows you to pull up the entity dependency graph in one query and will not be executed when iterating additional database queries. The following is an example using the Include() method:

  var people = await _context.People

                            .AsNoTracking()

                            .Include(p => p.Comments)

                               .ToListAsync();

  var peopleViewModel = new List<PersonViewModel>();

  people.ForEach(p =>

  {

    peopleViewModel.Add(new PersonViewModel

    {

        Person = p,

        NoOfComments = p.Comments != null ?

         p.Comments.Count : 0

});

         });

Please, rate my article. I did my best!

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 5.00 out of 5)
Loading…

Comments are closed.