EF Core – Inheritance mapping

There are two ways to do inheritance mapping in EF Core: Table-per-Hierarchy (TPH) = There’s a single table for all classes in the hierarchy. Table-per-Type (TPT) = There’s one table per class in the hierarchy. This is available in EF Core 5 and above. Let’s say we have a database with employees. All employees have … Read more

C# – Select distinct objects based on a property

There are three ways to select distinct objects based on a property using Linq: These select one movie per year: GroupBy() is the simplest because it doesn’t require any additional code. Distinct() is faster but it’s more complicated. DistinctBy() is the fastest and simplest, but requires the most code (it requires .NET 6 or getting … Read more

How to do retries in EF Core

EF Core has built-in retry functionality. To use it, you can call options.EnableRetryOnFailure(), like this: The retry logic is contained in execution strategy classes. The above code is using the default execution strategy class (SqlServerRetryingExecutionStrategy). When you execute a query, it goes through the execution strategy class. It executes the query and checks for transient … Read more

C# – How to use table-valued parameters (TVP) with ADO.NET, Dapper, and EF Core

Table-valued parameters (TVP) allow you to send multiple rows of data as a parameter into SQL queries. TVPs are flexible. They can be used in both raw SQL queries and with stored procs, and can be used in all types of queries, from inserts to selects. In this article, I’ll show how to use TVPs … Read more

SqlException: Cannot insert explicit value for identity column

When you have a table with an identity column, and you try to specify the value for identity column when inserting a record, you’ll get the following exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table ‘Movies’ when IDENTITY_INSERT is set to OFF. This error means you have an identity column in … Read more

EF Core – Aggregate SELECT queries

In this article, I’ll show how to use EF Core to aggregate data for the whole table, per group, and how to only include groups that meet a condition. I’ll show three different aggregates – count, sum, and average. In each scenario, I’ll show the LINQ query, the SQL query it generated, and the results … Read more

EF Core – SELECT queries involving multiple tables

When you have tables that are related, you’ll often need to get data from both tables at once, or filter records from one table based on values in another table. In this article, I’ll show examples of executing queries like this where more than one table is involved. You can do most queries using LINQ. … Read more

EF Core – Basic SELECT queries

In this article, I’ll show examples of how to execute basic SELECT queries when using EF Core. You can execute queries using LINQ or by writing raw SQL. I’ll use SQL Profiler to show the queries generated by LINQ. Note: I’ll be using .AsNoTracking().ToListAsync() in all cases. You’ll need to decide if that’s the right … Read more

EF Core – How to add a computed column

To add a computed column in EF Core, override DbContext.OnModelCreating() and specify the computed column using ModelBuilder, like this: In this article, I’ll show a full example of adding a computed column and then show how to specify that the computed column should be persisted. Example of adding a computed column Let’s say we have … Read more

EF Core – How to create a composite primary key

A composite primary key is a primary key that contains more than one column. In EF Core, to create a primary key with one column, you would use the [Key] attribute. To create a composite primary key with multiple columns, you have to override DbContext.OnModelCreating() and then specify all of the columns included in the … Read more