The art of simplicity

Recently I ran into a nasty startup error after registering both AddDbContext and AddDbContextFactory for the same DbContext in an ASP.NET Core project: System.AggregateException: 'Some services are not able to be constructed' System.InvalidOperationException: 'Error while validating the service descriptor 'ServiceType: Microsoft.EntityFrameworkCore.IDbContextFactory`1[StartStopLijsten.ApiService.Data.StartStopDbContext] Lifetime: Singleton ImplementationType: Microsoft.EntityFrameworkCore.Internal.DbContextFactory`1[StartStopLijsten.ApiService.Data.StartStopDbContext]': Cannot consume scoped service 'Microsoft.EntityFrameworkCore.DbContextOptions`1[StartStopLijsten.ApiService.Data.StartStopDbContext]' from singleton 'Microsoft.EntityFrameworkCore.IDbContextFactory`1[StartStopLijsten.ApiService.Data.StartStopDbContext]'.' The app refuses to start, and although the message is clear why it refuses to start, it doesn't make it obvious how...

There's a moment when a transaction fails halfway through, and you realize rolling back everything is overkill. You did five inserts, the sixth one violates a constraint, and you'd rather undo just that last operation than start the whole transaction over. That's what savepoints are for, and EF Core has supported them for a while now but until recently I didn't know this feature existed. What's the problem with plain transactions? A regular database transaction is all-or-nothing. Something like this: using var transaction = await context.Database.BeginTransactionAsync(); try { context.Blogs.Add(new Blog { Url = "https://blog1.com" }); await context.SaveChangesAsync(); context.Blogs.Add(new Blog { Url = "https://blog2.com" }); await context.SaveChangesAsync(); // this one fails context.Blogs.Add(new Blog { Url = null }); await context.SaveChangesAsync(); await transaction.CommitAsync(); } catch { a...

If you've been using Entity Framework Core for a while, you've probably written a query like this: var ids = new[] { 1, 2, 3, 4, 5 }; var blogs = await context.Blogs .Where(b => ids.Contains(b.Id)) .ToListAsync(); Simple enough. But under the hood, how EF Core translates that ids collection into SQL has quietly changed(again) in EF Core 10, and this time the change is significant enough to be listed as a breaking change. Let's dig into what's happening, why the EF team made this call, and what it means for your applications. A brief history To understand EF Core 10's approach, it helps to know where things stood before. EF Core 8: The OPENJSON era In EF Core 8, when you passed a collection to a LINQ Contains or Where clause, EF encoded the entire collection as a JSON string and sent it as a single parameter. SQL Server would then unpack it using the OPENJSON function: SELECT [b].[Id], [b].[Name] FROM [Blogs] AS [b] WHERE [b].[Id] IN ( ...

When working with Entity Framework Core, understanding change tracking behavior is crucial for both performance and data consistency. While I was ware of the AsNoTracking() method, I discovered a lesser-known but powerful alternative: AsNoTrackingWithIdentityResolution() during a code review.  Let's explore what makes this method special and when you should use it. Quick recap: What is AsNoTracking()? Before diving into AsNoTrackingWithIdentityResolution , let's briefly review AsNoTracking() . By default, EF Core tracks all entities returned from queries in the change tracker. This tracking enables: Automatic detection of changes to entities Update operations without explicitly attaching entities Identity resolution (ensuring only one instance per entity exists in memory) However, tracking comes with overhead. When you're performing read-only operations where you don't need to update data, AsNoTracking() improves performance by skipping the change...

Debugging database queries in Entity Framework Core can sometimes feel like searching for a needle in a haystack. When your application generates dozens or hundreds of SQL queries, identifying which LINQ query produced which SQL statement becomes a real challenge. Fortunately, I discovered an elegant solution that EF Core provides: Query Tags . Query Tags Query Tags allow you to add custom comments to the SQL queries generated by your LINQ expressions. These comments appear directly in the generated SQL, making it incredibly easy to trace back from a SQL query to the specific code that created it. To use this feature you need to apply the TagWith method to your on any IQueryable and pass a descriptive comment: This generates SQL that looks like this: Instead of trying to reverse-engineer which code generated a particular SQL query, you can immediately see the purpose and origin of each query in your database logs or profiler. Advanced techniques Chaining multiple ta...

Today I was working with a team that was implementing a CQRS architecture. CQRS (Command Query Responsibility Segregation) is a design pattern that separates the responsibilities of reading and writing data into distinct models. The idea is to use one model to handle commands (which modify data) and another model to handle queries (which retrieve data). This separation allows for better scalability, performance optimization, and flexibility, as the read and write operations can be independently optimized or scaled based on the specific needs of the system. After creating a read model for a specific table in the database, EF core started to complain and returned the following error message: System.InvalidOperationException : Cannot use table 'Categories' for entity type 'CategoryReadModel since it is being used for entity type 'Category' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'CategoryReadModel' on the...

Athough EF Core is a developer friendly Object-Relational Mapper (ORM), working with it isn't without its challenges. One error that we encountered during a pair programming session was: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value In this blog post, we will delve into the causes of this error and explore ways to resolve it. "Constructing a database in the 18th century" - Generated by AI Understanding the error This error typically occurs when there is an attempt to convert a datetime2 value in SQL Server to a datetime value, and the value falls outside the valid range for the datetime data type. datetime : This data type in SQL Server has a range from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. datetime2 : This newer data type, introduced in SQL Server 2008, has a much broader range from January 1, 0001, to December 31, 9999, with an accuracy of 100 nanoseconds....

I was pair programming with a team member when she got the following compiler error: Error CS1503 Argument 2: cannot convert from 'string' to 'System.FormattableString' The error appeared after trying to compile the following code: The reason becomes apparent when we look at the signature of the SqlQuery method: As you can see the method expects a FormattableString not a string . Why is this? By using a FormattableString EF Core can protect us from SQL injection attacks. When we use this query with parameters(through string interpolation), the supplied parameters values are wrapped in a DbParameter . To get rid of the compiler error, we can do 2 things: 1. We explicitly create  a FormattableString from a regular string: 2. We use string interpolation and add a ‘$’ sign in front of the query string: More information SQL Queries - EF Core | Microsoft Learn RelationalDatabaseFacadeExtensions.SqlQuery<TResult> Method (Microsoft.EntityF...

In EF Core when fetching multiple entities in one request it can result in a cartesian product as the same data is loaded multiple times which negatively impacts performance. An example is when I try to load a list of Customers with their ordered products: The resulting query causes a cartesian product as the customer data is duplicated for every ordered product in the result set. EF Core will generate a warning in this case as it detects that the query will load multiple collections. I talked before about the AsSplitQuery method to solve this. It allows Entity Framework to split each related entity into a query on each table: However you can also enable query splitting globally in EF Core and use it as the default. Therefore update your DbContext configuration: More information EF Core–AsSplitQuery() (bartwullems.blogspot.com) Single vs. Split Queries - EF Core | Microsoft Learn

In Domain-Driven Design (DDD), an aggregate is a cluster of domain objects that are treated as a single unit for the purpose of data changes. The aggregate has a root and a boundary: Aggregate Root : This is a single, specific entity that acts as the primary point of interaction. It guarantees the consistency of changes being made within the aggregate by controlling access to its components. The aggregate root enforces all business rules and invariants within the aggregate boundary. Boundary : The boundary defines what is inside the aggregate and what is not. It includes the aggregate root and other entities or value objects that are controlled by the root. Changes to entities or value objects within the boundary must go through the aggregate root to ensure consistency. An example of an aggregate is an Order (which is the Aggregate root) together with OrderItems (entities inside the Aggregate). The primary function of an aggregate is to ensure data consist...

When mapping a decimal type to a database through an ORM like EF Core, it is important to consider the precision. You don't want to lose data or end up with incorrect values because the maximum number of digits differs between the application and database. If you don’t explicitly configure the store type, EF Core will give you a warning to avoid losing precision. Imagine that we have the following Product class with a corresponding configuration: If we try to use this Product class in our application, we get the following warning: warn: SqlServerEventId.DecimalTypeDefaultWarning[30000] (Microsoft.EntityFrameworkCore.Model.Validation)       No store type was specified for the decimal property 'UnitPrice' on entity type 'Product'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values in 'OnModelCreating' using 'Has...

The .NET 8 release of Entity Framework Core offers a large list of new features. The goal of this post is not to walk you through all these features, therefore you can have a look at the What's new page on Microsoft Learn , instead I want to talk about a specific feature as a follow-up on the previous posts about EF Core I did this week. In those posts I talked about the FromSql method to use your handwritten SQL statements to fetch EF Core entities. What I didn’t mention is that this only worked for entities that were registered as an entity to the DbContext. Starting with the EF Core 8 release, this condition has been removed, allowing us to create any SQL statements you want and map them to C# objects. This means that EF Core can now become an alternative to micro-ORM’s like Dapper . Of course there is maybe still a performance difference(I’ll do a benchmark and share the results) but feature wise this is a great addition. To use this feature, we need to call the SqlQue...

Yesterday I talked about an error I got when using the FromSql method in Entity Framework Core(EF Core). It allows you to execute raw SQL queries against a relational database. Here is the example I was using yesterday: Remark: The FromSql method was introduced in EF Core 7.0. In older versions, you should use FromSqlInterpolated instead. However when we tried to execute the code above it failed with the following error message: System.InvalidOperationException : The required column 'Id' was not present in the results of a 'FromSql' operation. You typically get the  error message above when there isn’t a matching column found in the SQL result for every property in your entity type. In our case the query was using a ‘SELECT *’ so I couldn’t be that we missed a column. To explain what was causing the issue, I first have to show our entity type and mapping class: As you can see there is a difference between the name of one of our columns(ProductID) and t...

While doing a pair programming session with a new developer in one of my teams, we ended up with a compiler error after writing the following code: This is the error message we got: Argument 2: cannot convert from 'string' to 'System.FormattableString'   The fix was easy just add a ‘$’ before the query: However it would not have been a good pair programming session if we didn’t drill down further into this. What is a FormattableString? A FormattableString in C# is a type introduced in .NET 4.6. It represents a composite format string, which consists of fixed text intermixed with indexed placeholders (format items). These placeholders correspond to the objects in a list. The key features of FormattableString are: Capturing Information Before Formatting : A FormattableString captures both the format string (similar to what you’d pass to string.Format , e.g., "Hello, {0}" ) and the arguments that would be used to format it. ...

Last week I had the pleasure to work with a team that started using Entity Framework Core for the first time. They had a lot of experience using NHibernate, so the concept of an ORM was not new. But it was interesting to see which things are obvious when switching to EF Core and which are not. After a few hiccups the team was finally on a role and they were starting to add more and more features. A (last?) question I got was regarding the usage of the DateOnly and TimeOnly types. These types were introduced in .NET 6 and are a welcome addition next to the DateTime type. The question was of course if we could use these types in combination with EF Core? These type have been supported for several database providers (e.g. SQLite, MySQL, and PostgreSQL) since their introduction. Unfortunately for SQL Server, we had to wait for a recent release of a Microsoft.Data.SqlClient package. So starting from EF8 DateOnly and TimeOnly are supported for SQL Server as well. Remark: If yo...

Last week I had the pleasure to work with a team that started using Entity Framework Core for the first time. They had a lot of experience using NHibernate, so the concept of an ORM was not new. But it was interesting to see which things are obvious when switching to EF Core and which are not. I thought the team was finally on track after a few bumps in the road when they came back to me with another problem. When trying to fetch a set of entities, the query failed with the following error message: System.Data.SqlTypes.SqlNullValueException : Data is Null. This method or property cannot be called on Null values. This was the object they were trying to fetch: And here is the LINQ query they were using: I have to admit that it took me a while before I discovered why this failed. The reason becomes obvious if you take a look inside the csproj file: This project had Nullable Reference Types enabled.  If this is enabled and you have a required property that shouldn...

Last week I had the pleasure to work with a team that started using Entity Framework Core for the first time. They had a lot of experience using NHibernate, so the concept of an ORM was not new. But it was interesting to see which things are obvious when switching to EF Core and which are not. The start was a little bit of trial and error , but the next time they contacted me wasn’t to solve the next problem. Progress! Instead they had a question… In Nhibernate the mapping between your classes and database tables is typically done through HBM files(if you like XML) or through code. In EF Core you can override the OnModelCreating method on the DbContext : Using separate files for your mapping code As the team was used to have separate files for the mapping code, they were wondering if this was possible with EF Core as well. Luckily, the answer is yes. To do that you need to implement the IEntityTypeConfiguration interface: and change the OnModelCreating implementatio...

Last week I had the pleasure to work with a team that started using Entity Framework Core for the first time. They had a lot of experience using NHibernate, so the concept of an ORM was not new. But it was interesting to see which things are obvious when switching to EF Core and which are not. Yesterday I shared a first problem they encountered. Shortly after I explained a possible solution they contacted me back with a new error message: System.ArgumentException : 'AddDbContext' was called with configuration, but the context type 'NorthwindDbContext' only declares a parameterless constructor. This means that the configuration passed to 'AddDbContext' will never be used. If configuration is passed to 'AddDbContext', then 'Northwind1DbContext' should declare a constructor that accepts a DbContextOptions<NorthwindDbContext> and must pass it to the base constructor for DbContext. So what was the problem this time? They clearly didn’t we...

Last week I had the pleasure to work with a team that started using Entity Framework Core for the first time. They had a lot of experience using NHibernate, so the concept of an ORM was not new. But it was interesting to see which things are obvious when switching to EF Core and which are not. The first time they contacted me they had the following code in place: A DbContext (I switched the code to a simpler example): And a minimal configuration in the Program.cs file: When executing this code, it resulted in the following error message: No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions<TContext> object in its constructor and passes it to the base constructor for DbContext. I think this error message is quite helpful but the...

Yesterday I talked about lazy loading and how you can avoid the usage of proxy objects in NHibernate. Writing that post made me wonder if a similar thing is possible in Entity Framework Core. Let's find out! Remark: If you want to learn more about the concept of lazy loading check out my previous post first. Lazy loading in Entity Framework Lazy loading in Entity Framework works quite similar to NHibernate and also uses a proxy object by default. Therefore you need to install the Microsoft.EntityFrameworkCore.Proxies package and enabling it with a call to UseLazyLoadingProxies . Contrary to NHibernate you don’t need to make all your properties virtual but only those where lazy loading should be enabled: Lazy loading without a proxy Lazy loading without a proxy object in Entity Framework IS possible although it requires a bit more work than when using NHibernate. For Entity Framework you need to explicitly inject the ILazyLoader service and than use this service ...