Ravindra Devrani

Table structure and total records

Let’s look at our table structure as shown in the left panel of the picture

Note: There is a non-clustered index on the Title column of the Book table, which has a total of 1 million records.

select
  Id,
  Title,
  Author,
  Price
from Book where title='The Divine Comedy'

As you have noticed in the execution plan, there is a key lookup. Since index is only available for the Title column, Title is retrieved from the non-clustered index. However, other columns like Id, Author and Price are not present in the non-clustered index, so they will be retrieved from the clustered index (the Book table). This results in an additional key lookup step and may decrease the performance. It’s like, you are looking for records in two places (non clustered index and clustered index).

Example Setup

I am using a MinimalApi application. Create a new one if you need.

dotnet new webapi -n DITest

Open it in a IDE or editor of your choice.

public interface IMyService
{
    Guid InstanceId { get; }
}

public class MyService : IMyService
{
    public Guid InstanceId { get; }

    public MyService()
    {
        InstanceId = Guid.NewGuid();
        // We are logging in the constructor, so that we get notified whenever the instance is created
        Console.WriteLine($"==> Service created with InstanceId: {InstanceId}");
    }
}

IMyService have a read-only property named InstanceId of type Guid, which is set from the constructor. We are logging inside the constructor, so that we can get notified whenever the new instance is created.

There is also a video version of this tutorial.

Let’s understand it with example.

int[] nums1 = [1, 2, 3, 4];
int[] nums2 = [3, 4, 5, 6];

IEnumerable<int>? product = nums1.Zip(nums2, (n1, n2) => n1 * n2);

Console.WriteLine(string.Join(", ", product));  // 3, 8, 15, 24

Let’s break it down:

IEnumerable<int>? product = nums1.Zip(nums2, (n1, n2) => n1 * n2);

It takes nums1[i] and nums2[i], evaluates it (nums1[0]*nums2[0]) and returns it. Here i is the index of the array. For example.

I don’t think there is any need of introduction. Let’s jump to the code section. We coders understand with code more. Let’s understand the concept then you don’t need to remember any definition.

This is the recordset against which I am running queries.

First and foremost let’s say we need a record with LastName=”Doe”.

I have used database first approach in the .NET Framework 4.X. But It is the first time I am trying to use Db First approach in the .NET Core (I am using .net 9). Microsoft refers it ‘Scaffolding (Reverse Engineering)’. Personally I prefer Dapper for DB First approach. May be you have an existing database and you want to use it with ENTITY FRAMEWORK, then this approach might be helpful. I am playing around it and documenting it and sharing that journey with you. Let’s see what it offers.

Schema

• Department (DepartmentId, Name)
• Employee (EmployeeId, Name, DepartmentId)

Result set I need

Show all departments with total number of employees. Do not skip the departments which have 0 employees. As shown below:

DepartmentIdNameTotalEmployees1Engineering22Marketing13HR0

I have applied various queries and checked their equivalent sql.

1. Straightforward but skips the department which has no employees

This query does not meet my requirement. It would be a good choice if I don’t need departments without any employees.

NOTE: You can find the source code here.

Schema Overview

// Department
public class Department
{
    public int DepartmentId { get; set; }
    public string Name { get; set; } = string.Empty;

    public ICollection<Employee> Employees { get; set; } = [];
}

// Employee

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; } = string.Empty;

    public int DepartmentId { get; set; }

    public Department Department { get; set; } = null!;

    public ICollection<EmployeeSkill> EmployeeSkills { get; set; } = [];
}

// Skills

public class Skill
{
    public int SkillId { get; set; }
    public string Name { get; set; } = string.Empty;

    public ICollection<EmployeeSkill> EmployeeSkills { get; set; } = [];
}

// EmployeeSkills (Junction table)

public class EmployeeSkill
{
    public int EmployeeId { get; set; }
    public int SkillId { get; set; }

    public Employee Employee { get; set; } = null!;
    public Skill Skill { get; set; } = null!;
}

In simpler terms:

A query is SARGable (Search ARGument able) if a database engine can take advantage of index.

What makes a query non SARGable?

These are the few factors that makes a query non sargable.

1. Put a column inside a function in the where clause.

select
 s.SalesOrderID,
 s.SalesPersonID,
 s.OrderDate,
 s.SalesOrderNumber
from Sales.SalesOrderHeader s
where YEAR(s.OrderDate) = 2011; -- Row Retrieved: 1607 and Row read : 31465

Note: I have created covering index on the OrderDate:

Initially, it was not intended to be an article. I was tinkering around sql and documenting few things, then I thought, it could turnout to a nice article. I have wrote lots of article and created lots of videos on c#, .net core and angular. But it is my first article on the SQL.

In this blog post, we are going to explore few terms of execution plan, like table scan, clustered index scan, index seek, RID lookup and key lookup.

In this tutorial we are going to learn:

• How to create a cli tool?
• How to create a nuget package?
• How to install it in our machine.

CLI tools are very useful, they are very easy to use. We are already using it in our every day.

dotnet new console -o CliToolDemo

It is the example of dotnet cli tool, simple way to create a .net console application. You can also create your own cli tools. We are going to use a package created by Microsoft named System.CommandLine , which is a pre-released version as of I am writing this blog post. It is a pre-released version for so long, I wonder what are the plans about this package. You can also use a library named Cocona to create cli tools, which is pretty simple and widely popular. But I am going to stick with System.CommandLine . You should definitely checkout the cocona.