Case sensitivity in SQL Server

In SQL Server, the collation property controls case sensitivity. Case sensitivity affects sorting and queries (even the column names must match exactly if you’re using a case-sensitive collation at the database-level). The default collation when you create a database in SQL Server is SQL_Latin1_General_CP1_CI_AS. The CI stands for case-insensitive, which means SQL Server databases are … 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 – 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 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

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 if there are any pending migrations: To check which migrations have been applied: To apply a specific migration: This migrates up or … Read more