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

SQL Server – Bad SELECT query performance due to missing indexes

When you have a slow SELECT query, the most likely cause of the problem is that you’re missing an index. When your table has lots of data, having the right indexes will make a difference in the performance. Without indexes, SQL Server has to scan through your entire table to find the rows that match … Read more

C# – Using SqlDataReader to process multiple result sets

In this article I’ll show how to use the SqlDataReader ADO.NET class in two scenarios involving multiple result sets: Batches – When you execute multiple SELECTs in a single query. Each SELECT returns a different result set. You use a single reader to process the batch. Nested Queries – When you use multiple SqlDataReaders on … Read more

Querying JSON in SQL Server

In SQL Server 2016 they added support for handling JSON data. You can add JSON data, query it, and modify it. The JSON API in SQL Server is well-suited for simple scenarios where you only need to do basic querying and manipulation. For advanced querying, the SQL Server JSON API gets complicated and hard to … Read more