Latest Articles

Entity Framework Core
Do Not Waste Performance by Not Using Temp Tables With Entity Framework Core
It has been a while since I released my article about the usage of temp tables in Entity Framework (v6). Meanwhile, Microsoft has released a completely rewritten version of its O/R mapper so my old approach is no longer applicable. But before we learn about a new one, let us…
Pawel Gerr
Entity Framework Core
Better Entity Framework Core Performance by Reading Execution Plans
Both a LINQ query and an SQL statement are descriptions that state which data should be fetched, but not how.. Sure, when reading LINQ or SQL, we can make assumptions about the performance but not in every case. Some queries are either too fancy or too big to grasp, so our…
Pawel Gerr
Entity Framework Core
Entity Framework Core Performance-Optimierung aus der Praxis vom 06.05.20 [Webinar]
Im ersten Teil des Webinars werden die häufigsten Performance-Probleme in Entity Framework Core v2 und v3 aufgezeigt. Im zweiten Teil geht es darum, die generierten SQL-Abfragen zu verstehen, denn nur so kann das Problem erkannt und gelöst werden. Das wichtigste Mittel hierfür…
Pawel Gerr
Entity Framework Core
Unnecessary Fuzzy Searches may hurt your Entity Framework Core Performance
After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one. What do I mean by…
Pawel Gerr
Entity Framework Core
The performance issue "Cartesian Explosion" made its comeback in Entity Framework Core 3
In Entity Framework Core 3.0/3.1 the SQL statement generation underwent significant changes. As we have seen in the previous post these changes removed both the implicit client-side evaluation and the N+1 Query Problem (which is good!). Unfortunately, these changes (re)introduced…
Pawel Gerr
Entity Framework Core
Is "N+1 Queries" still a performance issue in Entity Framework Core 3?
In a previous post we saw that EF 2.1 is highly susceptible to the N+1 queries problem. After the release of a new version of Entity Framework Core (EF) the first question coming to mind is: "Is it still a big issue in EF 3.1?" And if the answer is no, is there anything else we…
Pawel Gerr
Entity Framework Core
Entity Framework Core 3.0 - "Hidden" GROUP BY Capabilities (Part 2)
In the previous blog post we used a navigational property to work around the limitations of the extension method . The problem is, there is not always such a property we can use for grouping. Especially, when following domain driven design practices, a bidirectional navigation is…
Pawel Gerr
Entity Framework Core
Entity Framework Core 3.0 - "Hidden" GROUP BY Capabilities (Part 1)
With Entity Framework Core 3.0 (EF) the internal implementation of the LINQ query translation has been changed a lot. So, some queries that didn't work previously are working now and some that worked are not working anymore. :) The LINQ extension method is a special one…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Getting more Deadlock Information with named Transactions
Whether in an application in production or in integration tests on CI, the deadlocks are virtually unavoidable. The behavior of a deadlock depends on the database (MS SQL Server, MySQL, etc)  and the isolation level (like Snapshot Isolation). Some of the databases are blocking…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Beware of Lifetime Extension of TransactionScopes using C# 8.0
In the blog post Entity Framework Core: Use TransactionScope with Caution! we saw a few cases we have to be aware of when using . Here is another one that can lead to an exception when using the new C# 8.0 feature: . First, we look at a code snippet without C# 8.0 feature: Which…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Include Filters
The Entity Framework Core (EF) extension method provides us the ability to load additional data besides the entities we are querying for. For example: loading products along with their translations. In some use cases we want to load all translations for the requested products…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Making RowNumber (more) useful
In the blog post ROW_NUMBER Support we saw how to implement a custom function and in the previous post Improved Value Conversion Support we realized that inserting and selecting custom types is one thing but using them for filtering is something totally different. Let's take a…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Improved Value Conversion Support
Entity Framework Core (EF) 2.1 introduced a new feature called Value Conversion. Now, we are able to map custom .NET types to a type the database understands and vice versa. This long-awaited feature is especially popular among software engineers following the domain driven…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Custom Functions (using HasDbFunction)
In the previous post (Custom Functions - using IMethodCallTranslator) we looked at the more flexible approach that requires some boilerplate code. For the usage of a custom function in a project, especially if there are no plans to make the function public then the sacrifice of…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Custom Functions (using IMethodCallTranslator)
In the previous blog post (Entity Framework Core: ROW_NUMBER Support) we saw how to use the custom function with Entity Framework Core (EF). Now, we will look into the implementation. In the next and the last post of this series we will look ath the 2nd approach that uses the…
Pawel Gerr
Entity Framework Core
Entity Framework Core - ROW_NUMBER Support
There are some use cases that force me to use raw T-SQL instead of using LINQ. One of them is the need for ROW_NUMBER. But not anymore... One again, due to great work of Entity Framework (EF) team we are able to add more capabilities to EF quite easily. At the moment there are…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Contains Check via Temp Tables
Motivation One of the main queries in my projects is to select multiple database records having a collection of some kind of identifiers. Most of the time it works very well using the method . The query above is translated by the Entity Framework Core (EF Core) to clause: Most…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Isolation of Integration Tests
When working with Entity Framework Core (EF) a lot of code can be tested using the In-Memory database provider but sometimes you want (or have) to go to the real database. For example, you are using not just LINQ but custom SQL statements due to performance reasons or you want to…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Changing DB Migration Schema at Runtime
In the first part of this short blog post series we looked at how to change the database schema of a DbContext, now it is all about changing the schema of the EF Core Migrations at runtime. The samples are on Github: PawelGerr/EntityFrameworkCore-Demos Given is a  implementing…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Use TransactionScope with Caution!
One of the new features of Entity Framework Core 2.1 is the support of TransactionScopes. The usage of a  is very easy, just put a new instance in a , write the code inside the block and when you are finished then call to commit the transaction: But, before changing your code…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Changing Database Schema at Runtime
At the moment there is no built-in support for changing the database schema at runtime. Luckily, Entity Framework Core (EF) provides us with the right tools to implement it by ourselves. The demos are on GitHub: github:PawelGerr/EntityFrameworkCore-Demos Given are a database…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 2 - Database First)
In the previous post we have created 2 Entity Framework Core (EF Core) models with a code first approach. One model was using the Table-per-Hierarchy (TPH) pattern and the other one Table-per-Type (TPT). In this post we want to approach a more common scenario we see in customer…
Pawel Gerr
Entity Framework Core
Entity Framework Core - Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 1 - Code First)
With O/R mappers there are a few patterns how a class hierarchy can be mapped to a relational database. The most popular ones are the Table-Per-Hierarchy (TPH) and the Table-Per-Type (TPT) patterns. The Entity Framework Core 2.x (EF Core) officially supports the Table-per…
Pawel Gerr
Entity Framework Core
Entity Framework Core 2.1 Performance - Beware of N+1 Queries (Revisited)
In the previous post we have identified some Entity Framework (EF) LINQ queries that are affected by so called N+1 queries problem. In the meantime a new version (2.1-RC1) of Entity Framework has been released so we check the SQL statement generation yet another time. Samples…
Pawel Gerr
Entity Framework Core
Entity Framework Core Performance - Beware of N+1 Queries
After working with Entity Framework 6 (EF 6) for several years, a software developer can predict the SQL statements being generated by EF just by looking at the LINQ queries. With Entity Framework Core (EF Core) the SQL statement generation has changed - in some cases for the…
Pawel Gerr
Entity Framework Core
Entity Framework Core Migrations - Assembly Version Mismatch
If you have switched your .NET Core project from to (MSBuild) and updated the nuget packages then you may run into an issue when executing some of the -commands. I got the following error after executing : The problem is that some of my (3rd party) dependencies are using…
Pawel Gerr
Entity Framework
Entity Framework - Prevent redundant JOINs - watch your LINQ !
Fetching one record from a collection using navigational properties in Entity Framework may lead to unnecessary JOINs. To show the problem we need two tables Products and Prices. The query shown below is fetching products along with their first price. Looks simple. Lets look at…
Pawel Gerr
Entity Framework
Entity Framework - High performance querying trick using SqlBulkCopy and temp tables
Implementing database access with Entity Framework is pretty convenient, but sometimes the query performance can be very poor. Especially using navigational properties to load collections leads to significantly longer execution times and more I/O. To see the impact of the loading…
Pawel Gerr