Entity Framework Bot State for Bot Framework SDKv4 (to store in SQL database IStorage implementation)

AppDbContext.cs

namespace DAL
{
    public class AppDbContext : DbContext
    {
        private readonly string defaultConnectionString;

        #region tables

        public DbSet<EFBotState> BotStates { get; set; }

        #endregion
        
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<EFBotState>().HasIndex(p => p.Key).IsUnique();
            modelBuilder.Entity<EFBotState>().Property(p => p.Key).IsRequired().HasMaxLength(150);
            modelBuilder.Entity<EFBotState>().Property(p => p.Value).IsRequired();
        }        
        // ...
    }
}

BotState.cs

using System;

namespace DAL.Entities
{
	public class EFBotState
    {
        public int Id { get; set; }
        public string Key { get; set; }
        public string Value { get; set; }

        public DateTime CreatedTime { get; set; }
        public DateTime UpdatedTime { get; set; }
    }
}

EntityFrameworkStorage.cs

using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using DAL;
using System.Linq;
using Microsoft.Bot.Builder;
using DAL.Entities;

namespace App.Models
{

    public class EntityFrameworkStorage : IStorage
    {
        private static readonly JsonSerializer StateJsonSerializer = new JsonSerializer() { TypeNameHandling = TypeNameHandling.All };
        private static readonly JsonSerializerSettings jsonSerializerSettings = new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All };

        private readonly object _syncroot = new object();
        private int _eTag = 0;
        private readonly string connectionString;

        public EntityFrameworkStorage(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException("Connection string should not be empty");
            }

            this.connectionString = connectionString;
        }

        public Task DeleteAsync(string[] keys, CancellationToken cancellationToken)
        {
            lock (_syncroot)
            {
                using (AppDbContext context = new AppDbContext(connectionString))
                {
                    var elements = context.BotStates.Where(f => keys.Contains(f.Key));
                    context.RemoveRange(elements);
                    context.SaveChanges();
                }
            }

            return Task.CompletedTask;
        }

        public Task<IDictionary<string, object>> ReadAsync(string[] keys, CancellationToken cancellationToken)
        {
            var storeItems = new Dictionary<string, object>(keys.Length);
            lock (_syncroot)
            {
                using (AppDbContext context = new AppDbContext(connectionString))
                {
                    var elements = context.BotStates.Where(f => keys.Contains(f.Key)).ToArray();
                    foreach (var x in elements)
                    {
                        storeItems.Add(x.Key, JsonConvert.DeserializeObject(x.Value, jsonSerializerSettings));
                    }
                }
            }

            return Task.FromResult<IDictionary<string, object>>(storeItems);
        }

        public Task WriteAsync(IDictionary<string, object> changes, CancellationToken cancellationToken)
        {
            lock (_syncroot)
            {
                using (AppDbContext context = new AppDbContext(connectionString))
                {
                    
                    EFBotState[] dbElements = context.BotStates.Where(f => changes.Keys.Contains(f.Key)).ToArray();
                        
                    foreach (var change in changes)
                    {
                        var newValue = change.Value;
                        var oldStateETag = default(string);

                        EFBotState foundElement = dbElements.Where(r => r.Key == change.Key).FirstOrDefault();
                        if (foundElement != null)
                        {
                            JObject oldJObject = JObject.Parse(foundElement.Value);
                            if (oldJObject.TryGetValue("eTag", out JToken etag)) 
                            {
                                oldStateETag = etag.Value<string>();
                            }
                        }


                        JObject newState = JObject.FromObject(newValue, StateJsonSerializer);

                        // Set ETag if applicable
                        if (newValue is IStoreItem newStoreItem)
                        {
                            if (oldStateETag != null
                                    &&
                               newStoreItem.ETag != "*"
                                    &&
                               newStoreItem.ETag != oldStateETag)
                            {
                                throw new Exception($"Etag conflict.\r\n\r\nOriginal: {newStoreItem.ETag}\r\nCurrent: {oldStateETag}");
                            }

                            newState["eTag"] = (_eTag++).ToString();
                        }

                        string newStringValue = newState.ToString(Formatting.None);

                        if (foundElement != null)
                        {
                            foundElement.Value = newStringValue;
                            context.BotStates.Update(foundElement);
                        }
                        else
                        {
                            context.BotStates.Add(new EFBotState
                            {
                                Key = change.Key,
                                Value = newStringValue,
                            });
                        }

                        context.SaveChanges();
                    }
                }
            }

            return Task.CompletedTask;
        }
    }
}

Startup.cs

namespace App
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddBot<AppBot>(options =>
            {

                var secretKey = Configuration.GetSection("botFileSecret")?.Value;
                var botFilePath = Configuration.GetSection("botFilePath")?.Value;

                var botConfig = BotConfiguration.Load(botFilePath ?? @".\BotConfiguration.bot", secretKey);
                services.AddSingleton(sp => botConfig ?? throw new InvalidOperationException($"The .bot config file could not be loaded. ({botConfig})"));

                string environment = isProduction ? "production" : "development";
                var service = botConfig.Services.Where(s => s.Type == "endpoint" && s.Name == environment).FirstOrDefault();
                if (!(service is EndpointService endpointService)) {
                    throw new InvalidOperationException($"The .bot file does not contain an endpoint with name {environment}");
                }

                options.CredentialProvider = new SimpleCredentialProvider(endpointService.AppId, endpointService.AppPassword);

                ILogger logger = loggerFactory.CreateLogger<AppBot>();
                options.OnTurnError = async (context, exception) =>
                {
                    logger.LogError($"Exception caught : {exception}");
                    await context.SendActivityAsync("Something went wrong");
                };

                IStorage dataStore = new EntityFrameworkStorage(Configuration.GetConnectionString("SqlServerConfig"));
                var conversationState = new ConversationState(dataStore);
                options.State.Add(conversationState);

            });
        }
    }
}

Hello, everyone. I used this code to connect bot to SQL Azure, but with a little changes in Startup.cs file:

Nowadays, options.State.Add (...) is deprecated and doesn't work. Also, in bot framework sdk v4 is not necessary use .bot file for your connections (use appsettings.json instead).

In this case, I changed services.AddBot (...) to this:
`` ''
services.AddSingleton <IHttpContextAccessor, HttpContextAccessor> ();

         IStorage dataStore = new EntityFrameworkStorage (Configuration.GetConnectionString ("SqlServerConfig"));
         var conversationState2 = new ConversationState (dataStore);
         services.AddSingleton (conversationState2);

`` ''

And that's all you need to do. Use Ice2burn's code for a succesfully connection to SQL.