This article will show several best practice cases for using Entity Framework Core (aka EF Core). EF was an innovative technology to convert data to classes when ADO or even DAO were mainly used for data transformation in C#. It made developers' lives easier by not having to memorize and frequently refer which column included what kind of information. EF had evolved from EF to EF Core since .NET Core was introduced. Without knowing the best practices, you may see some issues such as "very" slow data processing or high memory usage.
1. DO NOT USE POOLING
The pooling is when you reuse the same db context over and over again in the same application. This was a good practice when PC or server had limited resources and they were much slower. However, this feature is prone to memory leak. Nowadays you can recreate a db context whenever you need to use it.
2. AsNoTracking()
It is a good practice when you add AsNoTracking() on the read-only data. DB Context keeps track of all changes whenever you bring in data using EF Core, but this consumes resources and may slow down your data processing. In some cases, you will see a db context using GBs of memory. Without this, you will write a code as below:
However, if you are just bringing in the data to show in a grid control (read-only). Then the following code technique is recommended:
If you use AsNoTracking(), the db context does not keep track of changes, so when you save data using EF Core, you will see problems, but there is a command to support that. Before saving, use the following code:
By using AsNoTracking, you will see the performance boost as below:
3. FromSqlRaw() or FromSqlInterpolated()
Sometimes complex Linq queries will degrade EF Core's performance. In that case, use pure t-sql with FromSqlRaw() or FromSqlInterpolated() as below:
FromSqlInterpolated is more secure than FromSqlRaw because it can prevent an injection attack from the hackers. The same code above becomes as below using FromSqlInterpolated() method.
You are adding "$" in the sql command and any parameters will be added in the string with { }. This is the same technique as a string formatter.
4. Streaming vs Buffering
When you bring an array data to the client, there are two ways - buffering or streaming. Buffering is when you use .ToList() or .ToArray() method. This gets buffered in the memory. When you bring a small number of data, then this is okay, but as the number grows, the performance of the application will degrade.
Streaming is a preferred way when you are dealing with a lot of data. If you do not use those methods above, the data is stored as Enumerable. If you are using a loop, then you can buffer individual record, not the whole list or array. This is slower than buffering the entire list but this will prevent possible application crashing due to the resource shortage.
One warning on using Where method on the code block above is whether you use BEFORE or AFTER AsEnumerable() method. Any Where clause you use BEFORE will be filtered on the server and WILL NOT transferred to the client. Any Where clause you use AFTER AsEnumerable method is when you filter data AFTER all data is transferred to the client. Depending on your needs, you must use this wisely, especially when you are transferring data over the internet.
