C# – Adding dynamic parameters with Dapper

The simplest way to add dynamic parameters with Dapper is by passing in Dictionary<string, object>: You can also add dynamic parameter by using the DynamicParameters class. You can use whichever approach is simplest in the given scenario. In this article, I’ll show examples of adding dynamic parameters in different scenarios. Add dynamic parameters one at … Read more

C# – Map query results to multiple objects with Dapper

When you’re querying joined tables, you can map each row to multiple objects by using the multi mapping feature in Dapper. To multi map, you have to provide Dapper with the following: Which types to map to. Which column(s) to split on. This tells Dapper which columns it should try to map to which type. … Read more

C# – Executing basic SQL queries with Dapper

Here’s an example of using Dapper to execute a basic SELECT query: Dapper abstracts away the repetitive code involved in executing SQL queries, including mapping parameters and query results. It does this without any configuration (it maps by using reflection). In this article, I’ll show more examples of using Dapper in common scenarios, such as … Read more

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

Get SQL Server query results as JSON

The simplest way to get query results as JSON is to use FOR JSON PATH in the query (note: this was added in SQL Server 2016): It returns the results as a single JSON string with one JSON object per row: Note: SQL Server returns the JSON without indenting. All examples in this article show … Read more

SQL Server – How to use INFORMATION_SCHEMA to find database metadata

Let’s say you want to find all columns in a database that have the word ‘Name’ in the column name. You can query INFORMATION_SCHEMA.COLUMNS, like this: This query returns the following five rows showing the table name, column name, and column data type: TABLE_NAME COLUMN_NAME DATA_TYPE Actors FirstName nvarchar Actors LastName nvarchar Actors Fullname nvarchar … Read more

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

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 – 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