Solve real coding problems
When you want to select a single row with Dapper, the simplest option is to use QuerySingleOrDefault() with a SELECT + WHERE query (and pass in the parameters / model type like usual). Here’s an example: This SQL query returns a single row from the Movies table and Dapper maps it and returns a Movie … Read more
There are five main aggregate functions in SQL: COUNT(), SUM(), AVG(), MIN(), and MAX(). These calculate aggregate values for sets of rows. When you use them with GROUP BY, they calculate the aggregate values for each group. Let’s say you want to get movie streaming stats for each user. Here’s an example of using GROUP … Read more
You can use GROUP BY to group rows based on one or more columns. This produces one row per group in the query results. Each group contains the grouping columns and the result(s) of any aggregate function(s) (COUNT/MIN/MAX/AVG/SUM) you want to use on the group. Let’s say you have the following table and you want … Read more
The simplest way to connect to a MySQL database in a .NET project is to use the MySql.Data package (from Oracle). It provides classes that implement the standard ADO.NET interfaces (such as IDbConnection). First, add the MySql.Data package to your project (this is using View > Other Windows > Package Manager Console): Now use the … Read more
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
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
You can use the sp_msForeachTable stored proc in SQL Server to execute a query/command against all tables in the database. To give you an idea about how to use this, here’s an example of using it to do a SELECT query against all tables: Internally, sp_msForeachTable gets all user table names (from dbo.sysobjects) and opens … Read more
There are two ways to filter GROUP BY results: In other words, WHERE controls which rows will be considered for grouping, GROUP BY groups the filtered rows, and then HAVING filters the groups. I’ll show examples below. Example – Using WHERE with GROUP BY Let’s say you have the following table and you want to … Read more