SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects

When you try to execute a query with EF Core that has parameters, you get the following exception: System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects The error message is confusing because it’s not showing the full type names. This error means you’re using System.Data.SqlClient.SqlParameter, but EF Core only accepts Microsoft.Data.SqlClient.SqlParameter. … Read more

EF Core – Inheritance mapping

There are two ways to do inheritance mapping in EF Core: Let’s say we have a database with employees. All employees have an id and a name. There are currently two types of employees: programmers and drivers. Programmers have a language (ex: C#), and drivers have a car (ex: Honda). We can model this with … 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

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 SQL aggregate functions – count, sum, and average. In each scenario, I’ll show the LINQ query, the SQL query it generated, and … Read more

EF Core – SELECT queries involving multiple tables

When you’ve created 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, when you are creating (or modifying) a table, you can, you can use the [Key] attribute to create primary key with one column. To create a composite primary key with multiple columns, you have to override DbContext.OnModelCreating() and … Read more

EF Core – How to add indexes

In this article, I’ll show how to add indexes using EF Core. If you’re not sure about why you would need indexes, take a look at this article explaining how indexes greatly improve query performance. Add an index with a single column The simplest way to add an index is to by adding the [Index] … Read more

EF Core – Add a foreign key

In this article, I’ll show how to add a foreign key using EF Core. Then I’ll show how foreign keys affect inserts and deletes. How to add a foreign key A Show has one or more Episodes. In this section, I’ll show how to link these two tables together to enforce the 1-to-many relationship. In … Read more

EF Core – Apply migrations programmatically

In EF Core, you create migrations when you are first creating the database and tables and also for any database schema changes. DbContext.Database has a few methods you can call to manage migrations programmatically. To apply any pending migrations: If the database doesn’t exist, MigrateAsync() will create it and then apply the migrations. To check … Read more

EF Core – Database schema changes

Anytime you change the definition of the database – from renaming a column to creating a table – it’s referred to as a database schema change. With EF Core, you deal with database schema changes by using migrations. When you first create the database with EF Core, you create a migration that contains the initial … Read more

EF Core – How to create a database and a table

In this article, I’ll show how to use EF Core to create a database with one table in it. At the end, I’ll show a standalone console app that inserts a record into this database. Note: I’ll be using SQL Server. I haven’t tried this with other database providers. Please leave a comment if you’re … Read more