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 EfFuncCallSKcd EfFuncCallSK
Te above creates a Razor Pages app with support for Entity Framework and SQLite.
Add these packages:
dotnet add package CsvHelperdotnet add package Microsoft.SemanticKerneldotnet add package Microsoft.EntityFrameworkCore.Designdotnet add package Microsoft.EntityFrameworkCore.Toolsdotnet add package Microsoft.EntityFrameworkCoredotnet 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,School1,Tom,Max,Nursing2,Ann,Fay,Mining3,Joe,Sun,Nursing4,Sue,Fox,Computing5,Ben,Ray,Mining6,Zoe,Cox,Business7,Sam,Ray,Mining8,Dan,Ash,Medicine9,Pat,Lee,Computing10,Kim,Day,Nursing11,Tim,Rex,Computing12,Rob,Ram,Nursing13,Jan,Fry,Mining14,Jim,Tex,Nursing15,Ben,Kid,Business16,Mia,Chu,Medicine17,Ted,Tao,Computing18,Amy,Day,Nursing19,Ian,Roy,Nursing20,Liz,Kit,Nursing21,Mat,Tan,Medicine22,Deb,Roy,Medicine23,Ana,Ray,Mining24,Lyn,Poe,Computing25,Amy,Raj,Nursing26,Kim,Ash,Mining27,Bec,Kid,Nursing28,Eva,Fry,Computing29,Eli,Lap,Business30,Sam,Yim,Nursing31,Joe,Hui,Mining32,Liz,Jin,Nursing33,Ric,Kuo,Business34,Pam,Mak,Computing35,Cat,Yao,Medicine36,Lou,Zhu,Mining37,Tom,Dag,Business38,Stu,Day,Business39,Tom,Gad,Mining40,Bob,Bee,Business41,Jim,Ots,Business42,Tom,Mag,Business43,Hal,Doe,Mining44,Roy,Kim,Mining45,Vis,Cox,Nursing46,Kay,Aga,Nursing47,Reo,Hui,Nursing48,Bob,Roe,Mining49,Jay,Eff,Computing50,Eva,Chu,Business51,Lex,Rae,Nursing52,Lin,Dex,Mining53,Tom,Dag,Business54,Ben,Shy,Computing55,Rob,Bos,Nursing56,Ali,Mac,Business57,Edi,Gee,Computing58,Eva,Cao,Mining59,Jun,Lam,Computing60,Eli,Tao,Computing61,Ana,Bay,Computing62,Gil,Tal,Mining63,Wes,Dey,Nursing64,Nea,Tan,Computing65,Ava,Day,Nursing66,Rie,Ray,Business67,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 folderpublic 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/Migrationsdotnet 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()!;elseschoolGroup = students.GroupBy(s => s.School).OrderBy(g => g.Count()).FirstOrDefault()!;if (schoolGroup != null)return $"{schoolGroup.Key} has {schoolGroup.Count()} students";elsereturn 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;elsereturn 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 formpublic async Task<IActionResult> OnPostAsync(string prompt) {// call the Azure Functionvar 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);elsebuilder.Services.AddAzureOpenAIChatCompletion(azModel, azEndpoint, azApiKey);builder.Services.AddLogging(c => c.AddDebug().SetMinimumLevel(LogLevel.Trace));builder.Plugins.AddFromType<StudentPlugin>();var kernel = builder.Build();// Create chat historyChatHistory history = [];// Get chat completion servicevar chatCompletionService = kernel.GetRequiredService<IChatCompletionService>();// Get user inputhistory.AddUserMessage(question);// Enable auto function callingOpenAIPromptExecutionSettings openAIPromptExecutionSettings = new() {ToolCallBehavior = ToolCallBehavior.AutoInvokeKernelFunctions};// Get the response from the AIvar 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 historyhistory.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:
