Migration from ADO.Net to Entity Framework Core
The BPM tool which was developed in our company, could only work with SQL Server, later on a need occurred to make it work with PostgreSQL and other RDMS. It has been decided to migrate custom created ADO.net structures to Entity Framework Core (EF Core). The aim of this article is to share some advice and tips for the ones who have decided to apply this migration or for the one who are already using the EF Core.
TL;DR
- Design of DB Context and its usage in run-time
- How to use logging in Development/Production environments
- Integrates database hints to EF Core
- The things to be done for Bulk Operations
- How we write the unit tests for the data layers that we use EF Core
- EF Core performance tuning hints
Design and Run-time Usage of DB Context
As I have mentioned before, we aimed to migrate our current application to Entity Framework so that we had to proceed with DB-First. There is no Visual designer(edmx file) support for EF Core but you may use “DB Scaffold-DbContext” command to automatically generate DB Context. However this can cause DB Context to be more complicated and huge.
We have preferred to choose more structured architecture and so composed our own contexts, models, configurations and db objects, even though it wasn’t so practical. These DB context designs were related with extension methods. Via fluent API, we created extension methods on DB Context ModelBuilder. During the configurations we used the configuration classes of each model so that we prevented the chaos in DB Context and complexity of codes.
You may related code samples below and also in this Github link;
The application should work with multiple databases and there are differences between the schema , connection strings of databases. Also since the application has 5–6 difference contexts inside, below shown extension methods can be used.
In order to continue with DB Context creation at run-time;
Thinking that you are developing web applications, since EF Core db context is not a thread-safe class, your db context object should be re-created in every request and then should be disposed. For that purpose, I advice you to use the IoC container and let it do the object initializing.
You can use the scoped (Scoped objects are the same within a request, but different across different requests) or transient (Transient objects are always different; a new instance is provided to every controller and every service.) functions of the built-in dependency resolver in .Net Core.
Logging
In my opinion logging is a key point in EF Core, its advantages and also disadvantages can be notified, depending on its usage.
To start with I would like to mention about enabling logging in DB Context and benefits of it. You should use “useLoggerFactory” method of DbContextOptionsBuilder in order to log every operation that EF Core sends to Database(Opening and the closure of the connection, SQL or parameters that are sent to DB etc.).
If you require to log in the common areas in all .net projects such as Console, Output window, Microsoft.Extensions.Logging library already provides these providers for you but if you require to develop your own custom provide (sends log to queue or file) you have to implement ILoggerProvider and ILogger interfaces. These are great advantages provided by Microsoft.Extension.Logging packages. On the other hand, logging can be a disadvantage since console or output windows are single thread synchronized responsive structures. Every thread that logs in console or output windows on EF Core, locks other threads that does the same operation. As a result performance issues may occur. In order to not to face a performance issue, I advice you to write your own logger which sends logs to queue in a-sync way.
Hints
In order to use Sql Server table hints you should write your own custom Sql Server query generator classes which are inherited from SqlServerQuerySqlGeneratorFactory.
In our entity repository class methods, there is a boolean field called isNoLock that determines whether the query will use table with nolock hint or not. So the custom class(implements sqlserversqlquerygenerator) that we have created applies the same function. Samples of this custom class can be found in my Github page. You may proceed in that way in order to break in before creating and sending queries to db via sqlquerygenerators.
Bulk Operations
EF Core is lack of Bulk Operations. For every single crud operation on DB Context(add, update or delete), EF Core calls “detect changes method” to calculate changes and differences on object. You should disable this feature when retrieving data. However, thinking that, you need to use bulk insert operation(50.000, 100.000 rows), you may use a loop and call object add method every time but this way detect changes method to be called in every operation which can cause the query response to take minutes. You may call AddRange method to call detect changes only once however this operation is also slower than bulk operations which can be done on database.
So what should be done for bulk operations?
There are only 2 choices; using 3rd party nuget packages or writing your own bulk operations method ob db context. In the first place, we proceed with Z Framework nuget packages which is incandescent and provides multiple db support but comes with commercial licence cost. If you would like to proceed with free opinion, I would suggest that you should write your own extension method on context for bulk operations. I’ve used ADO.Net SqlBulkCopy for Sql Server Bulk Inserts and Npgsql COPY helper for Postgresql databases.
If you wish, there are lots of free or licensed EF Core Extensions which have been advised by Microsoft doc site.
Unit Testing
I would like to mention about our unit test writing process for repository methods and share you the libraries that we used.
As I mentioned at beginning of the article, existing application works with pure ADO.NET classes and T-SQL queries in repository methods. After we make the application running with EF Core and mutliple databases, we started to write our unit tests in specified structure. Well, How did we proceed?
We have written each unit test methods for each repository methods. Firstly, unit test method retrieve or call oldest ADO.NET and T-Sql queries. Secondly, we run repository methods with EF Core,MSSQL and for the last time the method run with EF Core, PostgreSql and then we compared the results of each. Also with the help of Benchmark Helper we obtain execution results in milliseconds (ADO.Net sql server, EF Core Sql Server and EF Core Postgresql) and send them to Grafana to view result in charts or graphs.
If you dont require to research detaily for benchmark you may use StopWatch to collect elapsed milliseconds. However, if microseconds are important for you, you should use benchmarkdotnet nuget package which is a powerful benchmark library and has support for .Net Standard to collect professional benchmark results.
Performance Tips
- Disable Change Tracking
If you will only do select operations on database or just need to retrieve data I strongly advice that you should disable EF tracking feature. For that purpose, you may set QueryTrackingBehaviour as NoTracking for whole context or you may use AsNoTracking() extension method which has been provided by Microsoft.EntityFrameworkCore at the end of IQueryable results. In our case, we set as default auto tracking off in db context constructor, If we need to enable it then we pass an input field to constructor before obtain db context with auto tracking enabled.
- Tuning your EF Queries
Generated query refactoring is another important point for performance tuning. Things that you should control; only specified and needed columns to be select in your query. Retrieve generated sql query and check your execution plan(for Sql server) or query plan(for postgres) to determine indexes are correct or not.
- Complied EF Queries
As default, linq queries executing with in below actions;
*Building and compiling linq queries.
*Executing queries
*Collecting results from DB.
For your frequently queries you may cache building and compiling part of your linq queries. For that purpose, you should use compiled queries to enhance your orm performance.
- Include() and ThenInclude()
There is no Lazy Loading support before EF Core 2.0 and previous versions. After 2.0 you may enable lazy loading feature to use.(Default is disabled).As our application needed, we do not use lazy loading feature. If you would like to use eager loading you have to have a look at Include() and ThenInclude() methods for gathering performance improvements.
- Disable logging in release mode
As I mentioned beginning of the article, apart from debug and local development you should disable your EF Core logging. As may be required you have to proceed with async queue structures to log your EF Core operations.Otherwise, you may encounter with performance issue.
Conclusion
EntityFramework Core is great ORM for .Net/.Net Core projects. I hope it was helpful for those who thinking migration to EF Core or already using it. Please do not hesitate to give your feed-backs and responses to me.
Please find sample codes for this article at below.
