Semantic Kernel: Creating Plugins to access the business data
In the modern of software application development, every single application wants an AI capability to be integrated for providing results to their end-users based on their questions/prompt. The challenge in such scenario is to integrate the AI capability to an existing application with seamless integration with the smooth data sharing. This is where we can make use of the Semantic Kernel.
What is Semantic Kernel?
In a very simple words, Semantic Kernel is Microsoft’s open-source SDK that is Designed to integrate large language models (LLMs) like GPT with traditional programming logic. It supports multiple languages like C#, Python, and Java, making it accessible for a range of developers. It provides hybrid AI orchestration that combines AI capabilities like summarizing, answering, and generating with conventional app code and workflows. It offers Plugin-friendly architecture to easily integrates external APIs and functions as "skills" that the AI can call. We can use Semantic Kernel for building applications Like:
- AI Agents that act as an agent for customer service or even a virtual training assistant.
- A Handler of various AI and Non-AI e.g. based on the received prompt implement a RAG solution on the available data to generate a summary and send a report.
- A copilot to perform domain tasks like brief summary generator on HR Policies
What is Semantic Kernel Plugin?
Plugins are a an important key component of Semantic Kernel. Technically, a plugin is a group of functions that can be exposed to an AI application and services. The functions within plugins can then be orchestrated by an AI application in such a way that they can further accomplish requests received from the users for generating details chat-based results. E.g. We have a CRM application that has the customers' data and all sales orders stored in database. You already have an API application that provides necessary customer information as well as order details by them. Now you want to use the AI capability with the CRM application so that requests from the user will be handled by the Semantic Kernel where it will invoke the existing app as a plugin function and then, the received data will be used for generating resultant prompt from the GPT response. This way without any considerable modifications in the existing application we can use AI capabilities using Semantic Kernel.
In this article, we will be implementing the plugin for the Symantic Kernel so that the existing logic will be able to use the AI Capabilities. Figure 1 shows the idea of the Semantic Kernel plugin Implementation with an existing application.
As shown in Figure 1, the implementation of the code for this article goes as follows:
- The prompt will be posted to the API Endpoint. This API endpoint will be using Semantic Kernel that uses the posted prompt.
- The prompt contains information values like city, country, etc. The Semantic Kernel will create a function from the prompt and using the Azure Open AI GPT deployment it will extract values for City, Country, etc.
- The extracted values will be used by the Semantic Kernel for the further use.
- The Semantic Kernel will use these extracted values and pass them by invoking the plugin function.
- The plugin function will call the existing the data access logic to fetch data based on the parameters received from plugin function.
- The existing logic will connect to the database and fetch data.
- The data will be sent back to the plugin function.
- Semantic kernel will accept the received data and use it for the further processing.
- The data is again sent back to Azure Open AI GPT deployment for reformatting to the output prompt. This output prompt will be sent back to the Semantic Kernel (Figure 1, has numbering 9 for Data input to GPT and resultant prompt from GPT.
- Reformatted data is sent back as a response to the client application.
This is how Semantic Kernal can be integrated with an existing application. Semantic Kernel can also be used to create plugin function to CRM applications to extract information.
The application implementation
You must have an Azure Subscription. Login to Azure portal and create Azure Open AI Service as explained in this article. Please copy the Key and Endpoint of the Azure Open AI, we need this to authenticate the client application with AI Service. You need to deploy the GPT model in the Azure Open AI Service. The same article explains the GPT model deployment.
The code of the article is ASP.NET Core .NET 9 project created using Visual Studio 2022. The project using Entity Framework Core to perform data access from the SQL Database. The Northwind database is created by downloading SQL Script from this link. Download scripts and run instnwnd.sql script to create Northwind database. I have created a new table OrderDetailsDynamic using the script shown in Listing 1:
SELECT OrderID, Customers.ContactName AS CustomerName,
Employees.FirstName + ' ' + Employees.LastName AS EmployeeName,
OrderDate, RequiredDate, ShippedDate, Shippers.CompanyName AS ShipperName,
Freight, ShipName, ShipAddress, ShipCity as City, ShipPostalCode, ShipCountry as Country
INTO OrderDetailsDynamic
FROM Orders, Customers, Employees, Shippers
WHERE Customers.CustomerID = Orders.CustomerID
AND Employees.EmployeeID = Orders.EmployeeID
AND Shippers.ShipperID = Orders.ShipVia;
Listing 1: The SQL Script to create OrderDetailsDynamic
Step 1: Create ASP.NET Core 9 API project named Core_DataAccessPlugIns. In this project add following NuGet packages.
- Microsoft.EntityFramework.SqlServer
- Microsoft.EntityFrameworkCore
- Microsoft.EntityFrameworkCore.Design
- Microsoft.EntityFrameworkCore.Relational
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.SemanticKernel Version 1.58
- Microsoft.SemanticKernel.Abstractions Version 1.58
- Microsoft.SemanticKernel.Connectors.AzureOpenAI Version 1.58
- Microsoft.SemanticKernel.Core Version 1.58
- System.Linq.Dynamic.Core
The Azure Open AI Connector of Semantic Kernel is used to access the Azure Open AI Services and hence we need not to install Azure.AI.OpenAI package separately.
Open the Command Prompt and navigate to the project folder to run the following command to generate models for data access using Entity Framework core.
dotnet ef dbcontext scaffold "Data Source=MyServer;Initial Catalog=Northwind;Integrated Security=SSPI;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer -o Models
Step 2: The project now has the Models folder will entity classes for tables in Northwind database. Copy the database connection string from the NorthwindContext.cs file from the Models folder so that we can add it in appsettings.json file. Modify the appsettings.json file to define database connection string and Azure Open AI Settings e.g. Azure Open AI Endpoint, Key, Deployment Name, and Model version no. Listing 2 show the appsettings.json file.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"NwConnection": "[DATABASE-CONNECTION-STRING]"
},
"AzureAISettings": {
"AIServiceEndpoint": "[AZURE-OPEN-AI-SERVICE-ENDPOINT]",
"AIServiceKey": "[AZURE-OPEN-AI-SERVICE-KEY]",
"AIServiceDeploymentName": "[DEPLOYMENT-NAME]",
"AIDeploymentVersion": "[VERSION-NO]"
}
}
Listing 2: The appsettings.json
Step 3: In the project add a new folder named RequestResponse. In this folder add the class file in which we will add code for PromptRequest and PromptResponse classes as shown in Listing 3. We will use these classes to post the prompt to API in HTTP POST request and received response prompt from API.
namespace Core_DataAccessPlugIns.RequestResponse
{
public class PromptRequest
{
public string? Prompt { get; set; }
}
public class PromptResponse
{
public string? Prompt { get; set; }
public string? Response { get; set; }
public string? ErrorMessage { get; set; }
}
}
Listing 3: The Prompt Request and Response classes
Step 4: In the project, add a new folder named PlugInServices. In this folder we will add the code for NwServices data access class. This class is constructor injected with the Context class that we have created using Entity Framework core. The NwServices class has the KernelFunction methods. These methods will be invoked by the Semantic Kernel. The GetCustomerAsync() method will return customers by city, country based on logical AND and OR conditions. The GetOrderDetailsAsync() method will use the dynamic LINQ to return order details in this case its sum of freight for order shipping based on any property and its value passed to this method. If no parameter is passed to this method, then details of all orders will be returned. Listing 4 shows code for the NwServices class.
using System.ComponentModel;
using Microsoft.EntityFrameworkCore;
using Core_DataAccessPlugIns.Models;
using Microsoft.SemanticKernel;
using System.Linq.Dynamic.Core;
namespace Core_DataAccessPlugIns.PluginServices
{
public class NwServices
{
private readonly NwContext _context;
public NwServices(NwContext context)
{
_context = context;
}
[KernelFunction("GetCustomerInfo")]
[Description("Get customer information based on city and country.")]
public async Task<List<Customer>> GetCustomersAsync(string? city, string? country)
{
List<Customer> customers = null;
if(city is null && country is null)
{
customers = await _context.Customers.ToListAsync();
}
else if(city is not null && country is null)
{
customers = await _context.Customers
.Where(c => c.City == city)
.ToListAsync();
}
else if(city is null && country is not null)
{
customers = await _context.Customers
.Where(c => c.Country == country)
.ToListAsync();
}
else
{
customers = await _context.Customers
.Where(c => c.City == city && c.Country == country)
.ToListAsync();
}
return customers;
}
[KernelFunction("OrderDetailsDynamic")]
[Description("Get order details with various operations like, sum, details by shipcity,shipcountry")]
public async Task<object> GetOrderDetailsAsync(
string? propertyName,
string? propertyValue = null,
string? operation=null)
{
object dynamicResult = null;
switch (operation)
{
case "sum":
// In System.Linq.Dynamic.Core, the keyword "it" is a placeholder for the current object in the collection—similar to how we'd use a lambda parameter like x in traditional LINQ:
// Dynamic Where clause
string filter = $"{propertyName} == @0";
var summary = _context.OrderDetailsDynamics
.Where(filter, propertyValue) // Filter dynamically
.GroupBy(propertyName, "it")
.Select("new (Key as GroupValue, Sum(Freight) as TotalFreight)")
.ToDynamicList();
dynamicResult = summary; // updated to assign to summary
break;
default:
dynamicResult = await _context.OrderDetailsDynamics.ToListAsync();
break;
}
return dynamicResult;
}
}
}
Listing 4: NwServices class
Step 5: In the project, add a new folder named ChatServices. In this folder we will add code for ChatInfoGenerator class. This is the most important part of the application. In the constructor this class create an instance of Semantic Kernel Builder and connects with Azure Open AI using the Kernel Builder. To connect to Azure Open AI Service, it uses settings from appsettings.json file that we have created Step 2. The constructor further registers the NwServices class as a plugin for the Semantic Kernel. Since the NwServices class has the NwContext class as its dependency, the constructor needs to extract the NwContext class from the ASP.NET Core dependency container. Once the NwServices class is added as a plugin, the constructor must define Prompt Execution Settings with parameters like Temperature, MaxTokens, TopP, ServiceId, etc. Please note that the value for the ServiceId is set as default, this value will be used as arguments while invoking the Kernel Functions those are defined in NwServices class. Listing 5 shows code for ChatInfoGenerator class.
using System.Text.Json;
using System.Text.RegularExpressions;
using Core_DataAccessPlugIns.Models;
using Core_DataAccessPlugIns.PluginServices;
using Microsoft.AspNetCore.Components.Forms;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.SemanticKernel;
using Microsoft.SemanticKernel.ChatCompletion;
using Microsoft.SemanticKernel.Connectors.AzureOpenAI;
using Microsoft.SemanticKernel.Connectors.OpenAI;
namespace Core_DataAccessPlugIns.ChatServices
{
public class ChatInfoGenerator
{
private IKernelBuilder _kernelBuilder;
private Kernel _kernel;
IChatCompletionService _chatCompletionService;
AzureOpenAIPromptExecutionSettings settings;
public ChatInfoGenerator(IConfiguration config, IServiceProvider serviceProvider)
{
_kernelBuilder = Kernel.CreateBuilder()
.AddAzureOpenAIChatCompletion(
config["AzureAISettings:AIServiceDeploymentName"],
config["AzureAISettings:AIServiceEndpoint"],
config["AzureAISettings:AIServiceKey"]
);
_kernel = _kernelBuilder.Build();
_chatCompletionService = _kernel.GetRequiredService<IChatCompletionService>();
// Resolve the dependency from the service provider
var nwContext = serviceProvider.GetRequiredService<NwContext>();
// Create an instance of your plugin with the dependency injected
var nwServices = new NwServices(nwContext);
// Register the plugin instance with the kernel
_kernel.Plugins.AddFromObject(nwServices, "NwServices");
settings = new AzureOpenAIPromptExecutionSettings
{
Temperature = 0.7f,
MaxTokens = 1000,
TopP = 0.9f,
FrequencyPenalty = 0.0f,
PresencePenalty = 0.0f,
FunctionChoiceBehavior = FunctionChoiceBehavior.Auto(autoInvoke: true),
ServiceId = "default"
};
}
public async Task<string> GetCustomersByCityAndCountry(string userMessage)
{
// read the prompt and parse it to get city and country
var dictionary = await ParsePromptToDictionaryValues(userMessage);
string city = dictionary.ContainsKey("CityName") ? dictionary["CityName"] : null;
string country = dictionary.ContainsKey("CountryName") ? dictionary["CountryName"] : null;
string responseContents = string.Empty;
try
{
// Get Customers
var args = new KernelArguments
{
["city"] = city,
["country"] = country,
ExecutionSettings = new Dictionary<string, PromptExecutionSettings>
{
["default"] = settings
}
};
var customersResult = await _kernel.Plugins.GetFunction("NwServices", "GetCustomerInfo").InvokeAsync(_kernel, args);
var customers = customersResult.GetValue<List<Customer>>();
var customerData = JsonSerializer.Serialize(customers, new JsonSerializerOptions
{
WriteIndented = true,
PropertyNamingPolicy = JsonNamingPolicy.CamelCase
});
string prompt = $@"
You are a helpful assistant that provides customer information.
User asked: {userMessage}
Here is the customer data retrieved from the system:
{customerData}
Now, summarize this information in a friendly and concise way in Tabular format. Please include columns and rows in the table. Please do not include any additional text outside the table. Please ensure the table is well formatted and easy to read. The column width must be appropriate for the data.
";
var result = await _kernel.InvokePromptAsync(prompt, args);
responseContents = result.ToString();
responseContents = Regex.Replace(responseContents, @"\s+", " ").Trim();
responseContents = Regex.Replace(responseContents, @"\|", " | ").Trim();
responseContents = Regex.Replace(responseContents, @"\n", "\n| ").Trim();
}
catch (Exception ex)
{
throw ex;
}
return responseContents;
}
public async Task<string> GetFreightDetailsAsync(
string userMessage)
{
// read the prompt and parse it to get propertyname, propertyvalue and operation
var dictionary = await ParsePromptToDictionaryValues(userMessage);
string propertyname = string.Empty;
string propertyvalue = string.Empty;
string operation = dictionary.ContainsKey("Operation") ? dictionary["Operation"] : null;
if (dictionary.ContainsKey("City") && !string.IsNullOrEmpty(dictionary["City"]))
{
propertyname = "City";
propertyvalue = dictionary["City"];
}
else if(dictionary.ContainsKey("Country") && !string.IsNullOrEmpty(dictionary["Country"]))
{
propertyname = "Country";
propertyvalue = dictionary["Country"];
}
else if(dictionary.ContainsKey("ShipName") && !string.IsNullOrEmpty(dictionary["ShipName"]))
{
propertyname = "ShipName";
propertyvalue = dictionary["ShipName"];
}
else if(dictionary.ContainsKey("EmployeeName") && !string.IsNullOrEmpty(dictionary["EmployeeName"]))
{
propertyname = "EmployeeName";
propertyvalue = dictionary["EmployeeName"];
}
else
{
throw new ArgumentException("Invalid property name in the prompt.");
}
//string userMessage = $"Get me the freight details for {propertyname}, {propertyvalue} with operation {operation}";
string responseContents = string.Empty;
try
{
// Get Order Details
var args = new KernelArguments
{
["propertyname"] = propertyname,
["propertyvalue"] = propertyvalue,
["operation"] = operation,
ExecutionSettings = new Dictionary<string, PromptExecutionSettings>
{
["default"] = settings
}
};
var orderDetailsResult = await _kernel.Plugins.GetFunction("NwServices", "OrderDetailsDynamic").InvokeAsync(_kernel, args);
var orderDetails = orderDetailsResult.GetValue<object>();
var orderData = JsonSerializer.Serialize(orderDetails, new JsonSerializerOptions
{
WriteIndented = true,
PropertyNamingPolicy = JsonNamingPolicy.CamelCase
});
string prompt = $@"
You are a helpful assistant that provides freight details.
User asked: {userMessage}
Here is the order data retrieved from the system:
{orderData}
Now, summarize this information in a friendly and concise way in Tabular format. Please include columns and rows in the table. Please do not include any additional text outside the table. Please ensure the table is well formatted and easy to read. The column width must be appropriate for the data.
";
var result = await _kernel.InvokePromptAsync(prompt, args);
responseContents = result.ToString();
responseContents = Regex.Replace(responseContents, @"\s+", " ").Trim();
responseContents = Regex.Replace(responseContents, @"\|", " | ").Trim();
responseContents = Regex.Replace(responseContents, @"\n", "\n| ").Trim();
}
catch (Exception ex)
{
throw ex;
}
return responseContents;
}
private async Task<Dictionary<string, string>> ParsePromptToDictionaryValues(string prompt)
{
var dictionary = new Dictionary<string, string>();
// Step 2: Define the prompt template
/*
Semantic Kernel expects a variable named "input" in the arguments. But if you pass a plain string, it automatically maps it to the default input variable. If you use KernelArguments, then you must ensure the variable name matches exactly.
Use {{$input}} in the prompt and pass a plain string.
*/
string systemprompt = @"
Extract the following information from the text:
- CustomerName
- City
- Country
- ShipName
- EmployeeName
- Operation
Here the operation is like sum, average, etc.
Return the result in JSON format.
Text: {{$input}}
JSON:
";
// Lets load the semantic Funciton
var function = _kernel.CreateFunctionFromPrompt(
promptTemplate: systemprompt,
executionSettings: new OpenAIPromptExecutionSettings
{
MaxTokens = 200,
Temperature = 0,
TopP = 1
});
// This name must match with {{$input}} in the prompt
var input = new KernelArguments
{
["input"] = prompt
};
// Invoke the function
var result = await _kernel.InvokeAsync(function, input);
// Step 6: Parse and display the result
var json = result.GetValue<string>();
dictionary = JsonSerializer.Deserialize<Dictionary<string, string>>(json);
return dictionary;
}
}
}
Listing 5: The ChatInfoGenerator class
The ChatInfoGenerator class has following methods:
- ParsePromptToDictionaryValues(string prompt)
- Since we need to parse the received prompt from the end-user into the properties and its values e.g. City, Country, etc, we need to use GPT model to parse and extract the information from the received prompt.
- The method defines a system prompt to inform the GPT that it should extract information like City, Country, etc. from the input prompt. We have defined the text as {{$input}}, make sure that you have to use this name so that it can match with the Kernel Arguments so that the received prompt form the end-user will be parsed, and information will be extracted from it.
- Further the Kernel Function is created using the system prompt along with the execution settings like MaxTokens, Temperature, etc.
- This kernel function and kernel arguments are passed to InvokeAsync() method of the kernel so that required values will be extracted from the input prompt and extracted properties like City, Country, etc. and its values are stored in Dictionary.
- GetCustomersByCityAndCountry(string userMessage)
- This method accepts the userMessage from end-user and passes this prompt to the ParsePromptToDictionaryValues() method to extract values for the City and Country.
- These values are then passed as a Kernel Arguments to the GetCustomerInfo kernel function, so the customer information based on City or Country or based on both can be received.
- The Symantic Kernel further uses the received data and pass it to the GPT using InvokeAsync() as Kernel arguments so that the GPT will reformate the data in output prompt with required details mentioned in locally defined prompt. The result prompt will be returned back to client; in this case the data will be returned in tabular form to client.
- GetFreightDetailsAsync(string userMessage)
- Like explained GetCustomersByCityAndCountry() method, this method also parses the userMessage and receive City, Country, etc. and then pass these values to OrderDetailsDynamic kernel function. This kernel function returns sum of freight by City or Country and return the result back to Semantic Kernel. Again, like GetCustomersByCityAndCountry() method, this result will be used by the Semantic Kernel to reformat data in output prompt and responded to the client.
.....
// Add services to the container.
builder.Services.AddDbContext<NwContext>(options => {
options.UseSqlServer(builder.Configuration.GetConnectionString("NwConnection"));
});
// Learn more about configuring OpenAPI at https://aka.ms/aspnet/openapi
builder.Services.AddOpenApi();
builder.Services.AddScoped<ChatInfoGenerator>();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.MapOpenApi();
}
app.UseHttpsRedirection();
app.MapPost("/info", async (ChatInfoGenerator info, PromptRequest request) =>
{
var resp = await info.GetCustomersByCityAndCountry(request.Prompt);
return Results.Ok(resp);
});
app.MapPost("/orderdetails", async (ChatInfoGenerator info, PromptRequest prompt) =>
{
var resp = await info.GetFreightDetailsAsync(prompt.Prompt);
return Results.Ok(resp);
});
.....
