Showing posts with label Azure. Show all posts
Showing posts with label Azure. Show all posts

Sunday, June 29, 2025

Build and deploy a remote MCP server with Azure Functions and C#

In this walkthrough, we will build an MCP Server based on Azure Funtions and C#. It will be tested locally, then deployed to Azure using the az tool. To avoid confusion, we will go through this journey step-by-step. Although the methods we implement are very basic, the same concepts can be extended to cover much more sophisticated solutions.

MCP, what is that?

The Model Context Protocol (MCP) is a specification created by Anthropic that makes it easier for AI applications to talk to tooling.

Why Remote MCP Servers?

Installing the same MCP server locally everywhere you need it is unrealistic. Making sure people on your team have the same version installed is a daunting task.

The solution is MCP servers that run remotely. As long as the endpoint supports server-side events (SSE), you are good to go.

Prerequisites

Getting Started

We will create a vanilla C# console application with:

mkdir RemoteMcpFunc
cd RemoteMcpFunc

 Open the console application in VS Code with:

code .

Click on the Azure tool on the left-side, then click on the "Azure Functions" tool and choose "Create New Project...".

On the "Create new project" panel, choose the already highlighted current directory:

Next, choose C#.

Choose the latest version of .NET, which at the time of writing is ".NET 9.0 Isolated".

We will keep it simple by choosing the "HTTP trigger" template.

It the "Create new HTTP trigger" panel, provide a name for the Azure Function class file. In my case I simply named it HttpMCP.

Next you will provide a namespace (like Mcp.Function).

Again, to keep it simple, we will choose Anonymous.

To make sure that our basic Azure Function works as expected, type this command in the root of your project:

func start

This should show you output similar to this:

If you point your browser to the given URL, you will experience a welcome message similar to this:

Hit CTRL C to stop the application.

Add MCP package

At this stage our Azure Functions application has no MCP capability. We will change this by adding the following package:

dotnet add package Microsoft.Azure.Functions.Worker.Extensions.Mcp --prerelease

NOTE: At this time, the above package is experimental and in pre-release mode.

Add MCP smarts

We will add three simple MCP server functions with these abilities:

  1. the classic Hello World
  2. reverse a message
  3. multiply two numbers

Add the following ToolDefinitions.cs class file:

public static class ToolDefinitions {
  public static class HelloWorldTool {
    public const string Name = "HelloWorldTool";
    public const string Description = "A simple tool that says: Hello World!";
  }


  public static class ReverseMessageTool {
    public const string Name = "ReverseMessageTool";
    public const string Description = "Echoes back message in reverse.";

    public static class Param {
      public const string Name = "Message";
      public const string Description = "The Message to reverse";
    }
  }

  public static class MultiplyNumbersTool {
    public const string Name = "MultiplyNumbersTool";
    public const string Description = "A tool that shows paramater usage by asking for two numbers and multiplying them together";

    public static class Param1 {
      public const string Name = "FirstNumber";
      public const string Description = "The first number to multiply";
    }

    public static class Param2 {
      public const string Name = "SecondNumber";
      public const string Description = "The second number to multiply";
    }
  }

  public static class DataTypes {
    public const string Number = "number";
    public const string String = "string";
  }
}

The above file contains tool names, descriptions and data types. There are three MCP tools that we will be creating:

Name Purpose Parameters
HelloWorldTool Simply displays 'Hello World'  
ReverseMessageTool Reverses text string
MultiplyNumbersTool Multiplies two numbers number,number

To keep things clean, we will create a separate class for each tool. 

HelloWorldMcpTool.cs

public class HelloWorldMcpTool {
  [Function("HelloWorldMcpTool")]
  public IActionResult Run(
    [McpToolTrigger(ToolDefinitions.HelloWorldTool.Name, ToolDefinitions.HelloWorldTool.Description)]
    ToolInvocationContext context
  ) {
      return new OkObjectResult($"Hi. I am {ToolDefinitions.HelloWorldTool.Name} and my message is 'HELLO WORLD!'");
  }
}

ReverseMessageMcpTool.cs

public class ReverseMessageMcpTool {
  [Function("ReverseMessageMcpTool")]
  public IActionResult Run(
    [McpToolTrigger(ToolDefinitions.ReverseMessageTool.Name, ToolDefinitions.ReverseMessageTool.Description)]
    ToolInvocationContext context,
    [McpToolProperty(ToolDefinitions.ReverseMessageTool.Param.Name, ToolDefinitions.DataTypes.String, ToolDefinitions.ReverseMessageTool.Param.Description)]
    string message
  ) {
    string reversedMessage = new string(message.ToCharArray().Reverse().ToArray());
    return new OkObjectResult($"Hi. I'm {ToolDefinitions.ReverseMessageTool.Name}!. The reversed message is: {reversedMessage}");
  }
}

MultiplyNumbersMcpTool.cs

public class MultiplyNumbersMcpTool {
  [Function("MultiplyNumbersMcpTool")]
  public IActionResult Run(
    [McpToolTrigger(ToolDefinitions.MultiplyNumbersTool.Name, ToolDefinitions.MultiplyNumbersTool.Description)]
    ToolInvocationContext context,
    [McpToolProperty(ToolDefinitions.MultiplyNumbersTool.Param1.Name, ToolDefinitions.DataTypes.Number, ToolDefinitions.MultiplyNumbersTool.Param1.Description)]
    int firstNumber,
    [McpToolProperty(ToolDefinitions.MultiplyNumbersTool.Param2.Name, ToolDefinitions.DataTypes.Number, ToolDefinitions.MultiplyNumbersTool.Param2.Description)]
    int secondNumber) {
    return new OkObjectResult($"Hi. I am {ToolDefinitions.MultiplyNumbersTool.Name}!. The result of {firstNumber} multiplied by {secondNumber} is: {firstNumber * secondNumber}");
  }
}

At this stage, we have created all the business logic for our three tools. What remains is configuring these tools in our Program.cs file. Add this code to Program.cs before: builder.Build().Run();

builder.EnableMcpToolMetadata();

builder.ConfigureMcpTool(ToolDefinitions.HelloWorldTool.Name);

builder.ConfigureMcpTool(ToolDefinitions.ReverseMessageTool.Name)
  .WithProperty(ToolDefinitions.ReverseMessageTool.Param.Name, ToolDefinitions.DataTypes.String, ToolDefinitions.ReverseMessageTool.Param.Description);

builder.ConfigureMcpTool(ToolDefinitions.MultiplyNumbersTool.Name)
  .WithProperty(ToolDefinitions.MultiplyNumbersTool.Param1.Name, ToolDefinitions.DataTypes.Number, ToolDefinitions.MultiplyNumbersTool.Param1.Description)
  .WithProperty(ToolDefinitions.MultiplyNumbersTool.Param2.Name, ToolDefinitions.DataTypes.Number, ToolDefinitions.MultiplyNumbersTool.Param2.Description);

In local.settings.json, make the following changes:

  1. Set the value of AzureWebJobsStorage to "UseDevelopmentStorage=true" for local development with the Azure emulator.
  2. Inside the "Values" section, add: "AzureWebJobsSecretStorageType": "Files". This is to use local file system for secrets instead of blob storage.

Contents of local.settings.json will look like this:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
    "AzureWebJobsSecretStorageType": "Files"
  }
}

Testing our MCP server locally

Start your Azure Functions app with:

func start

Note the MCP server SSE endpoint which will be later used to configure the server in VS Code.

Open the VS Code Command Palatte.

Choose 'MCP: Add Server...".

Next, choose "HTTP (HTTP or Server-Sent Events)".

Paste the MCP server SSE endpoint. In my case it is http://localhost:7071/runtime/webhooks/mcp/sse.

Next, you will be asked to give your MCP Server a name. You can accept the defaut value.

Select "Workspace Settings" so that the configuration is saved in your project in the .vscode/mcp.json file.

The .vscode/mcp.json file is open in the editor.

{
  "servers": {
    "my-mcp-server-c5a639d4": {
      "url": "http://localhost:7071/runtime/webhooks/mcp/sse"
    }
  }
}

Click on Start to start the MCP server.

Open the Open the GitHub Copilot Chat panel in the top right of VS Code.

In the chat panel, choose Agent, any Claude model, then click on the tools icon.

Our three tools appear. This means that we can now use them.

Back in the chat window, try this prompt:

Call the HelloWorldTool.

The MCP HelloWorldTool is detected. Click on the Continue button.

This is the respose I received:

I also tried this prompt to test the reverse merssage tool.

Reverse the message: MCP is very cool

And, received this response:

Again, I tried this prompt to test the multiplication tool tool.

Multiply 100 and 89.

And, received this result:

Our Azure Functions MCP Server works well locally. The next challenge is to make it work remotely by deploying it to Azure.

Deploy to Azure

We will use the az Azure CLI Tool. Folow these steps.

# Login to Azure

az login

# Create a resource group named: rg-mcp-func-server

az group create --name rg-mcp-func-server --location eastus

# Create a storage account named mcpstoreacc in resource-group rg-mcp-func-server in the eastus data center

az storage account create --name mcpstoreacc --resource-group rg-mcp-func-server --location eastus --sku Standard_LRS

# Create a function app named mcp-func-app

az functionapp create --resource-group rg-mcp-func-server --consumption-plan-location eastus --runtime dotnet-isolated --functions-version 4 --name mcp-func-app --storage-account mcpstoreacc

# Deploy (or redeploy) function app named mcp-func-app

func azure functionapp publish mcp-func-app

Upon successful deployment, the endpoint will be displayed in your terminal window:

Login into your Azure account and search for the resource group to find the deployed Azure Function.

Click on the functions app. In the example above, the functions app is named mcp-func-app. Copy the Default Domain and add to it https://. In .vscode/mcp.json replace http://localhost:7071 with the 'Default Domain' from Azure. Leave /runtime/webhooks/mcp/sse in the URL. Your .vscode/mcp.json will be similar to this:

{
    "servers": {
        "my-mcp-server-c5a639d4": {
            //"url": "http://localhost:7071/runtime/webhooks/mcp/sse"
            "url": "https://mcp-func-app.azurewebsites.net/runtime/webhooks/mcp/sse"
        }
    }
}

We will need to get an mcp_extension key from Azure. You will find that by clicking on "App keys" on the left navigarion in Azure and copying the mcp_extension key.

In .vscode/mcp.json, add this code above “servers”:

"inputs": [
  {
    "type": "promptString",
    "id": "functions-mcp-extension-system-key",
    "description": "Azure Functions MCP Extension System Key",
    "password": true
  }
],

.vscode/mcp.json looks like this:

The above added JSON will prompt the user to enter the mcp-extension key.

Again, in .vscode/mcp.json, add this “headers” block under “url”:

"headers": {
  "x-functions-key": "${input:functions-mcp-extension-system-key}"
}

.vscode/mcp.json now looks like this:

When you start the server, you will be prompted for the MCP Extension System Key. Enter the mcp_extension key you obtained from Azure. Once the server is running again, you can start prompting it as before. The big difference this time is that it is running remotely, instead of on your local computer.

I entered this prompt:

Multiply 11 and 22 and provide the answer.

And received this result:

Conclusion

We have learned how to create MCP servers using Microsoft's Azure Functions technology with C#. We went through the development, testing, and deployment process. In general, this is a much more compelling solution for large scale implementations of MCP Servers. You can take these basic concepts and build bigger and better MCP Servers.

Saturday, December 14, 2024

.NET Aspire and Semantic Kernel AI

 Let's learn how to use the .NET Aspire Azure OpenAI client. We will familiarize ourselves with the Aspire.Azure.AI.OpenAI library, which is used to register an OpenAIClient in the dependency injection (DI) container for consuming Azure OpenAI or OpenAI functionality. In addition, it enables corresponding logging and telemetry.

Companion Video: https://youtu.be/UuLnCRdYvEI
Final Solution Code: https://github.com/medhatelmasry/AspireAI_Final

Pre-requisites:

  • .NET 9.0
  • Visual Studio Code
  • .NET Aspire Workload
  • "C# Dev Kit" extension for VS Code

Getting Started

We will start by cloning a simple C# solution that contains two projects that use Semantic Kernel - namely a console project (ConsoleAI) and a razor-pages project (RazorPagesAI). Clone the project in a working directory on your computer by executing these commands in a terminal window:

git clone https://github.com/medhatelmasry/AspireAI.git
cd AspireAI

The cloned solution contains a console application (ConsoleAI) and a razor-pages application (RazorPagesAI). They both do pretty much do the same thing. The objective of today’s exercise is to:

  • use .NET Aspire so that both projects get started from one place 
  • pass environment variables to the console and razor-pages web apps from the .AppHost project that belongs to .NET Aspire

Open the solution in VS Code and update the values in the following appsettings.json files with your access parameters for Azure OpenAI and/or OpenAI:

ConsoleAI/appsettings.json
RazorPagesAI/appsettings.json

The most important settings are the connection strings. They are identical in both projects:

"ConnectionStrings": {
  "azureOpenAi": "Endpoint=Azure-OpenAI-Endpoint-Here;Key=Azure-OpenAI-Key-Here;",
  "openAi": "Key=OpenAI-Key-Here"
}

After you update your access parameters, try each application separately to see what it does:

Here is my experience using the console application (ConsoleAI) with AzureOrOpenAI set to “OpenAI”:

cd ConsoleAI
dotnet run


I then changed the AzureOrOpenAI setting to “Azure” and ran the console application (ConsoleAI) again:

Next, try the razor pages web application (RazorPagesAI) with AzureOrOpenAI set to “OpenAI”:

cd ../RazorPagesAI
dotnet watch


In the RazorPagesAI web app’s appsettings.json file, I changed AzureOrOpenAI to “Azure”, resulting in a similar experience.


In the root folder, add .NET Aspire to the solution:

cd ..
dotnet new aspire --force

Add the previous projects to the newly created .sln file with:

dotnet sln add ./AiLibrary/AiLibrary.csproj
dotnet sln add ./RazorPagesAI/RazorPagesAI.csproj
dotnet sln add ./ConsoleAI/ConsoleAI.csproj

Add the following .NET Aspire agent packages to the client ConsoleAI and RazorPagesAI projects with:

dotnet add ./ConsoleAI/ConsoleAI.csproj package Aspire.Azure.AI.OpenAI --prerelease
dotnet add ./RazorPagesAI/RazorPagesAI.csproj package Aspire.Azure.AI.OpenAI --prerelease

To add Azure hosting support to your IDistributedApplicationBuilder, install the 📦 Aspire.Hosting.Azure.CognitiveServices NuGet package in the .AppHost project:

dotnet add ./AspireAI.AppHost/AspireAI.AppHost.csproj package Aspire.Hosting.Azure.CognitiveServices

In VS Code, add the following references:

  1. Add a reference from the .AppHost project into ConsoleAI project.
  2. Add a reference from the .AppHost project into RazorPagesAI project.
  3. Add a reference from the ConsoleAI project into .ServiceDefaults project.
  4. Add a reference from the RazorPagesAI project into .ServiceDefaults project.

Copy the AI and ConnectionStrings blocks from either the console (ConsoleAI) or web app (RazorPagesAI)  appsettings.json file into the appsettings.json file of the .AppHost project. The appsettings.json file in the .AppHost project will look similar to this:

"AI": {
  "AzureOrOpenAI": "OpenAI",
  "OpenAiChatModel": "gpt-3.5-turbo",
  "AzureChatDeploymentName": "gpt-35-turbo"
},
"ConnectionStrings": {
  "azureOpenAi": "Endpoint=Azure-OpenAI-Endpoint-Here;Key=Azure-OpenAI-Key-Here;",
  "openAi": "Key=OpenAI-Key-Here"
}

Add the following code to the Program.cs file in the .AppHost project just before builder.Build().Run()

IResourceBuilder<IResourceWithConnectionString> openai;
var AzureOrOpenAI = builder.Configuration["AI:AzureOrOpenAI"] ?? "Azure"; ;
var chatDeploymentName = builder.Configuration["AI:AzureChatDeploymentName"];
var openAiChatModel = builder.Configuration["AI:OpenAiChatModel"];
 
// Register an Azure OpenAI resource. 
// The AddAzureAIOpenAI method reads connection information
// from the app host's configuration
if (AzureOrOpenAI.ToLower() == "azure") {
    openai = builder.ExecutionContext.IsPublishMode
        ? builder.AddAzureOpenAI("azureOpenAi")
        : builder.AddConnectionString("azureOpenAi");
} else {
    openai = builder.ExecutionContext.IsPublishMode
        ? builder.AddAzureOpenAI("openAi")
        : builder.AddConnectionString("openAi");
}
 
// Register the RazorPagesAI project and pass to it environment variables.
//  WithReference method passes connection info to client project
builder.AddProject<Projects.RazorPagesAI>("razor")
    .WithReference(openai)
    .WithEnvironment("AI__AzureChatDeploymentName", chatDeploymentName)
    .WithEnvironment("AI__AzureOrOpenAI", AzureOrOpenAI)
    .WithEnvironment("AI_OpenAiChatModel", openAiChatModel);
 
 // register the ConsoleAI project and pass to it environment variables
builder.AddProject<Projects.ConsoleAI>("console")
    .WithReference(openai)
    .WithEnvironment("AI__AzureChatDeploymentName", chatDeploymentName)
    .WithEnvironment("AI__AzureOrOpenAI", AzureOrOpenAI)
    .WithEnvironment("AI_OpenAiChatModel", openAiChatModel);

We need to add .NET Aspire agents in both our console and web apps. Let us start with the web app. Add this code to the Program.cs file in the RazorPagesAI project right before “var app = builder.Build()”: 

builder.AddServiceDefaults();

In the same Program.cs of the web app (RazorPagesAI), comment out the if (azureOrOpenAi.ToLower() == "openai") { …. } else { ….. } block and replace it with this code:

if (azureOrOpenAi.ToLower() == "openai") {
    builder.AddOpenAIClient("openAi");
    builder.Services.AddKernel()
        .AddOpenAIChatCompletion(openAiChatModel);
} else {
    builder.AddAzureOpenAIClient("azureOpenAi");
    builder.Services.AddKernel()
        .AddAzureOpenAIChatCompletion(azureChatDeploymentName);
}

In the above code, we call the extension method to register an OpenAIClient for use via the dependency injection container. The method takes a connection name parameter. Also, register Semantic Kernel with the DI. 

Also, in the Program.cs file in the ConsoleAI project, add this code right below the using statements:

var hostBuilder = Host.CreateApplicationBuilder();
hostBuilder.AddServiceDefaults();

In the same Program.cs of the console app (ConsoleAI), comment out the if (azureOrOpenAi.ToLower() == "azure") { …. } else { ….. } block and replace it with this code:

if (azureOrOpenAI.ToLower() == "azure") {
    var azureChatDeploymentName = config["AI:AzureChatDeploymentName"] ?? "gpt-35-turbo";
    hostBuilder.AddAzureOpenAIClient("azureOpenAi");
    hostBuilder.Services.AddKernel()
        .AddAzureOpenAIChatCompletion(azureChatDeploymentName);
} else {
    var openAiChatModel = config["AI:OpenAiChatModel"] ?? "gpt-3.5-turbo";
    hostBuilder.AddOpenAIClient("openAi");
    hostBuilder.Services.AddKernel()
        .AddOpenAIChatCompletion(openAiChatModel);
}
var app = hostBuilder.Build();

Replace “var kernel = builder.Build();” with this code:

var kernel = app.Services.GetRequiredService<Kernel>();
app.Start();

You can now test that the .NET Aspire orchestration of both the Console and Web apps. Stop all applications, then, in a terminal window,  go to the .AppHost project and run the following command:

dotnet watch

You will see the .NET Aspire dashboard:


Click on Views under the Logs column. You will see this output indicating that the console application ran successfully:


Click on the link for the web app under the Endpoints column. It opens the razor pages web app in another tab in your browser. Test it out and verify that it works as expected.

Stop the .AppHost application, then comment out the AI and ConneectionStrings blocks in the appsettings.json files in both the console and web apps. If you run the .AppHost project again, you will discover that it works equally well because the environment variables are being passed from the .AppHost project into the console and web apps respectively.

One last refinement we can do to the console application is do away with the ConfigurationBuilder because we can get a configuration object from the ApplicationBuilder. Therefore, comment out the following code in the console application:

var config = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
    .Build();

Replace the above code with the following:

var config = hostBuilder.Configuration;

You can delete the following package from the ConsoleAI.csproj file:

<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="9.0.0" />

Everything works just as it did before.


Thursday, February 29, 2024

OpenAI Function Calling with Semantic Kernel, C#, & Entity Framework

In this article, we will create a Semantic Kernel plugin that contains four functions that interact with live SQLite data. Entity Framework will be used to access the SQLite database. The end result is to use the powers of the OpenAI natural language models to ask questions and get answers about our custom data.

Source code: https://github.com/medhatelmasry/EfFuncCallSK

Companion Video: https://youtu.be/4sKRwflEyHk

Getting Started

Let’s start by creating an ASP.NET Razor pages web application. Select a suitable working folder on your computer, then enter the following terminal window commands:

dotnet new razor --auth individual -o EfFuncCallSK
cd EfFuncCallSK

Te above creates a Razor Pages app with support for Entity Framework and SQLite.

Add these packages:

dotnet add package CsvHelper
dotnet add package Microsoft.SemanticKernel 
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SQLite.Design

The CsvHelper package will help us load a list of products from a CSV file named students.csv and hydrate a list of Student objects. The second package is needed to work with Semantic Kernel. The rest of the packages support Entity Framework and SQLite.

Let’s Code

appsettings.json

Add these to appsettings.json:

"AIService": "OpenAI", /* Azure or OpenAI */
"AzureOpenAiSettings": {
   "Endpoint": "https://YOUR_RESOURCE_NAME.openai.azure.com/",
   "Model": "gpt-35-turbo",
   "ApiKey": "fake-key-fake-key-fake-key-fake-key"
},
"OpenAiSettings": {
  "ModelType": "gpt-3.5-turbo",
  "ApiKey": "fake-key-fake-key-fake-key-fake-key"
}

The first setting allows you to choose between using OpenAI or Azure OpenAI.

Of course, you need to adjust the endpoint setting with the appropriate value that pertains to the OpenAI and Azure OpenAI services. Also, enter the correct value for the ApiKey.

NOTE: You can use OpenAI or Azure OpenAI, or both.

Data

Create a folder named Models. Inside the Models folder, add the following Student class: 

public class Student {
   public int StudentId { get; set; }
   public string? FirstName { get; set; }
   public string? LastName { get; set; }
   public string? School { get; set; }
 
   public override string ToString() {
      return $"Student ID: {StudentId}, First Name: {FirstName}, Last Name: {LastName}, School: {School}";
   }
}

Developers like having sample data when building data driven applications. Therefore, we will create sample data to ensure that our application behaves as expected. Copy the following data and save it in a text file wwwroot/students.csv:

StudentId,FirstName,LastName,School
1,Tom,Max,Nursing
2,Ann,Fay,Mining
3,Joe,Sun,Nursing
4,Sue,Fox,Computing
5,Ben,Ray,Mining
6,Zoe,Cox,Business
7,Sam,Ray,Mining
8,Dan,Ash,Medicine
9,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Nursing
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Nursing
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Cat,Yao,Medicine
36,Lou,Zhu,Mining
37,Tom,Dag,Business
38,Stu,Day,Business
39,Tom,Gad,Mining
40,Bob,Bee,Business
41,Jim,Ots,Business
42,Tom,Mag,Business
43,Hal,Doe,Mining
44,Roy,Kim,Mining
45,Vis,Cox,Nursing
46,Kay,Aga,Nursing
47,Reo,Hui,Nursing
48,Bob,Roe,Mining
49,Jay,Eff,Computing
50,Eva,Chu,Business
51,Lex,Rae,Nursing
52,Lin,Dex,Mining
53,Tom,Dag,Business
54,Ben,Shy,Computing
55,Rob,Bos,Nursing
56,Ali,Mac,Business
57,Edi,Gee,Computing
58,Eva,Cao,Mining
59,Jun,Lam,Computing
60,Eli,Tao,Computing
61,Ana,Bay,Computing
62,Gil,Tal,Mining
63,Wes,Dey,Nursing
64,Nea,Tan,Computing
65,Ava,Day,Nursing
66,Rie,Ray,Business
67,Ken,Sim,Nursing

Add the following code inside the ApplicationDbContext class located inside the Data folder:

public DbSet<Student> Students => Set<Student>();    
 
protected override void OnModelCreating(ModelBuilder modelBuilder) {
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Student>().HasData(LoadStudents());
}  
 
// Load students from a csv file named students.csv in the wwwroot folder
public static List<Student> LoadStudents() {
    var students = new List<Student>();
    using (var reader = new StreamReader(Path.Combine("wwwroot", "students.csv"))) {
        using var csv = new CsvReader(reader, CultureInfo.InvariantCulture);
        students = csv.GetRecords<Student>().ToList();
    }
    return students;
}

Let us add a migration and subsequently update the database. Execute the following CLI commands in a terminal window.

dotnet ef migrations add Students -o Data/Migrations
dotnet ef database update

At this point the database and tables are created in a SQLite database named app.db.

Helper Methods

We need a couple of static helper methods to assist us along the way. In the Models folder, add a class named Utils and add to it the following class definition:

public class Utils {
  public static string GetConfigValue(string config) {
    IConfigurationBuilder builder = new ConfigurationBuilder();
    if (System.IO.File.Exists("appsettings.json"))
      builder.AddJsonFile("appsettings.json", false, true);
    if (System.IO.File.Exists("appsettings.Development.json"))
      builder.AddJsonFile("appsettings.Development.json", false, true);
    IConfigurationRoot root = builder.Build();
    return root[config]!;
  }
 
  public static ApplicationDbContext GetDbContext() {
    var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
    var connStr = Utils.GetConfigValue("ConnectionStrings:DefaultConnection");
    optionsBuilder.UseSqlite(connStr);
    ApplicationDbContext db = new ApplicationDbContext(optionsBuilder.Options);
    return db;
  }
}

Method GetConfigValue() will read values in appsettings.json from any static method. The second GetDbContext() method gets an instance of the ApplicationDbContext class, also from any static method.

Plugins

Create a folder named Plugins and add to it the following class file named StudentPlugin.cs with this code:

public class StudentPlugin {
  [KernelFunction, Description("Get student details by first name and last name")]
  public static string? GetStudentDetails(
  [Description("student first name, e.g. Kim")]
  string firstName,
  [Description("student last name, e.g. Ash")]
  string lastName
  ) {
    var db = Utils.GetDbContext();
    var studentDetails = db.Students
      .Where(s => s.FirstName == firstName && s.LastName == lastName).FirstOrDefault();
    if (studentDetails == null)
      return null;
    return studentDetails.ToString();
  }

  [KernelFunction, Description("Get students in a school given the school name")]
  public static string? GetStudentsBySchool(
    [Description("The school name, e.g. Nursing")]
    string school
  ) {
    var studentsBySchool = Utils.GetDbContext().Students
      .Where(s => s.School == school).ToList();
    if (studentsBySchool.Count == 0)
      return null;
    return JsonSerializer.Serialize(studentsBySchool);
  }


  [KernelFunction, Description("Get the school with most or least students. Takes boolean argument with true for most and false for least.")]
  static public string? GetSchoolWithMostOrLeastStudents(
    [Description("isMost is a boolean argument with true for most and false for least. Default is true.")]
    bool isMost = true
  ) {
    var students = Utils.GetDbContext().Students.ToList();
    IGrouping<string, Student>? schoolGroup = null;
    if (isMost)
      schoolGroup = students.GroupBy(s => s.School)
          .OrderByDescending(g => g.Count()).FirstOrDefault()!;
    else
        schoolGroup = students.GroupBy(s => s.School)
            .OrderBy(g => g.Count()).FirstOrDefault()!;
    if (schoolGroup != null)
      return $"{schoolGroup.Key} has {schoolGroup.Count()} students";
    else
      return null;
  }

  [KernelFunction, Description("Get students grouped by school.")]
  static public string? GetStudentsInSchool() {
    var students = Utils.GetDbContext().Students.ToList().GroupBy(s => s.School)
      .OrderByDescending(g => g.Count());
    if (students == null)
      return null;
    else
      return JsonSerializer.Serialize(students);
  }
}

 In the above code, there are four methods with these purposes:

GetStudentDetails()Gets student details given first and last names
GetStudentsBySchool()Gets students in a school given the name of the school
GetSchoolWithMostOrLeastStudents()Takes a Boolean value isMost – true returns school with most students and false returns school with least students.
GetStudentsInSchool()Takes no arguments and returns a count of students by school.

The User Interface

We will re-purpose the Index.cshtml and Index.cshtml.cs files so the user can enter a prompt in natural language and receive a response that comes from the OpenAI model working with our semantic kernel plugin. 

Index.chtml

Replace the content of Pages/Index.cshtml with:

@page
@model IndexModel
@{
    ViewData["Title"] = Model.Service + " Function Calling with Semantic Kernel";
}
<div class="text-center">
    <h3 class="display-6">@ViewData["Title"]</h3>
    <form method="post">
        <input type="text" name="prompt" size="80" required />
        <input type="submit" value="Submit" />
    </form>
    <div style="text-align: left">
        <h5>Example prompts:</h5>
        <p>Which school does Mat Tan go to?</p>
        <p>Which school has the most students?</p>
        <p>Which school has the least students?</p>
        <p>Get the count of students in each school.</p>
        <p>How many students are there in the school of Mining?</p>
        <p>What is the ID of Jan Fry and which school does she go to?</p>
        <p>Which students belong to the school of Business? Respond only in JSON format.</p>
        <p>Which students in the school of Nursing have their first or last name start with the letter 'J'?</p>
    </div>
    @if (Model.Reply != null)
    {
        <p class="alert alert-success">@Model.Reply</p>
    }
</div>

The above markup displays an HTML form that accepts a prompt from a user. The prompt is then submitted to the server and the response is displayed in a paragraph (<p> tag) with a green background (Bootstrap class alert-success).

Meantime, at the bottom of the page there are some suggested prompts – namely:

Which school does Mat Tan go to?
Which school has the most students?
Which school has the least students?
Get the count of students in each school.
How many students are there in the school of Mining?
What is the ID of Jan Fry and which school does she go to?
Which students belong to the school of Business? Respond only in JSON format.
Which students in the school of Nursing have their first or last name start with the letter 'J'?

Index.chtml.cs

Replace the IndexModel class definition in Pages/Index.cshtml.cs with:

public class IndexModel : PageModel {
  private readonly ILogger<IndexModel> _logger;
  private readonly IConfiguration _config;
 
  [BindProperty]
  public string? Reply { get; set; }
 
  [BindProperty]
  public string? Service { get; set; }
 
  public IndexModel(ILogger<IndexModel> logger, IConfiguration config) {
    _logger = logger;
    _config = config;
    Service = _config["AIService"]!;
  }
  public void OnGet() { }
  // action method that receives prompt from the form
  public async Task<IActionResult> OnPostAsync(string prompt) {
    // call the Azure Function
    var response = await CallFunction(prompt);
    Reply = response;
    return Page();
  }
 
  private async Task<string> CallFunction(string question) {
    string azEndpoint = _config["AzureOpenAiSettings:Endpoint"]!;
    string azApiKey = _config["AzureOpenAiSettings:ApiKey"]!;
    string azModel = _config["AzureOpenAiSettings:Model"]!;
    string oaiModelType = _config["OpenAiSettings:ModelType"]!;
    string oaiApiKey = _config["OpenAiSettings:ApiKey"]!;
    string oaiModel = _config["OpenAiSettings:Model"]!;
    string oaiOrganization = _config["OpenAiSettings:Organization"]!;
    var builder = Kernel.CreateBuilder();
    if (Service!.ToLower() == "openai")
      builder.Services.AddOpenAIChatCompletion(oaiModelType, oaiApiKey);
    else
      builder.Services.AddAzureOpenAIChatCompletion(azModel, azEndpoint, azApiKey);
    builder.Services.AddLogging(c => c.AddDebug().SetMinimumLevel(LogLevel.Trace));
    builder.Plugins.AddFromType<StudentPlugin>();
    var kernel = builder.Build();
    // Create chat history
    ChatHistory history = [];
    // Get chat completion service
    var chatCompletionService = kernel.GetRequiredService<IChatCompletionService>();
    // Get user input
    history.AddUserMessage(question);
    // Enable auto function calling
    OpenAIPromptExecutionSettings openAIPromptExecutionSettings = new() {
      ToolCallBehavior = ToolCallBehavior.AutoInvokeKernelFunctions
    };
    // Get the response from the AI
    var result = chatCompletionService.GetStreamingChatMessageContentsAsync(
      history,
      executionSettings: openAIPromptExecutionSettings,
      kernel: kernel);
    string fullMessage = "";
    await foreach (var content in result) {
      fullMessage += content.Content;
    }
    // Add the message to the chat history
    history.AddAssistantMessage(fullMessage);
    return fullMessage;
  }
}

In the above code, the prompt entered by the user is posted to the OnPostAsync() method. The prompt is then passed to the CallFunction() method, which returns the final response from Azure OpenAI.

The CallFunction() method reads the OpenAI or Azure OpenAI settings from appsettings.json, depending on the AIService key.

A builder object is created from Semantic Kernel. If we are using OpenAI, then the AddOpenAIChatCompletion service is added. Otherwise, the AddAzureOpenAIChatCompletion service is added.

The StudentPlugin is then added to the builder object Plugins collection.

The builder Build() method is then called returning a kernel object. From the kernel object we then get a chatCompletionService object by calling the GetRequiredService() method.

Thereafter:

  • Add the prompt to the history
  • Make a call to the chat message service and receive a response
  • Concatenate response into a single string
  • Return the concatenated message

Trying the application

In a terminal window, at the root of the Razor Pages web application, enter the following command:

dotnet watch

The following page will display in your default browser:


You can enter any of the suggested prompts to ensure we are getting the proper results. I entered the last prompt and got these results:




Conclusion

We have seen how Semantic Kernel and Function Calling can be used with data coming from a database. In this example we are using SQLite. However, an other database source can be used using the same technique.