SQL Server OUTER APPLY

 OUTER APPLY is a powerful operator used to join each row from an outer table to the results of a table-valued function or subquery. It behaves like a LEFT JOIN, but works with correlated subqueries that return multiple columns.

OUTER APPLY:

• Evaluates the right-hand subquery for each row of the left table.

• Returns all rows from the left table.

• Returns matching rows from the APPLYed subquery — or NULLs when the subquery returns nothing.

It’s similar to:

Operator                      BehaviorCROSS APPLY         Like INNER JOINOUTER APPLY         Like LEFT JOIN

E.g

SELECT 

    c.CustomerID,

    c.Name,

    o.OrderID,

    o.OrderDate

FROM Customers c

OUTER APPLY (

    SELECT TOP(1) *

    FROM Orders o

    WHERE o.CustomerID = c.CustomerID

    ORDER BY o.OrderDate DESC

) o;

Cheers

Samitha 

Copilot vs ChatGPT vs Gemini AI

 

 

Choosing between Microsoft Copilot, ChatGPT, and Gemini AI depends on your environment and what you need the AI to do.

• Microsoft Copilot is the best fit for organizations deeply using the Microsoft 365 ecosystem. It excels at structured, context-based tasks such as automating Power BI workflows, assisting in Teams, and enhancing productivity and collaboration across Office apps.

• ChatGPT is the most versatile and flexible option. It suits creatives, educators, developers, and businesses needing customizable solutions for content creation, brainstorming, coding, or general problem-solving. Its adaptability makes it ideal for both individuals and startups across many industries.

• Gemini AI shines with its multimodal strengths and smooth integration into Google Workspace. It’s excellent for multimedia content creation, real-time insights, and team collaboration—making it a great choice for marketing teams, creative professionals, and companies already using Google’s ecosystem.

Ultimately, the right tool depends on your tech stack, your workflow, and the type of AI assistance you require.

Cheers

Samitha

GitHub Copilot

 GitHub Copilot is an AI-powered coding companion that helps with code completion, editing, reviewing, documenting, and managing projects, while offering customization, enterprise control, and integration across multiple development environments.

Core Coding Features

• Code Completion:
  Autocomplete-style code suggestions in supported IDEs (VS Code, Visual Studio, JetBrains, etc.).
  VS Code also supports next edit suggestions to predict your next likely edit.

• Copilot Chat:
  AI chat assistant for coding help, available in IDEs, GitHub.com, GitHub Mobile, and Windows Terminal.
  Supports “skills” for specialized tasks.

• Copilot Edits:
  Lets Copilot modify code directly across multiple files.

  • Edit Mode: User controls files and iterations (for precise updates).

  • Agent Mode: Copilot autonomously edits and iterates to complete complex tasks.

• Copilot Coding Agent:
  An autonomous AI that can make code changes, resolve issues, and create pull requests automatically.

• Copilot CLI (Public Preview):
  Command-line tool for asking Copilot questions, editing local files, and managing GitHub issues or pull requests.

Collaboration & Review

• Copilot Code Review:
  AI-generated code review feedback and suggestions.

• Copilot Pull Request Summaries:
  Automatically summarizes PR changes and areas to review.

• Copilot Text Completion (Preview):
  Writes pull request descriptions quickly and clearly.

• Copilot in GitHub Desktop:
  Suggests commit messages and descriptions automatically.

Customization & Extensions

• Copilot Extensions:
  Integrate third-party tools into Copilot Chat; available on GitHub Marketplace.

• Copilot Custom Instructions:
  Tailor Copilot responses based on personal or project-specific preferences.

• Copilot Spaces:
  Centralize project resources (code, docs, specs) for contextualized Copilot responses.

• Copilot Knowledge Bases (Enterprise):
  Add organization-specific docs as context for Copilot Chat queries.

Advanced Tools

• GitHub Spark (Preview):
  Build and deploy full-stack apps using natural language prompts.

• Copilot Coding Agent (Advanced):
  Executes multi-step coding tasks, handles errors, and integrates with external systems.

Enterprise & Admin Features

• Policy Management:
  Control which Copilot features are enabled across orgs and enterprises.

• Access Management:
  Define who can use Copilot within organizations.

• Usage Data & Audit Logs:
  Track adoption, usage metrics, and user activity for governance.

• Exclude Files:
  Prevent Copilot from accessing sensitive or irrelevant files.

Cheers

Samitha

Securing Connection Strings & AppSettings in ASP.NET Core

 ASP.NET Core apps use appsettings.json to store configuration like connection strings, API keys, and secrets. If unprotected, these can expose sensitive data.

Why Security Matters

• Connection strings often include DB usernames & passwords.

• API keys/secrets can unlock external services.

• Misconfigurations may leak data in logs or errors.

• Compromised values can lead to data theft, privilege escalation, or abuse.

Default Configuration Sources

• appsettings.json

• appsettings.{Environment}.json

• Environment variables

• User Secrets (development)

• Key vaults/Secrets managers (production)

Security Options

1. User Secrets (Dev Only)

   • Stored locally, not in source control.

   • dotnet user-secrets set "ConnectionStrings:DefaultConnection" "..."

2. Environment Variables

   • Safer than JSON files, follows 12-factor app principles.

   • Example (PowerShell):

     $env:ConnectionStrings__DefaultConnection="..."
     

3. Cloud Secret Managers (Production)

   • Azure Key Vault

     builder.Configuration.AddAzureKeyVault(
         new Uri($"https://{builder.Configuration["KeyVaultName"]}.vault.azure.net/"),
         new DefaultAzureCredential());
     

   • AWS Secrets Manager

     var secret = await secretsManager.GetSecretValueAsync(
         new GetSecretValueRequest { SecretId = "MyApp-DbConnection" });
     

Best Practice: 

Use User Secrets for local dev, Environment Variables for staging, and a Cloud Secret Manager for production.

Cheers

Samitha

SQL Server 2025 (preview) working with JSON

 

Working with JSON in SQL Server

SQL Server allows you to store JSON text in database tables and use built-in functions to query or modify it using standard T-SQL.

1. Extract Values from JSON

You can extract values using:

JSON_VALUE: Returns a scalar value (e.g., string, number).

JSON_QUERY: Returns an object or array.

E.g.

SELECT JSON_VALUE(jsonCol, '$.info.address.Town') AS Town

FROM People

WHERE ISJSON(jsonCol) > 0;

SELECT JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People;

2. Modify JSON Data

Use JSON_MODIFY to update parts of a JSON string.

E.g

DECLARE @json NVARCHAR(MAX);

SET @json = '{"city":"Paris"}';

SET @json = JSON_MODIFY(@json, '$.city', 'London');

SELECT @json AS ModifiedJson;

-- Result: {"city":"London"}

3. Convert JSON to Rowset

Use OPENJSON to transform JSON arrays into tabular data.

E.g

DECLARE @json NVARCHAR(MAX) = N'[

  {"id": 1, "name": "Alice"},

  {"id": 2, "name": "Bob"}

]';

SELECT *

FROM OPENJSON(@json)

WITH (

    id INT,

    name NVARCHAR(100)

);

4. Validate JSON

E.g

Checks if a string is valid JSON (returns 1 if true).

SELECT ISJSON('{"name":"value"}') AS IsJson; -- Returns 1

SELECT ISJSON('invalid') AS IsJson;          -- Returns 0

5. Combined JSON

SELECT 

  Name,

  JSON_VALUE(jsonCol, '$.info.address.Town') AS Town,

  JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People

WHERE ISJSON(jsonCol) = 1

  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade';

Cheers

Samitha

SQL Sever Introduces Copilot (preview)

 Copilot in SSMS helps you ask questions about your database and environment, and assists in writing T-SQL with AI. It doesn’t retain or use your data for training. Copilot works with SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL Database in Fabric. Query execution respects your login permissions—if you lack access to certain objects, Copilot can generate queries but cannot execute them.

read here for more information.

Cheers

Samitha

C# 14 Extension members

 C# 14 introduces new syntax for defining extension members, expanding on traditional extension methods. Key additions include:

• Extension properties and indexers alongside extension methods.

• Ability to define static extension members, making them appear as if they are static members of the extended type

Two Types of Extension Blocks:

Instance-like extension members (e.g., sequence.IsEmpty):

 

extension<TSource>(IEnumerable<TSource> source)

{

    public bool IsEmpty => !source.Any();

    public TSource this[int index] => source.Skip(index).First();

    public IEnumerable<TSource> Where(Func<TSource, bool> predicate) { ... }

}

Static-like extension members (e.g., IEnumerable<int>.Identity):

 

extension<TSource>(IEnumerable<TSource>)

{

    public static IEnumerable<TSource> Combine(IEnumerable<TSource> first, IEnumerable<TSource> second) { ... }

    public static IEnumerable<TSource> Identity => Enumerable.Empty<TSource>();

}

These features allow more natural syntax and better integration with existing types by simulating both instance and static members via extensions.

Cheers

Samitha

ASP.NET Core changes in .NET 9 MapStaticAssets

 MapStaticAssets is a new feature that enhances the delivery of static assets in web applications. It is intended as a  replacement for UseStaticFiles.  

MapStaticAssets offers key advantages over UseStaticFiles, including:

• Build-time compression:

  • Uses gzip during development and gzip + Brotli during publish, minimizing asset sizes.

• Content-based ETags:

• Generates ETags from the SHA-256 hash of each file’s content, ensuring browsers only re-download files when their content changes

Read more here.

Cheers

Samitha

EF Core Vs Dapper

 Both Dapper and EF Core are excellent ORM options suitable for projects of any size. The choice between them largely depends on personal or organizational preferences. Dapper appeals to those who prefer writing raw SQL queries, while EF Core is favored by those who prefer working with LINQ. Each has its own strengths and weaknesses

You can learn more on Dapper vs EF Core here.

Cheers

Samitha

C# 14 New feature for Null conditional assignment

The preview of .NET 10 and the upcoming C# version introduces a potential new feature: null-conditional assignment. This aims to simplify code where you assign a value to a property only if the object isn't null.

Currently, you'd write:

if (classObj is not null)

{

  classObj .Property = 100;

}

With the new feature, this can be shortened to:

classObj ?.Property = 100;

This makes the code more concise and readable when dealing with potentially null objects.

Cheers,

Samitha

EF Core 10 Support for .NET 10 LeftJoin and RightJoin operators

 In earlier versions of EF Core, performing a LEFT JOIN in LINQ required a complex combination of SelectMany, GroupJoin, and DefaultIfEmpty.

With .NET 10, a new built-in LeftJoin method simplifies this process. It allows developers to write cleaner and more intuitive LINQ queries, and EF Core fully supports it in database queries.

Example:

var studentDepartments = context.Students

    .LeftJoin(

        inner: context.Departments,

        outerKeySelector: student => student.DepartmentID,

        innerKeySelector: department => department.ID,

        resultSelector: (student, department) => new

        {

            FirstName = student.FirstName,

            LastName = student.LastName,

            Department = department.Name ?? "[NONE]"

        });

Cheers

Samitha

Entity Framework Selectmany

 In Entity Framework Core, the SelectMany operation flattens collections, which can lead to unexpected results if not carefully applied. For example, in a query that filters students and their active courses:

var activeCourses = await _employeeRepository

    .GetAllEmployees()

    .Where(employee=> employee.IsActive)

    .SelectMany(employee=> employee.PayeDetails)

    .Where(payDetail=> payDetail.IsTermnated)

    .ToListAsync();

The Where clause before SelectMany filters the employees, but after SelectMany, the query works with a flattened collection of pay details. This means the second Where clause filters the pay details, not the employees. As a result, you might get pay details from terminated employees because the filtering is done on the flattened collection of pay details, not the original employee collection. 

In some cases Selectmany can act like a CrossJoin as well.

Cheers

Samitha

Ef .Net Core DbSet.ExecuteUpdate

 EF Core 7.0 introduces the ExecuteUpdate method, which allows for updating entities in the database based on a query result. However, it requires explicit specification of the changes to be made, as EF Core does not automatically detect them. Tracked entities will not remain in sync, and additional commands may need to be issued in a specific order to avoid violating database constraints (e.g., updating dependents before deleting a principal).

The ExecuteUpdate method complements, rather than replaces, the existing SaveChanges mechanism. It functions similarly to the ExecuteDelete method, but with the key difference that an update requires specifying which properties to update and how to do so, typically using calls to SetProperty.

Cheers.

Samitha

Return no operation form a task implementation

 When you want to return a no operation result from a task implementation from an endpoint you can use following to return no operation performed.

 public Task LongRunningAsync()

        {

            // do some work

            var x = 1;     

         // Can't return 'null' 

            return Task.FromResult<object<(null);

        }

Cheers,

Samitha

Package Manager Console error The type initializer for 'System.Management.Automation.Runspaces.InitialSessionState' threw an exception

Recently I came across the error The type initializer for 'System.Management.Automation.Runspaces.InitialSessionState' threw an exception, when I am trying to open the Package Manager Console.

As per comment form Yishai Galatzer  this is caused by a stack overflow bug in a PowerShell DLL 

Workaround

In your Visual Studio folder, make a backup copy of file devenv.exe.config.

Then, in the original devenv.exe.config file, insert the following after the opening assemblyBinding element(run as admin) and save the file

<!-- WORKAROUND START ->
<dependentAssembly>
        <assemblyIdentity name="System.Management.Automation" publicKeyToken="31bf3856ad364e35" />
        <publisherPolicy apply="no" />
      </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.PowerShell.Commands.Utility" publicKeyToken="31bf3856ad364e35" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.PowerShell.ConsoleHost" publicKeyToken="31bf3856ad364e35" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.PowerShell.Commands.Management" publicKeyToken="31bf3856ad364e35" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.PowerShell.Security" publicKeyToken="31bf3856ad364e35" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.PowerShell.Commands.Diagnostics" publicKeyToken="31bf3856ad364e35" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
<!-- WORKAROUND END -->

To Make the changes effect you'll need to restart VS.

Cheers,

Samitha