Tuesday, 1 July 2025

Pluralizers in Entity Framework

In Entity Framework, you can implement a IPluralizationService to control how entities' names will be pluralized. In additional singularalized. The standard setup is an English pluralizer. In addition, a Spanish pluralizer is available. If you want to support another language for pluralization (and singularization), I have added a sample of this in the following Github repo of mine :

https://github.com/toreaurstadboss/BulkOperationsEntityFramework

Note that the Norwegian Pluralization service could also pluralize words in English and try to check if the word to pluralize is either English or Norwegian. It is about 500,000 English nouns and 100,000, so it might be hard to create a perfect pluralizer for both English or Norwegian. A list of about 40,000+ nouns are available here: https://gist.github.com/trag1c/f74b2ab3589bc4ce5706f934616f6195/ The Norwegian Pluralization service next could use that wordlist to check if the the word is English and use the standard built in English pluralizer service. I will give an updated version of the NorwegianPluralizationService at the end of the article.

NorwegianPluralizationService.cs



using System;
using System.Collections.Generic;
using System.Data.Entity.Infrastructure.Pluralization;
using System.Diagnostics;
using System.Linq;

namespace BulkOperationsEntityFramework.Lib.Services
{

    /// <summary>
    /// Sources for the pluralization rules for Norwegian language:
    /// https://toppnorsk.com/2018/11/18/flertall-hovedregler/
    /// </summary>
    public class NorwegianPluralizationService : IPluralizationService
    {

        public static List<string> PluralizedWords = new List<string>();

        public string Pluralize(string word)
        {
            if (PluralizedWords.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                return word; // Return the already pluralized word
            }

//#if DEBUG
//            Debugger.Break();
//            Debugger.Launch(); // Uncomment this line to break into the debugger when this method is called, for example when database migrations are made with EF Code First
//#endif

            word = NormalizeWord(word);

            string pluralizedWord;

            if (_specialCases.ContainsKey(word))
            {
                pluralizedWord = _specialCases[word];
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordsChangingVowelToÆ.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                if (word.Equals("Håndkle", StringComparison.OrdinalIgnoreCase))
                {
                    pluralizedWord = "Håndklær";
                    PluralizedWords.Add(pluralizedWord);
                    return pluralizedWord;
                }
                pluralizedWord = word.Replace("å", "æ").Replace("e", "æ") + "r";
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordsForUnits.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                pluralizedWord = word;
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordsForRelatives.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                switch (word.ToLower())
                {
                    case "far": pluralizedWord = "Fedre"; break;
                    case "mor": pluralizedWord = "Mødre"; break;
                    case "datter": pluralizedWord = "Døtre"; break;
                    case "søster": pluralizedWord = "Søstre"; break;
                    case "fetter": pluralizedWord = "Fettere"; break;
                    case "onkel": pluralizedWord = "Onkler"; break;
                    case "svigerbror": pluralizedWord = "Svigerbrødre"; break;
                    case "svigerfar": pluralizedWord = "Svigerfedre"; break;
                    case "svigersøster": pluralizedWord = "Svigersøstre"; break;
                    case "svigermor": pluralizedWord = "Svigermødre"; break;
                    case "bror": pluralizedWord = "Brødre"; break;
                    default: pluralizedWord = word; break;
                }
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordsNeutralGenderEndingWithEumOrIum.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                if (word.EndsWith("eum"))
                    pluralizedWord = word.Substring(0, word.Length - 3) + "eer";
                else if (word.EndsWith("ium"))
                    pluralizedWord = word.Substring(0, word.Length - 3) + "ier";
                else
                    pluralizedWord = word;
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordsNoPluralizationForNeutralGenderOneSyllable.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                pluralizedWord = word;
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordChangingVowelsInPluralFemale.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                pluralizedWord = NormalizeWord(word.ToLower().Replace("å", "e").Replace("a", "e") + "er");
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_wordsChangingVowelsInPluralMale.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                string rewrittenWord = NormalizeWord(word.Replace("o", "ø"));
                if (rewrittenWord.Equals("føt", StringComparison.OrdinalIgnoreCase))
                    pluralizedWord = rewrittenWord + "ter";
                else if (rewrittenWord.EndsWith("e"))
                    pluralizedWord = rewrittenWord + "r";
                else if (!rewrittenWord.EndsWith("er"))
                    pluralizedWord = rewrittenWord + "er";
                else
                    pluralizedWord = rewrittenWord;
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_nonEndingWordsInPlural.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                pluralizedWord = word;
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            // General rules
            if (word.EndsWith("er"))
                pluralizedWord = word.Substring(0, word.Length - 2) + "ere";
            else if (word.EndsWith("el"))
                pluralizedWord = word.Substring(0, word.Length - 2) + "ler";
            else if (word.EndsWith("e"))
                pluralizedWord = word + "r";
            else if (word.EndsWith("en"))
                pluralizedWord = word + "er";
            else
                pluralizedWord = word + "er";

            PluralizedWords.Add(pluralizedWord);
            return pluralizedWord;
        }

        public string Singularize(string word)
        {
            word = NormalizeWord(word);

            // Reverse special cases
            var specialSingular = _specialCases.FirstOrDefault(kvp => kvp.Value.Equals(word, StringComparison.OrdinalIgnoreCase));
            if (!specialSingular.Equals(default(KeyValuePair<string, string>)))
                return specialSingular.Key;

            // Words that are the same in singular and plural
            if (_nonEndingWordsInPlural.Contains(word, StringComparer.OrdinalIgnoreCase) ||
                _wordsNoPluralizationForNeutralGenderOneSyllable.Contains(word, StringComparer.OrdinalIgnoreCase) ||
                _wordsForUnits.Contains(word, StringComparer.OrdinalIgnoreCase))
                return word;

            // Irregulars and vowel changes (expand as needed)
            if (word.Equals("Bøker", StringComparison.OrdinalIgnoreCase)) return "Bok";
            if (word.Equals("Føtter", StringComparison.OrdinalIgnoreCase)) return "Fot";
            if (word.Equals("Brødre", StringComparison.OrdinalIgnoreCase)) return "Bror";
            if (word.Equals("Menn", StringComparison.OrdinalIgnoreCase)) return "Mann";
            if (word.Equals("Kvinner", StringComparison.OrdinalIgnoreCase)) return "Kvinne";
            if (word.Equals("Gutter", StringComparison.OrdinalIgnoreCase)) return "Gutt";
            if (word.Equals("Netter", StringComparison.OrdinalIgnoreCase)) return "Natt";
            if (word.Equals("Tær", StringComparison.OrdinalIgnoreCase)) return "Tå";
            if (word.Equals("Tenner", StringComparison.OrdinalIgnoreCase)) return "Tann";
            if (word.Equals("Trær", StringComparison.OrdinalIgnoreCase)) return "Tre";
            if (word.Equals("Knær", StringComparison.OrdinalIgnoreCase)) return "Kne";
            if (word.Equals("Bønder", StringComparison.OrdinalIgnoreCase)) return "Bonde";
            if (word.Equals("Hender", StringComparison.OrdinalIgnoreCase)) return "Hand";
            if (word.Equals("Døtre", StringComparison.OrdinalIgnoreCase)) return "Datter";
            if (word.Equals("Fedre", StringComparison.OrdinalIgnoreCase)) return "Far";
            if (word.Equals("Mødre", StringComparison.OrdinalIgnoreCase)) return "Mor";
            if (word.Equals("Søstre", StringComparison.OrdinalIgnoreCase)) return "Søster";
            if (word.Equals("Øyne", StringComparison.OrdinalIgnoreCase)) return "Øye";

            // "ler" ending (from "el")
            if (word.EndsWith("ler"))
            {
                return word.Substring(0, word.Length - 2);
            }
            if (word.EndsWith("ter"))
            {
                return word.Substring(0, word.Length - 1);
            }

            // "ere" ending (from "er" ending in singular, e.g. "Lærere" -> "Lærer")
            if (word.EndsWith("ere"))
                return word.Substring(0, word.Length - 1);

            // "er" ending (general case, e.g. "Biler" -> "Bil", "Stoler" -> "Stol", "Jenter" -> "Jente")
            if (word.EndsWith("er"))
                return word.Substring(0, word.Length - 2);

            // "r" ending (from "e" ending in singular, e.g. "Jenter" -> "Jente" already handled above)
            if (word.EndsWith("r"))
            {
                var possibleSingular = word.Substring(0, word.Length - 1);
                return possibleSingular;
            }

            // Default: return as is
            return word;
        }

        /// <summary>
        /// Make the world normalized, i.e. first letter upper case and rest lower case letters, the word is trimmed.
        /// Not considering using invariant culture here, as this is a Norwegian pluralization service.
        /// </summary>
        /// <remarks>In case an empty word (null or empty) is passed in, just return the word.
        /// Edge case: In case just One non-empty letter was passed in, make the word also uppercase.</remarks>
        private string NormalizeWord(string word)
        {
            word = word?.Trim();
            if (string.IsNullOrEmpty(word) || word.Trim().Length <= 1) {
                return word?.ToUpper();
            }
            return word.Substring(0, 1).ToUpper() + word.Trim().ToLower().Substring(1);
        }

        private string[] _nonEndingWordsInPlural = new string[] {
            "mus", "sko", "ski", "feil", "ting" }; // Add more non-ending words in plural as needed

        private string[] _wordsChangingVowelsInPluralMale = new string[]
        {
            "bonde", "fot", "bok", "bot", "rot"
        };

        private Dictionary<string, string> _specialCases = new Dictionary<string, string>
        {
            { "Mann", "Menn" } , // 'mann' => 'menn'
            { "Barn", "Barn" }, // 'barn' => 'barn' (no pluralization)
            { "Øye", "Øyne" }, // 'øye' => 'øyne' (plural form of 'eye') //consider adding more special cases here in case all the other pluralization rules do not cover the given word
        };

        private string[] _wordsChangingVowelToÆ = new string[]
        {
            "Håndkle", "Kne", "Tre", "Tå"
        };

        private string[] _wordsForUnits = new string[]
        {
            "meter", "centimeter", "millimeter", "kilometer", "gram", "kilogram", "tonn", "liter", "desiliter", "centiliter", "dollar", "lire",
            "pesetas", "euro", "yen", "franc", "pund", "rupee", "ringgit", "peso", "real", "won", "yuan"
        };

        private string[] _wordChangingVowelsInPluralFemale = new string[]
        {
            "and", "hand", "hånd", "natt", "stang", "strand", "tang", "tann"
        };

        private string[] _wordsForRelatives = new string[]
        {
            "far", "mor", "datter", "fetter", "onkel", "bror", "svigerbror", "svigerfar", "svigermor", "svigersøster", "søster"
        };

        private string[] _wordsNoPluralizationForNeutralGenderOneSyllable = new string[]
        {
            "hus", "fjell", "blad"
        };

        private string[] _wordsNeutralGenderEndingWithEumOrIum = new string[]
        {
            "museum", "Jubileum", "kjemikalium"
        };

    }
}


The following DbConfiguration set up for the DbContext sets up the pluralization service to use for Entity Framework.

ApplicationDbConfiguration.cs



using BulkOperationsEntityFramework.Lib.Services;
using System;
using System.Data.Entity;
using System.Data.Entity.SqlServer;

namespace BulkOperationsEntityFramework
{
    public class ApplicationDbConfiguration : DbConfiguration
    {

        public ApplicationDbConfiguration()
        {
        
            SetPluralizationService(new NorwegianPluralizationService());  //Set up the NorwegianPluralizationService as the Pluralizer         
            
            //more code etc..
        }

    }

}


I have also created a Schema attribute to control schema names of tables convention previously in the solution, so the Norwegian pluralizer is also being used there.

SchemaConvention.cs



using BulkOperationsEntityFramework.Attributes;
using BulkOperationsEntityFramework.Lib.Services;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Reflection;

namespace BulkOperationsEntityFramework.Conventions
{
    public class SchemaConvention : Convention
    {
        public SchemaConvention()
        {
            var pluralizer = new NorwegianPluralizationService();

            Types().Configure(c =>
            {
                var schemaAttr = c.ClrType.GetCustomAttribute<SchemaAttribute>(false);
                var tableName = pluralizer.Pluralize(c.ClrType.Name);

                if (schemaAttr != null && !string.IsNullOrEmpty(schemaAttr.SchemaName))
                {
                    c.ToTable(tableName, schemaAttr.SchemaName ?? "dbo");
                }
                else
                {
                    c.ToTable(tableName);
                }
            });
        }
    }
}


The DbContext will use the IPluralizationService. Consider first this example DbContext :

ApplicationDbContext



using BulkOperationsEntityFramework.Conventions;
using BulkOperationsEntityFramework.Models;
using BulkOperationsEntityFramework.Test;
using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Linq;

namespace BulkOperationsEntityFramework
{

    [DbConfigurationType(typeof(ApplicationDbConfiguration))]
    public class ApplicationDbContext : DbContext
    {       

        public ApplicationDbContext(DbConnection connection) : base(connection, false)
        {
        }

        public ApplicationDbContext() : base("name=App")
        {
        }

        public virtual DbSet Bruker { get; set; }

        public DbSet ArkivertBruker { get; set; }

        public DbSet ArkivertGjest { get; set; }

        public DbSet Sesjon { get; set; }

        public DbSet Jubileum { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {            
            modelBuilder.Conventions.Add(new SchemaConvention());
            //more code etc
          
        }

    }

}


The following test cases checks how good the pluralizer works.

ApplicationDbContextTests.cs



using Bogus;
using BulkOperationsEntityFramework.Lib.Services;
using BulkOperationsEntityFramework.Models;
using FluentAssertions;
using Moq;
using Newtonsoft.Json;
using NUnit.Framework;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;

namespace BulkOperationsEntityFramework.Test
{

    [TestFixture]
    public class ApplicationDbContextTests
    {

       
        [Test]
        [TestCaseSource(nameof(NorwegianPluralizationCases))]
        public void CanUsePluralizationService(string word, string expected)
        {
            var norwegianPluralizationService = new NorwegianPluralizationService();
            string pluralizedWord = norwegianPluralizationService.Pluralize(word);
            pluralizedWord.Should().Be(expected, "Norwegian Pluralization service should return the correct plural form of the word.");
        }

        [Test, TestCaseSource(nameof(NorwegianSingularizationCases))]
        public void NorwegianPluralizationService_CanSingularize(string plural, string expectedSingular)
        {
            var norwegianPluralizationService = new NorwegianPluralizationService();

            var actual = norwegianPluralizationService.Singularize(plural);
            Assert.That(actual, Is.EqualTo(expectedSingular), $"Expected singular of '{plural}' to be '{expectedSingular}', but got '{actual}'.");
        }

        public static IEnumerable<TestCaseData> NorwegianPluralizationCases
        {
            get
            {
                yield return new TestCaseData("Bil", "Biler");
                yield return new TestCaseData("Bok", "Bøker");
                yield return new TestCaseData("Hund", "Hunder");
                yield return new TestCaseData("Stol", "Stoler");
                yield return new TestCaseData("Jente", "Jenter");
                yield return new TestCaseData("Gutt", "Gutter");
                yield return new TestCaseData("Lærer", "Lærere");
                yield return new TestCaseData("Barn", "Barn");
                yield return new TestCaseData("Fjell", "Fjell");
                yield return new TestCaseData("Sko", "Sko");
                yield return new TestCaseData("Ting", "Ting");
                yield return new TestCaseData("Mann", "Menn");
                yield return new TestCaseData("Kvinne", "Kvinner");
                yield return new TestCaseData("Bror", "Brødre");
                yield return new TestCaseData("Far", "Fedre");
                yield return new TestCaseData("Mor", "Mødre");
                yield return new TestCaseData("Datter", "Døtre");
                yield return new TestCaseData("Søster", "Søstre");
                yield return new TestCaseData("Øye", "Øyne");
                yield return new TestCaseData("Hand", "Hender");
                yield return new TestCaseData("Fot", "Føtter");
                yield return new TestCaseData("Tå", "Tær");
                yield return new TestCaseData("Tann", "Tenner");
                yield return new TestCaseData("Natt", "Netter");
                yield return new TestCaseData("Tre", "Trær");
                yield return new TestCaseData("Kne", "Knær");
                yield return new TestCaseData("Bonde", "Bønder");

                // _nonEndingWordsInPlural
                yield return new TestCaseData("Mus", "Mus");
                yield return new TestCaseData("Ski", "Ski");
                yield return new TestCaseData("Feil", "Feil");

                // _wordsChangingVowelsInPluralMale
                yield return new TestCaseData("Bot", "Bøter");
                yield return new TestCaseData("Rot", "Røter");

                // _wordsChangingVowelToÆ
                yield return new TestCaseData("Håndkle", "Håndklær");
                yield return new TestCaseData("Kne", "Knær");

                // _wordsForUnits (should not pluralize)
                yield return new TestCaseData("Meter", "Meter");
                yield return new TestCaseData("Gram", "Gram");
                yield return new TestCaseData("Dollar", "Dollar");

                // _wordChangingVowelsInPluralFemale
                yield return new TestCaseData("And", "Ender");
                yield return new TestCaseData("Hånd", "Hender");
                yield return new TestCaseData("Stang", "Stenger");
                yield return new TestCaseData("Strand", "Strender");
                yield return new TestCaseData("Tang", "Tenger");
                yield return new TestCaseData("Tann", "Tenner");

                // _wordsForRelatives (some already covered, but add missing)
                yield return new TestCaseData("Fetter", "Fettere");
                yield return new TestCaseData("Onkel", "Onkler");
                yield return new TestCaseData("Svigerbror", "Svigerbrødre");
                yield return new TestCaseData("Svigerfar", "Svigerfedre");
                yield return new TestCaseData("Svigermor", "Svigermødre");
                yield return new TestCaseData("Svigersøster", "Svigersøstre");

                // _wordsNoPluralizationForNeutralGenderOneSyllable
                yield return new TestCaseData("Hus", "Hus");
                yield return new TestCaseData("Blad", "Blad");

                // _wordsNeutralGenderEndingWithEumOrIum
                yield return new TestCaseData("Museum", "Museer");
                yield return new TestCaseData("Jubileum", "Jubileer");
                yield return new TestCaseData("Kjemikalium", "Kjemikalier");
            }
        }

        public static IEnumerable<TestCaseData> NorwegianSingularizationCases
        {
            get
            {
                yield return new TestCaseData("Biler", "Bil");
                yield return new TestCaseData("Bøker", "Bok");
                yield return new TestCaseData("Hunder", "Hund");
                yield return new TestCaseData("Stoler", "Stol");
                yield return new TestCaseData("Jenter", "Jente");
                yield return new TestCaseData("Gutter", "Gutt");
                yield return new TestCaseData("Lærere", "Lærer");
                yield return new TestCaseData("Barn", "Barn");
                yield return new TestCaseData("Fjell", "Fjell");
                yield return new TestCaseData("Sko", "Sko");
                yield return new TestCaseData("Ting", "Ting");
                yield return new TestCaseData("Menn", "Mann");
                yield return new TestCaseData("Kvinner", "Kvinne");
                yield return new TestCaseData("Brødre", "Bror");
                yield return new TestCaseData("Fedre", "Far");
                yield return new TestCaseData("Mødre", "Mor");
                yield return new TestCaseData("Døtre", "Datter");
                yield return new TestCaseData("Søstre", "Søster");
                yield return new TestCaseData("Øyne", "Øye");
                yield return new TestCaseData("Hender", "Hand");
                yield return new TestCaseData("Føtter", "Fot");
                yield return new TestCaseData("Tær", "Tå");
                yield return new TestCaseData("Tenner", "Tann");
                yield return new TestCaseData("Netter", "Natt");
                yield return new TestCaseData("Trær", "Tre");
                yield return new TestCaseData("Knær", "Kne");
                yield return new TestCaseData("Bønder", "Bonde");
            }
        }

    }
}


The pluralization is used when creating migrations and map entites to table names. The following migration shows how the Norwegian pluralization has pluralized the table names.

202506292302222_Init.cs



namespace BulkOperationsEntityFramework.Migrations
{
    using System.Data.Entity.Migrations;

    public partial class Init : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Archive.Arkivertbrukere",
                c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Email = c.String(maxLength: 255),
                    FirstName = c.String(maxLength: 255),
                    LastName = c.String(maxLength: 255),
                    PhoneNumber = c.String(maxLength: 255),
                })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "Arkiv.Gjester",
                c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Email = c.String(maxLength: 255),
                    FirstName = c.String(maxLength: 255),
                    LastName = c.String(maxLength: 255),
                })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "dbo.Brukere",
                c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Email = c.String(maxLength: 255),
                    FirstName = c.String(maxLength: 255),
                    LastName = c.String(maxLength: 255),
                    PhoneNumber = c.String(maxLength: 255),
                })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "dbo.Jubileer",
                c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Date = c.DateTime(),
                    Description = c.String(maxLength: 255),
                })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "dbo.Sesjoner",
                c => new
                {
                    Key = c.Guid(nullable: false),
                    CreatedAt = c.DateTime(nullable: false),
                    ExpiresAt = c.DateTime(),
                    IpAddress = c.String(maxLength: 255),
                    UserAgent = c.String(maxLength: 255),
                })
                .PrimaryKey(t => t.Key);

        }

        public override void Down()
        {
            DropTable("dbo.Sesjoner");
            DropTable("dbo.Jubileer");
            DropTable("dbo.Brukere");
            DropTable("Arkiv.Gjester");
            DropTable("Archive.Arkivertbrukere");
        }
    }
}


The table names above are pluralized into their Norwegian pluralization. In Norwegian we call these words "ubestemt flertall", indefinite plural. It is possible to add detection if the passed in noun is an English noun. But beware that there are several Norwegian nouns overlapping these English nouns. So in fairness, a Norwegian wordlist should also be checked. However, the following update only shows how an English dictionary can be checked. The English noun list is put into a static variable into memory for quick access. The world list I have tested with got about 40,0000+ noun as mentioned previously in this article.

NorwegianPluraizationService.cs

(updated with a check against a English noun list and using the default EnglishPluralizationService that Entity Framework contains to add support for English noun pluralization (and singularization)


using System;
using System.Collections.Generic;
using System.Data.Entity.Infrastructure.Pluralization;
using System.IO;
using System.Linq;
using System.Reflection;

namespace BulkOperationsEntityFramework.Lib.Services
{

    /// <summary>
    /// Sources for the pluralization rules for Norwegian language:
    /// https://toppnorsk.com/2018/11/18/flertall-hovedregler/
    /// </summary>
    public class NorwegianPluralizationService : IPluralizationService
    {
        private EnglishPluralizationService _englishPluralizationService = new EnglishPluralizationService();
        
        public static List<string> PluralizedWords = new List<string>();

        private static List<string> EnglishNounsWordList = null;

        public string Pluralize(string word)
        {
            if (PluralizedWords.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                return word; // Return the already pluralized word
            }

            //#if DEBUG
            //            Debugger.Break();
            //            Debugger.Launch(); // Uncomment this line to break into the debugger when this method is called, for example when database migrations are made with EF Code First
            //#endif

            word = NormalizeWord(word);

            if (EnglishNounsWordList == null)
            {
                EnglishNounsWordList = new List<string>();
                var assembly = typeof(NorwegianPluralizationService).Assembly;
                // Adjust the resource name to match your project's default namespace and folder structure
                var resourceName = "BulkOperationsEntityFramework.Lib.Services.EnglishNouns.txt";
                using (var stream = assembly.GetManifestResourceStream(resourceName))
                using (var reader = new StreamReader(stream))
                {
                    EnglishNounsWordList.AddRange(reader.ReadToEnd().Split('\n').Select(l => l.Trim()));
                }
            }

            string pluralizedWord;

            if (EnglishNounsWordList.Contains(word, StringComparer.OrdinalIgnoreCase))
            {
                pluralizedWord = _englishPluralizationService.Pluralize(word);
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }

            if (_specialCases.ContainsKey(word))
            {
                pluralizedWord = _specialCases[word];
                PluralizedWords.Add(pluralizedWord);
                return pluralizedWord;
            }
            
            //more code


To sum up, we can customize Entity Framework pluralization to support other languages. But please note that there is a lot of work to make a good pluralization service. Also, you probably want to support English nouns too. The last version above did not use a Norwegian wordlist. A better pluralization service could directly try to lookup pluralized and singularized nouns of both languages instead of all the general and specific rules presented in these code samples. The English pluralization service do not do this, instead it relies on rule sets, such as the first version of
the Norwegian pluralizer shown earlier in this article is using.

Saturday, 28 June 2025

Setting up connection resiliency for Entity Framework

In Entity Framework, it is possible to add more connection resiliency. This can be done for example if you are working against a more unstable database connection, maybe because the database is served in the Cloud and/or is not scaled properly to its load. Whatever reason, it is possible to add more connection resiliency. The connection resiliency can be used in other scenarios that just SQL servers hosted in Azure, such as On-Premise databases. It should add more resiliency and stability for scenarios where connections to database needs to be improved. This could also be due to mobile clients being moved in and out of areas with good network access, such as within buildings and factories on different levels trying to access a wireless connection that connects to a database. The code in this article is available in my Github repo here:

https://github.com/toreaurstadboss/BulkOperationsEntityFramework

first off, the ExecutionStrategy is set up. A DbConfiguration is added to set this up.

ApplicationDbModelConfiguration.cs



using System;
using System.Data.Entity;
using System.Data.Entity.SqlServer;

namespace BulkOperationsEntityFramework
{
    public class ApplicationDbConfiguration : DbConfiguration
    {

        public ApplicationDbConfiguration()
        {
            SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, () =>
             new CustomSqlAzureExecutionStrategy(maxRetryCount: 10, maxDelay: TimeSpan.FromSeconds(5))); //note : max total delay of retries is 30 seconds per default in SQL Server
        }

    }

}


In EF Core 8 (using Entity Framework with .NET 8, you can set it up like this :

Program.cs




    public class ApplicationDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                "DefaultConnection",
                sqlOptions =>
                {
                    sqlOptions.EnableRetryOnFailure(
                        maxRetryCount: 10,
                        maxRetryDelay: TimeSpan.FromSeconds(5),
                        errorNumbersToAdd: null
                    );
                });
        }



Setting up the interval strategy

The CustomSqlAzureExecutionStrategy inherits from the SqlAzureExecutionStrategy. delay = min ( maxDelay , random × ( 2 retryCount 1 ) × baseDelay ) The default base delay in Entity Framework is to wait one second, so the next wait time will be about 2 seconds, then the next delays will quickly grow up the max wait time of five seconds. The custom sql azure execution strategy implementation inherits from SqlAzureExecutionStrategy.

CustomSqlAzureExecutionStrategy.cs



using System;
using System.Data.Entity.SqlServer;

namespace BulkOperationsEntityFramework
{

    public class CustomSqlAzureExecutionStrategy : SqlAzureExecutionStrategy
    {

        [ThreadStatic]
        private static int _currentRetryCount = 0;

        public CustomSqlAzureExecutionStrategy(int maxRetryCount, TimeSpan maxDelay)
        : base(maxRetryCount, maxDelay) { }

        protected override bool ShouldRetryOn(Exception ex)
        {
            _currentRetryCount++;
            Console.WriteLine($"{nameof(CustomSqlAzureExecutionStrategy)}: Retry-count within thread: {_currentRetryCount}");
            Log.Information("{Class}: Retry-count within thread: {RetryCount} {ExceptionType}", nameof(CustomSqlAzureExecutionStrategy), _currentRetryCount, ex.GetType().Name);

            return base.ShouldRetryOn(ex) || ex is SimulatedTransientSqlException;
        }

    }

}


Of course, just logging out to console probably is not a very elegant solution, and it could instead be logged out to for example SeriLog, which is used in the line with the Log.Information call. The SimulatedTransientSqlException looks like this:

SimulatedTransientSqlException.cs



 public class SimulatedTransientSqlException : Exception
 {
     public SimulatedTransientSqlException()
     : base("Simulated transient SQL exception.") { }
 }
 

The following db interceptor is added to simulate transient failures happening, at 10% chance of it happening.

TransientFailureInterceptor.cs



using System;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Diagnostics;

namespace BulkOperationsEntityFramework
{

    public class TransientFailureInterceptor : DbCommandInterceptor
    {
        private static readonly Random _random = new Random();

        public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            SimulateTransientFailure(interceptionContext);
            base.ReaderExecuting(command, interceptionContext);
        }

        public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            SimulateTransientFailure(interceptionContext);
            base.ScalarExecuting(command, interceptionContext);
        }

        public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            SimulateTransientFailure(interceptionContext);
            base.NonQueryExecuting(command, interceptionContext);
        }

        private void SimulateTransientFailure<TResult>(DbCommandInterceptionContext<TResult> context)
        {
            // Simulate a transient failure 10% of the time
            double r = _random.NextDouble();
            if (r < 0.1)
            {
                var ex = new SimulatedTransientSqlException();
                string info = "Throwing a transient SqlException. ";
                Trace.WriteLine($"{info} {ex.ToString()}");
                context.Exception = ex;
            }
        }
    }

    public class SimulatedTransientSqlException : Exception
    {
        public SimulatedTransientSqlException()
        : base("Simulated transient SQL exception.") { }
    }

}


Next up connecting the dots in the DbContext, setting up the db configuration

ApplicationDbContext.cs



[DbConfigurationType(typeof(ApplicationDbConfiguration))]
public class ApplicationDbContext : DbContext
{

    static ApplicationDbContext()
    {
        if (!AppDomain.CurrentDomain.GetAssemblies().Any(a => a.FullName.StartsWith("Effort")))
        {
            DbInterception.Add(new TransientFailureInterceptor()); //add an interceptor that simulates a transient connection error occuring (30% chance of it happening)
            DbInterception.Add(new SerilogCommandInterceptor()); //do not add logging if EF6 Effor is used (for unit testing)
        }
    }
    
    //more code..


Also note that you usually do not want to add the TransientFailureInterceptor, it is just added for testing. You could for example add a boolean property on your DbContext to set if you are testing out connection resiliency and add the TransientFailureInterceptor when you can to test it, or provide a public method to add the TransientfailureInterceptor, and remove it afterwards if desired. Within a Test-project, you should be able to test out connection resiliency.

Tuesday, 24 June 2025

Custom code conventions in Entity Framework

This article will once more look at code conventions in Entity Framework. A custom code convention will be added where if the property (column of an entity) is called "Key" and is of type Guid, it is set as the key of the entity (surrounding type the property resides in). The code in this article is available in my Github repo here:

https://github.com/toreaurstadboss/BulkOperationsEntityFramework

The custom code convention for setting all properties called Key of property type Guid as the key of an entity (table) looks like the following:

GuidKeyConvention.cs



using System;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Reflection;

namespace BulkOperationsEntityFramework.Conventions
{

    public class GuidKeyConvention : Convention
    {
        public GuidKeyConvention()
        {
            Types().Configure(t =>
            {
                var keyProperty = t.ClrType
                    .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .FirstOrDefault(p => p.PropertyType == typeof(Guid)
                    && string.Equals(p.Name, "Key", StringComparison.OrdinalIgnoreCase));

                if (keyProperty != null)
                {
                    t.HasKey(keyProperty);
                }
            });            
        }
    }

}



The custom code convention can then be added in the OnModelCreating method shown below :

ApplicationDbContext.cs



 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
     modelBuilder.Entity<User>().HasKey(u => u.Id);
     modelBuilder.Entity<User>().Property(u => u.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

     modelBuilder.Properties<string>().Configure(p => p.HasMaxLength(255)); // Set max length for all string properties

     modelBuilder.Conventions.Add(new GuidKeyConvention());
     
     //more code

 }


As shown above, a custom convention inherits from the System.Data.Entity.ModelConfiguration.Conventions.Convention class. The behavior of the custom code convention is set up in the constructor of the custom code convention. It is not overriding any methods, instead it is making use over inherited public methods Types() or Properties() or Properties(). An example of an entity that then will use this custom code convention is shown with the following entity (POCO) :

Session.cs



using System;

namespace BulkOperationsEntityFramework.Models
{
    public class Session
    {

        public Guid Key { get; set; } // Primary key by convention

        public DateTime CreatedAt { get; set; }

        public DateTime? ExpiresAt { get; set; }

        public string IpAddress { get; set; }

        public string UserAgent { get; set; }
    }
}


Adding this custom code convention, the following database migration is then added and shows that the property (field/column) called Key of type Guid.


namespace BulkOperationsEntityFramework.Migrations
{
    using System.Data.Entity.Migrations;

    public partial class Sessions : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Sessions",
                c => new
                {
                    Key = c.Guid(nullable: false),
                    CreatedAt = c.DateTime(nullable: false),
                    ExpiresAt = c.DateTime(),
                    IpAddress = c.String(maxLength: 255),
                    UserAgent = c.String(maxLength: 255),
                })
                .PrimaryKey(t => t.Key);

        }

        public override void Down()
        {
            DropTable("dbo.Sessions");
        }
    }
}


As shown in the database migration, the primary key is set to the field Key, which means that the field (property/column) Key is set as the primary key. It is of course easier to just attribute the property Key with the Key attribute or set up the primary key in the OnModelConfiguring (Fluent API). Custom code conventions are best in use when you make a custom code convention that saves a lot of setup where you have a large data model and want to standardize code conventions, this sample just is a demonstration of how such a custom code convention can be created by inheriting the Convention class in the namespace System.Data.Entity.ModelConfiguration.Conventions. In the previous article, a custom code convention encapsulated in a custom marker attribute and wiring up the logic via helper extension methods. Instead of inheriting from the Convention class, the wiring up of Schema attribute shown in previous article could be more standardized in this way. Such a SchemaConvention could be set up like this instead, inheriting Convention class.

SchemaConvention.cs




using BulkOperationsEntityFramework.Attributes;
using System.Data.Entity.Infrastructure.Pluralization;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Reflection;

namespace BulkOperationsEntityFramework.Conventions
{
    public class SchemaConvention : Convention
    {
        public SchemaConvention()
        {
            var pluralizer = new EnglishPluralizationService();

            Types().Configure(c =>
            {
                var schemaAttr = c.ClrType.GetCustomAttribute<SchemaAttribute>(false);
                var tableName = pluralizer.Pluralize(c.ClrType.Name);

                if (schemaAttr != null && !string.IsNullOrEmpty(schemaAttr.SchemaName))
                {
                    c.ToTable(tableName, schemaAttr.SchemaName ?? "dbo");
                }
                else
                {
                    c.ToTable(tableName);
                }
            });
        }
    }
}  



Saturday, 21 June 2025

Creating a convention based Schema attribute in Entity Framework for .NET Framework

This article will present a way to create a Schema attribute for Entity Framework in .NET Framework. Some people still use .NET Framework, hopefully the latest supported version .NET Framework 4.8, due to compability reasons and legacy code and having a Schema attribute is not supported in Entity Framework for .NET Framework, still in Entity Framework 6.5.0 which is the newest version. This is similar to the way we can set Schema via attribute on an entity in Entity Framework .NET Core 8. The code presented in the article is in the following Github repo : https://github.com/toreaurstadboss/BulkOperationsEntityFramework The Schema attribute is a simple marker attribute where the schema name to be set to the entity (class) upon it is applied on.

SchemAttribute.cs



using System;

namespace BulkOperationsEntityFramework.Attributes
{

    [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
    public class SchemaAttribute : Attribute
    {
        private readonly string _schemaName;

        public SchemaAttribute(string schemaName)
        {
            _schemaName = schemaName ?? "dbo"; //fallback to default schema 'dbo' if null is passed in here             
        }

        public string SchemaName => _schemaName;

    }

}


The attribute will be used as an attribute-based code convention for Entity Framework. First off, the code conventions are set up using a helper extension. Note that it is not necessary to cast this to DbContext here, it just makes it more readable that we are passing in DbContext here.

ApplicationDbContext.cs



protected override void OnModelCreating(DbModelBuilder modelBuilder) {

// more code here if needed

  modelBuilder.ApplyCustomCodeConventions((DbContext)this); // Apply custom code conventions based on DbSet types. Pass in db context.

// more code here if needed

}

The helper method ApplyCustomCodeConventions will loop through all the DbSet generic properties of the passed in DbContext. DbModelBuilder is the instance that the helper extension method provides more functionality on.

ModelBuilderExtensions.cs



using BulkOperationsEntityFramework.Attributes;
using System.Data.Entity;
using System.Linq;
using System.Reflection;

namespace BulkOperationsEntityFramework
{

    public static class ModelBuilderExtensions
    {

        /// <summary>
        /// Applies custom code conventions to the specified <see cref="DbModelBuilder"/> instance based on the <see
        /// cref="DbSet{TEntity}"/> types defined in the provided <see cref="DbContext"/>.
        /// </summary>
        /// <remarks>This method inspects the <see cref="DbSet{TEntity}"/> properties of the provided <see
        /// cref="DbContext"/> and applies schema conventions to each entity type. It is typically used to enforce
        /// custom schema rules or configurations during model creation.</remarks>
        /// <param name="modelBuilder">The <see cref="DbModelBuilder"/> instance to which the conventions will be applied.</param>
        /// <param name="context">The <see cref="DbContext"/> containing the <see cref="DbSet{TEntity}"/> types to analyze.</param>
        public static void ApplyCustomCodeConventions(this DbModelBuilder modelBuilder, DbContext context)
        {
            var dbSetTypes = context
                .GetType()
                .GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>))
                .Select(p => p.PropertyType.GetGenericArguments()[0]);

            foreach (var type in dbSetTypes)
            {
                ApplySchemaAttributeConvention(modelBuilder, type);
            }

        }

        /// <summary>
        /// Adds a convention to apply the Schema attribute to set the schema name of entities in the DbContext.
        /// </summary>
        /// <param name="modelBuilder"></param>
        /// <param name="type"></param>
        private static void ApplySchemaAttributeConvention(DbModelBuilder modelBuilder, System.Type type)
        {
            var schema = type.GetCustomAttribute<SchemaAttribute>(false)?.SchemaName;
            if (schema != null)
            {
                var entityMethod = typeof(DbModelBuilder).GetMethod("Entity").MakeGenericMethod(type);
                var entityTypeConfiguration = entityMethod.Invoke(modelBuilder, null);
                var toTableMethod = entityTypeConfiguration.GetType().GetMethod("ToTable", new[] { typeof(string), typeof(string) });
                toTableMethod.Invoke(entityTypeConfiguration, new object[] { type.Name, schema });
            }
        }
    }

}


The schema attribute could also set one and one entity in the OnModelConfiguration method like for example the following:

ApplicationDbContext.cs



    modelBuilder.Types().Where(p => p.GetCustomAttributes(false).OfType<SchemaAttribute>().Any())
        .Configure(t => t.ToTable(t.ClrType.Name, t.ClrType.GetCustomAttribute<SchemaAttribute>().SchemaName ?? "dbo")); //add support for setting Schema via Schema attribute using custom code convention


But the code above is much better but in a reusable helper method as shown higher up in this article so you can easier just paste in the helper method and do a much cleaner call in your DbContext's OnConfiguring method. Note that the Schema attribute is available if you use .NET Core Entity Framework Core 8, that is using .NET 8. Example usage:


ArchivedUser.cs



using BulkOperationsEntityFramework.Attributes;

namespace BulkOperationsEntityFramework.Models
{

    [Schema("Archive")]
    public class ArchivedUser
    {

        public int Id { get; set; }

        public string Email { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string PhoneNumber { get; set; }

    }
    
}


When this is set up, we can easily set up the schema of an entity by just attributing the table using this Schema attribute.

Tuesday, 10 June 2025

Database logging Entity Framework queries to SeriLog

Logging database queries from Entity Framework can be done using Sql Server Profiler or using Profiling tools inside VS 2022. A problem with this approach is that Sql server profiler tends to log very much other events than just the queries to the database and if you use the Profiling tool, they will truncate large queries. Note that database logging the traffic will quickly grow into large logs, of several gigabyte. Database logging can also reveal sensitive data, another challenge. But having a tailored database log can for developers be a helpful tool to both profile and check queries, especially when using an ORM like Entity Framework that can hide the actual queries being sent to the database since that part is abstracted away via IQueryable. Once you have the queries you want to inspect, you can profile them inside SQL Server Management Studio and check if the use of indexes in database are optimal or if more indexes should be added. In case you as a developer want to inspect queries that your application does, maybe activated via an app setting in config, you might be better off with a tailored way of logging the database queries that the application performs. This article will look at Entity Framework 6 for .NET Framework and Db interceptor that logs compacted, one-lined, interpolated queries that Entity Framework runs. Note that also later version of Entity Framework support db interceptors, such as EF Core 8 (for .NET 8). Let's first look at the repo for the code of this article :

https://github.com/toreaurstadboss/BulkOperationsEntityFramework

SerilogCommandInterceptor

The db interceptor looks like this, it will log to a file that uses SeriLog as a logging framework and to format the logging file(s).


using Serilog;
using System;
using System.Configuration;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;

namespace BulkOperationsEntityFramework.Test
{

    /// <summary>
    /// Intercepts Entity Framework database commands and logs them using Serilog.
    /// 
    /// This interceptor captures and logs SQL command text for NonQuery, Reader, and Scalar operations.
    /// Logging is configured via Serilog and can be customized using <c>App.config</c> or <c>Web.config</c> settings.
    /// 
    /// <para>
    /// <b>Configuration via AppSettings:</b>
    /// </para>
    /// <list type="table">
    ///   <item>
    ///     <term>serilog:write-to:File.path</term>
    ///     <description>Path to the log file. Default: <c>databaselogs\log.txt</c></description>
    ///   </item>
    ///   <item>
    ///     <term>serilog:write-to:File.rollingInterval</term>
    ///     <description>Rolling interval for log files (e.g., <c>Day</c>, <c>Hour</c>). Default: <c>Day</c></description>
    ///   </item>
    ///   <item>
    ///     <term>serilog:minimum-level</term>
    ///     <description>Minimum log level (e.g., <c>Information</c>, <c>Warning</c>). Default: <c>Information</c></description>
    ///   </item>
    ///   <item>
    ///     <term>serilog:write-to:File.retainedFileCountLimit</term>
    ///     <description>Number of log files to retain. Default: <c>21</c></description>
    ///   </item>
    /// </list>
    /// 
    /// <para>
    /// <b>Example App.config override:</b>
    /// </para>
    /// <code language="xml">
    /// <appSettings>
    ///   <add key="serilog:write-to:File.path" value="C:\Logs\mydb.log" />
    ///   <add key="serilog:write-to:File.rollingInterval" value="Hour" />
    ///   <add key="serilog:minimum-level" value="Warning" />
    ///   <add key="serilog:write-to:File.retainedFileCountLimit" value="10" />
    /// </appSettings>
    /// </code>
    /// </summary>
    public class SerilogCommandInterceptor : IDbCommandInterceptor
    {

        private static bool _isInitialized = false;

        public SerilogCommandInterceptor()
        {
            if (_isInitialized)
            {
                return;
            }

            var logPath = ConfigurationManager.AppSettings["serilog:write-to:File.path"] ?? "databaselogs\\log.txt";
            var logIntervalRaw = ConfigurationManager.AppSettings["serilog:write-to:File.rollingInterval"];
            var logInterval = Enum.TryParse(logIntervalRaw, true, out RollingInterval interval) ? interval : RollingInterval.Day;
            var minLevelRaw = ConfigurationManager.AppSettings["serilog:minimum-level"];
            var logLevel = Enum.TryParse(minLevelRaw, true, out Serilog.Events.LogEventLevel level) ? level : Serilog.Events.LogEventLevel.Information;
            var retainedCountRaw = ConfigurationManager.AppSettings["serilog:write-to:File.retainedFileCountLimit"];
            var retainedCount = int.TryParse(retainedCountRaw, out int count) ? count : 21;

            //Set up Serilog logging for the database logging interceptor - set up the minimum level to Information and
            //write to a file with rolling intervals. Set up the file size limit to 500 MB per file
            //in case the log file grows too large on a given day, it will roll over to a new file with the with a running number suffixed to it 
            //the logs will be stored in the "databaselogs" subfolder, or configured path in config file
            //logs will be kept or a maximum number of 21 days or specified number of days
            Log.Logger = new LoggerConfiguration()
                .MinimumLevel.Is(logLevel)
                .WriteTo.File(
                    logPath, 
                    rollingInterval: logInterval, 
                    rollOnFileSizeLimit: true,
                    fileSizeLimitBytes: 500 * 1000 * 1000,
                    outputTemplate: "[{Timestamp:yyyy-MM-dd HH:mm:ss} {Level:u3}] [SQL] {Message:lj}{NewLine}",
                    retainedFileCountLimit: retainedCount
                )
                .CreateLogger();

            _isInitialized = true;
        }

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { }

        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) =>
            Log.Information("{Tag} {Sql}", GetSqlTag(command.CommandText), CompactAndInterpolateSql(command));

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }

        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) =>
            Log.Information("{Tag} {Sql}", GetSqlTag(command.CommandText), CompactAndInterpolateSql(command));

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) =>
            Log.Information("{Tag} {Sql}", GetSqlTag(command.CommandText), CompactAndInterpolateSql(command));

        private string CompactAndInterpolateSql(DbCommand dbCommand)
        {
            string sql = InterpolateSql(dbCommand);
            return CompactSql(sql);
        }

        private string CompactSql(string sql) =>
            sql.Replace(Environment.NewLine, " ").Replace("\n", "").Replace("\r", " ").Trim();

        private string GetSqlTag(string sql)
        {
            if (sql.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)) return "[SELECT]";
            if (sql.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase)) return "[INSERT]";
            if (sql.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase)) return "[UPDATE]";
            if (sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase)) return "[DELETE]";
            return "[SQL]";
        }

        private string InterpolateSql(DbCommand dbCommand)
        {
            string sql = dbCommand.CommandText;
            foreach (DbParameter parameter in dbCommand.Parameters)
            {
                string value = FormatParameterValue(parameter.Value);
                sql = sql.Replace(parameter.ParameterName, value);
            }
            return sql;
        }

        private string FormatParameterValue(object value)
        { 
            if (value == null || value == DBNull.Value)
            {
                return "NULL";
            }
            if (value is string || value is DateTime || value is Guid)
            {
                return $"'{value}'"; // Wrap strings, DateTime, and Guid in single quotes
            }
            if (value is bool b)
            {
                return b ? "1" : "0";
            }

            return value.ToString();
        }        

    }
}


Adding a db interceptor should be preferably done once, for example in a static constructor, to ensure the db interceptor is available right away. Example of a simple db context that adds the interceptor shown above.


using BulkOperationsEntityFramework.Models;
using BulkOperationsEntityFramework.Test;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;

namespace BulkOperationsEntityFramework
{

    public class ApplicationDbContext : DbContext
    {

        static ApplicationDbContext()
        {
            DbInterception.Add(new SerilogCommandInterceptor());
        }

        public ApplicationDbContext() : base("name=App")
        {
        }

        public DbSet<User> Users { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>().HasKey(u => u.Id);
            modelBuilder.Entity<User>().Property(u => u.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        }

    }

}




Since Serilog is used, a great flexibility to the logging is possible to do. For example, logging database queries can be done with rolling intervals, such as logging per hour or per day or even per minute for example. Also, a max size for logs can be set. The logging within the same time interval for the logging is split into multiple log files, when log files reaches size limit. Example app config settings are here:


<appSettings>
	<add key="serilog:write-to:File.path" value="logs\dblogv3.txt" />
	<add key="serilog:minimum-level" value="Information" />
	<add key="serilog:write-to:File.rollingInterval" value="Day" />
	<add key="serilog:file:retentionDays" value="21" />
</appSettings>


If you use a tool like TailBlazer, opening very large logs of many gigabytes is fairly fast and you can define highlighting rules to discern between different types of queries.
A screenshot of the highlighting rules of Tail Blazor for the previous screen shot shown is shown below:



Tail Blazer tool

The Tail Blazer tool can be downloaded from GitHub here:

https://github.com/RolandPheasant/TailBlazer

Sunday, 1 June 2025

Using SqlBulkCopy with EntityFramework

This article tests out variying methods of ways of doing bulk inserts in EntityFramework. The code shown in this article uses .NET Framework 4.8
and Entity Framework 6.5.0. Support is better in Entity Framework Core, so the code shown here for SqlBulkCopy must be adjusted a little bit to also work with EntityFramework Core. A Github repo has been added here:

https://github.com/toreaurstadboss/BulkOperationsEntityFramework

A benchmark has been run to test out the different approaches. Not suprisingly, SqlBulkCopy is the fastest and most economic way of performing the bulk inserts (uses the least memory). The code for handling SqlBulkCopy is provided via extension methods listed below:

BulkInsertExtensions.cs




using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace BulkOperationsEntityFramework.Lib.Extensions
{

    /// <summary>
    /// Provides extension methods for performing bulk insert operations using SqlBulkCopy.
    /// </summary>
    public static class BulkInsertExtensions
    {
        /// <summary>
        /// Performs a bulk insert of the specified entities into the database.
        /// </summary>
        /// <typeparam name="T">The type of the entity.</typeparam>
        /// <param name="context">The DbContext instance.</param>
        /// <param name="entities">The collection of entities to insert.</param>
        /// <param name="tableName">
        /// Optional: The name of the destination table. If null, the entity type name is used.
        /// </param>
        /// <param name="columnMappings">
        /// Optional: Custom column mappings (propertyName → columnName).
        /// 
        /// <example>
        /// Example 1: Rename columns
        /// <code>
        /// var mappings = new Dictionary<string, string>
        /// {
        ///     { "Name", "ProductName" },
        ///     { "Price", "UnitPrice" }
        /// };
        /// await context.BulkInsertAsync(products, "Products", mappings);
        /// </code>
        /// </example>
        /// 
        /// <example>
        /// Example 2: Ignore properties using attributes
        /// <code>
        /// public class Customer
        /// {
        ///     public int Id { get; set; }
        ///     public string FullName { get; set; }
        /// 
        ///     [BulkIgnore]
        ///     public string TempNotes { get; set; }
        /// 
        ///     [NotMapped]
        ///     public string CalculatedField { get; set; }
        /// }
        /// 
        /// var mappings = new Dictionary<string, string>
        /// {
        ///     { "FullName", "CustomerName" }
        /// };
        /// await context.BulkInsertAsync(customers, "Customers", mappings);
        /// </code>
        /// </example>
        /// 
        /// <example>
        /// Example 3: Snake_case mapping
        /// <code>
        /// var mappings = new Dictionary<string, string>
        /// {
        ///     { "FirstName", "first_name" },
        ///     { "LastName", "last_name" },
        ///     { "Email", "email_address" }
        /// };
        /// await context.BulkInsertAsync(users, "user_accounts", mappings);
        /// </code>
        /// </example>
        /// </param>
        /// <param name="bulkCopyTimout">The bulk copy timeout in seconds. Default is set to 30.</param>
        public static void BulkInsert<T>(this DbContext context, IEnumerable<T> entities, string tableName = null,
            Dictionary<string, string> columnMappings = null, int bulkCopyTimeout = 30)
            where T : class
        {
            var dataTable = entities.ToBulkDataTable(columnMappings, out var finalMappings);
            BulkCopy(context, dataTable, tableName ?? typeof(T).Name, finalMappings, bulkCopyTimeout);
        }

        /// <summary>
        /// Asynchronously performs a bulk insert of the specified entities into the database.
        /// </summary>
        /// <typeparam name="T">The type of the entity.</typeparam>
        /// <param name="context">The DbContext instance.</param>
        /// <param name="entities">The collection of entities to insert.</param>
        /// <param name="tableName">
        /// Optional: The name of the destination table. If null, the entity type name is used.
        /// </param>
        /// <param name="columnMappings">
        /// Optional: Custom column mappings (propertyName → columnName).
        /// 
        /// <example>
        /// Example 1: Rename columns
        /// <code>
        /// var mappings = new Dictionary<string, string>
        /// {
        ///     { "Name", "ProductName" },
        ///     { "Price", "UnitPrice" }
        /// };
        /// await context.BulkInsertAsync(products, "Products", mappings);
        /// </code>
        /// </example>
        /// 
        /// <example>
        /// Example 2: Ignore properties using attributes
        /// <code>
        /// public class Customer
        /// {
        ///     public int Id { get; set; }
        ///     public string FullName { get; set; }
        /// 
        ///     [BulkIgnore]
        ///     public string TempNotes { get; set; }
        /// 
        ///     [NotMapped]
        ///     public string CalculatedField { get; set; }
        /// }
        /// 
        /// var mappings = new Dictionary<string, string>
        /// {
        ///     { "FullName", "CustomerName" }
        /// };
        /// await context.BulkInsertAsync(customers, "Customers", mappings);
        /// </code>
        /// </example>
        /// 
        /// <example>
        /// Example 3: Snake_case mapping
        /// <code>
        /// var mappings = new Dictionary<string, string>
        /// {
        ///     { "FirstName", "first_name" },
        ///     { "LastName", "last_name" },
        ///     { "Email", "email_address" }
        /// };
        /// await context.BulkInsertAsync(users, "user_accounts", mappings);
        /// </code>
        /// </example>
        /// </param>
        /// <param name="bulkCopyTimout">The bulk copy timeout in seconds. Default is set to 30.</param>
        public static async Task BulkInsertAsync<T>(this DbContext context, IEnumerable<T> entities, string tableName = null,
            Dictionary<string, string> columnMappings = null, int bulkCopyTimout = 30)
            where T : class
        {
            var dataTable = entities.ToBulkDataTable(columnMappings, out var finalMappings);
            await BulkCopyAsync(context, dataTable, tableName ?? typeof(T).Name, finalMappings, 30);
        }

        private static void BulkCopy(DbContext context, DataTable table, string tableName,
            Dictionary<string, string> finalMappings, int bulkCopyTimeout = 30)
        {
            var connection = (SqlConnection)context.Database.Connection;
            var wasClosed = connection.State == ConnectionState.Closed;

            if (wasClosed)
                connection.Open();

            using (var bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.BulkCopyTimeout = bulkCopyTimeout;

                foreach (var map in finalMappings)
                {
                    bulkCopy.ColumnMappings.Add(map.Key, map.Value);
                }
                bulkCopy.WriteToServer(table);
            }

            if (wasClosed)
                connection.Close(); // Ensure the connection is closed after the operation, if it was closed before
        }

        private static async Task BulkCopyAsync(DbContext context, DataTable table, string tableName, Dictionary<string, string> mappings,
            int bulkCopyTimeout = 30)
        {
            var connection = (SqlConnection)context.Database.Connection;
            var wasClosed = connection.State == ConnectionState.Closed;

            if (wasClosed)
                await connection.OpenAsync();

            using (var bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
                foreach (var map in mappings)
                {
                    bulkCopy.ColumnMappings.Add(map.Key, map.Value);
                }
                await bulkCopy.WriteToServerAsync(table);
            }

            if (wasClosed)
                connection.Close();  //Ensure the connection is closed after the operation, if it was closed before
        }
    }
}





As the code shows, we use the DbContext from EntityFramework and get the connection string from the Database.Connection.ConnectionString property. We also use the Database.Connection property and cast it to SqlConnection to get the connection object. As we see, SqlBulkCopy is part of ADO.Net and relies upon column mappings. The code above will ignore the properties marked by the BulkIgnore attribute and the NotMapped attribute

BulkIgnoreAttribute.cs



using System;

namespace BulkOperationsEntityFramework.Lib.Attributes
{

    /// 
    /// Indicates that a property should be ignored during bulk insert operations.
    /// 
    [AttributeUsage(AttributeTargets.Property)]
    public class BulkIgnoreAttribute : Attribute
    {
    }

}


The following helper class creates a DataTable for a collection of entities (an IEnumerable) and uses reflection to make such a mapping. Note that column mappings can also be provided in the BulkInsertExtensions methods shown above to override property to column mapping, if desired. Default sql bulk copy timeout is set to 30 seconds (it is also the default timeout)

DataTableExtensions.cs


The following code provides code for creating data table for a collection of objects (IEnumerable). The BulkInsertExtensions uses the specific method ToBulkDataTable. This data table skips properties that are marked with [NotMapped] or [BulkIgnore] attributes. Both ToDataTable methods shown here allows column mappings to customize the property to column mappings.

using BulkOperationsEntityFramework.Lib.Attributes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace BulkOperationsEntityFramework.Lib.Extensions
{

    public static class DatatableExtensions
    {

        /// <summary>
        /// Converts an IEnumerable of type T to a DataTable.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="columnMappings"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IEnumerable<T> data, Dictionary<string, string> columnMappings = null)
        {
            var dataTable = new DataTable();
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (var prop in properties)
            {
                var columnName = columnMappings != null && columnMappings.ContainsKey(prop.Name) ? columnMappings[prop.Name] : prop.Name;
                dataTable.Columns.Add(columnName, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            foreach (var item in data)
            {
                var values = properties.Select(p => p.GetValue(item) ?? DBNull.Value).ToArray();
                dataTable.Rows.Add(values);
            }

            return dataTable;
        }

        /// <summary>
        /// Converts an IEnumerable of type T to a DataTable with specified column mappings. Tailored for Bulk operations.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="columnMappings"></param>
        /// <param name="finalMappings"></param>
        /// <returns></returns>
        public static DataTable ToBulkDataTable<T>(this IEnumerable<T> entities, Dictionary<string, string> columnMappings, out Dictionary<string, string> finalMappings)
        {
            var dataTable = new DataTable();
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(p =>
                    !Attribute.IsDefined(p, typeof(System.ComponentModel.DataAnnotations.Schema.NotMappedAttribute)) &&
                    !Attribute.IsDefined(p, typeof(BulkIgnoreAttribute)))
                .ToArray();

            finalMappings = new Dictionary<string, string>();

            foreach (var prop in properties)
            {
                var columnName = columnMappings != null && columnMappings.ContainsKey(prop.Name)
                    ? columnMappings[prop.Name]
                    : prop.Name;

                dataTable.Columns.Add(columnName, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                finalMappings[prop.Name] = columnName;
            }

            foreach (var entity in entities)
            {
                var values = properties.Select(p => p.GetValue(entity) ?? DBNull.Value).ToArray();
                dataTable.Rows.Add(values);
            }

            return dataTable;
        }

    }
}




BulkInsertBenchmark

The following benchmark is available in the solution for benchmarking the different approaches to bulk copy with EntityFramework.
  • EF - add one and save in a loop. Not suprisingly, performs the worst due to the many roundtrips to the database
  • EF - add one by one and save at the end. Better performance, since we have one roundtrip. Will handle poor cases where we try to add many items in a batch.
  • EF - addrange and save at the end. Similar to the one above, one roundtrip to database
  • Dapper - add as batch and save. Minor better speed than the previous two. One roundtrip to database.
  • SqlBulkCopy - clearly the fastest way to insert a batch of entities to the database



using BenchmarkDotNet.Attributes;
using Bogus;
using BulkOperationsEntityFramework.Lib.Extensions;
using BulkOperationsEntityFramework.Models;
using Dapper;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace BulkOperationsEntityFramework.Benchmarks
{

    [MemoryDiagnoser]
    public class BulkInsertBenchmark
    {

        private static readonly Faker Faker = new Faker();

        [Params(100)]
        public int Size { get; set; }

        //First benchmark - Naive approach - add one and one entity and save changes everytime with round trip to database

        [Benchmark(Description = "EFAddOneAndSave - Add one user and then save. Then add another one. Results in many round-trips to database.")]
        public async Task EFAddOneAndSave()
        {
            using (var context = new ApplicationDbContext())
            {
                foreach (var user in GetUsers())
                {
                    context.Users.Add(user);
                    await context.SaveChangesAsync();
                }
            }
        }

        [Benchmark(Description = "EFAddOneByOneAndSave - Add one by one user, but save once after adding them. Results in one round-trip to database")]
        public async Task EFAddOneByOneAndSave()
        {
            using (var context = new ApplicationDbContext())
            {
                foreach (var user in GetUsers())
                {
                    context.Users.Add(user);

                }
                await context.SaveChangesAsync();
            }
        }

        [Benchmark(Description = "EFAddRange - Adds the users, then does the save. Results in one round-trip to database")]
        public async Task EFAddRange()
        {
            using (var context = new ApplicationDbContext())
            {
                var users = GetUsers();
                context.Users.AddRange(users);
                await context.SaveChangesAsync();
            }
        }

        [Benchmark(Description = "DapperInsertRange - Uses Dapper to insert the users. Results in one round-trip to database")]
        public async Task DapperInsertRange()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["App"].ConnectionString;

            string sql = @"
                INSERT INTO Users (Email, FirstName, LastName, PhoneNumber)
                VALUES (@Email, @FirstName, @LastName, @PhoneNumber)
            ".Trim();

            using (var connection = new SqlConnection(connectionString))
            {
                var users = GetUsers().Select(u => new 
                {
                    u.Email,
                    u.FirstName,
                    u.LastName,
                    u.PhoneNumber
                }).ToArray();

                await connection.ExecuteAsync(sql, users);
            }
        }

        [Benchmark(Description = "SqlBulkCopy - Uses SqlBulkCopy to insert the users. Results in one round-trip to database")]
        public async Task SqlBulkCopy()
        {
            using (var context = new ApplicationDbContext())
            {
                await context.BulkInsertAsync(GetUsers(), "Users");
            }
        }



        private User[] GetUsers() =>
            Enumerable.Range(1, Size).Select(i => new User
            {
                Email = Faker.Internet.Email(),
                FirstName = Faker.Name.FirstName(),
                LastName = Faker.Name.LastName(),
                PhoneNumber = Faker.Phone.PhoneNumber()
            }).ToArray();

    }

}


The following screenshots shows the benchmark results after running Benchmark.NET for the mentioned approaches and comparing the performance.

Results

Benchmark for a given batch size of 100

Benchmark for a given batch size of 100

Sunday, 18 May 2025

Displaying weather data using Seaborn with Python

Using the library Seaborn, built on top of MatPlotLib, displaying weather data is convenient. Using Anaconda and Jupyter Notebook, working on the data is user friendly. First off, let's look at a data set containing some Norwegian weather data. The following data set, contains weather data from Norway from 2020-2021 for 55 meterological stations. (13,61 MB in size), available on DbCL v1.0 license (meaning, free of use , 'as-is' warranty) on the Kaggle.com website.

https://www.kaggle.com/datasets/annbengardt/noway-meteorological-data/data

First off, the following imports are done in the Jupyter Notebook. This is a free IDE part of the Anaconda distribution that is prepared for data visualization, that runs in a browser.

NorwayMeteoDemo1.pynb


import seaborn as sns
import pandas as pd
import matplotlib.pyplot as plt

Next off, using Pandas, Python's Data Analysis Library, the data is prepared from the mentioned dataset. The format is in CSV format. Also, columns are added dynamically to the dataset. A moving 14-days average of the daily maxmimum air temperature is added using the rolling method and setting window to 14. Also, a date column is added. Our dataset contains three int64 values day, month and year. We combine these to create a date column.

NorwayMeteoDemo1.pynb


df = pd.read_csv("datasets/weather/NorwayMeteoDataCompleted.csv")
df['moving_max_air_temp_avg'] = df['max(air_temperature P1D)'].rolling(window = 14, min_periods = 1).mean()
df['date'] = pd.to_datetime(df[['year', 'month', 'day']])

Note the use of min_periods set to 1 for the moving average. Or else, you will get NaN in the start of the data of your created moving average column and the way Python works, it will cause NaN for all the next periods too ! Next, choosing what data to display. The following data will be shown in the demo.
  • Station id : SN69100 (This is Værnes - Trondheim Airport weather station by the way,
  • Year 2020
The station ids can be looked up here on this user's Github GIST: https://gist.github.com/ofaltins/c1f0158f1766c8bd695b2c8d545c052c The following code set up the filtered data and saves it into a variable

NorwayMeteoDemo1.pynb


filtered_df_2020= df[ 
    (df['sourceId'] == 'SN69100')  &
    (df['year'] == 2020)
]

Next up, the data is then displayed. The demo will show two lineplots, first the maximum air temperature as a lineplot using Seaborn. In addition, another line plot with the moving 14 days average of the daily maximum air temperature. Also, a bar plot is added below these two line plots. Note that the bar plot used here is bar and not barplot. Barplot is available in Seaborn, while Bar is available in the MatPlotLib.

NorwayMeteoDemo1.pynb



sns.set_style('whitegrid') # set the style to 'whitegrid' 

# Create a 2x1 subplot

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(12, 10), sharex=True)

sns.lineplot(data = filtered_df_2020, x = filtered_df_2020['date'], y = filtered_df_2020['max(air_temperature P1D)'], label='Daily max air temperature (C)', linewidth = 1.5, color = 'pink', ax = ax1)

sns.lineplot(data = filtered_df_2020, x = filtered_df_2020['date'], y = filtered_df_2020['moving_max_air_temp_avg'], label='14-day moving average Daily max air temperature (C)', color = 'red', linewidth = 1.8, ax = ax1)

ax2.bar(filtered_df_2020['date'], filtered_df_2020['sum(precipitation_amount P1D)'], data = filtered_df_2020, color = 'blue', label = 'Daily sum precipitation (mm)')

ax1.set_title('Værnes - Weather data - 2020')

ax1.set_xlabel('Date of year')
ax1.set_ylabel('Daily max air temperature (C)')

ax2.set_xlabel('Date of year')
ax2.set_ylabel('Daily sum precipitation (mm)')



The code above shows the resulting figure consisting of a 2x1 subplots layout, the upper plot shows the 2020 daily maximum air temperature combined with a moving 14-days average as a smoothing or trending function to show the general temperature shifts every second week of the year in average. The lower plot shows a bar plot, using MatPlotLib, since Seaborn's bar plot does not handle dates as x-axis (in our dataset, datetime64 is used). Seaborn and MatPlotLib offers a ton of plotting functionality. Maybe it also could be of interest for .NET Developers to use it more often? My previous article showed how it is possible to render in the backend images using MatPlotLib and then display in a Blazor serverside app, combining both
Python and .NET. That demo used Python.Net library for the Python interop with .NET. The screenshot shows the Jupyter Notebook IDE, part of Anaconda distribution of Python tailored for data analysis and data science. It displays the plots described from the Python script above.

Monday, 5 May 2025

Using MatPlotLib from .NET

MatPlotLib is a powerful library for data visualization. It provides graphing for scientific computing. It can be used for doing both mathematical calculations and statistics. Together with additional libraries like NumPy or Numerical Python, it is clear that Python as a programming language and ecosystem provides a lot of powerful functionality that is also free to use. MatplotLib has a BSD license, which means it can be ued for personal, academic or commercial purposes without restrictions. This article will look at using MatplotLib from .NET. First off an image that displays the demo and example of using MatplotLib.

The source code shown in this article is available on Github here:

https://github.com/toreaurstadboss/SeabornBlazorVisualizer



Using MatPlotLib from .NET

First off, install Anaconda. Anaconda is a Python distribution that contains a large collection of data visualization libraries. A compatible version with the lastest version of Python.net Nuget library. The demo displayed here uses Anaconda version 2023.03.

Anaconda archived versions 2023.03 can be installed from here. Windows users can download the file: https://repo.anaconda.com/archive/Anaconda3-2023.03-1-Windows-x86_64.exe

https://repo.anaconda.com/archive/

Next up, install also Python 3.10 version. It will be used together with Anaconda. A 64-bit installer can be found here:

Python 3.10 installer (Windows 64-bits) The correct versions of NumPy and MatPlotLib can be checked against this list :

https://github.com/toreaurstadboss/SeabornBlazorVisualizer/blob/main/SeabornBlazorVisualizer/conda_list_loading_matplotlib_working_1st_May_2025.txt

Calculating the determinite integral of a function

The demo in this article show in the link at the top has got an appsettings.json file, you can adjust to your environment.

appsettings.json Application configuration file




{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "PythonConfig": {
    "PythonDllPath": "C:\\Python310\\Python310.dll",
    "PythonHome": "C:\\Programdata\\anaconda3",
    "PythonSitePackages":  "C:\\Programdata\\anaconda3\\lib\\site-packages",
    "PythonVersion": "3.10"
  },
  "AllowedHosts": "*"
}


Clone the source code and run the application. It is a Blazor server app. You can run it from VS 2022 for example. The following code shows how Python.net is set up to start using Python. Both Python 3.10 and Anaconda site libs are used here. The Python runtime and engine is set up using this helper class.

PythonInitializer.cs



using Microsoft.Extensions.Options;
using Python.Runtime;

namespace SeabornBlazorVisualizer.Data
{

    /// <summary>
    /// Helper class to initialize the Python runtime
    /// </summary>
    public static class PythonInitializer
    {

        private static bool runtime_initialized = false;

        /// <summary>
        /// Perform one-time initialization of Python runtime
        /// </summary>
        /// <param name="pythonConfig"></param>
        public static void InitializePythonRuntime(IOptions<PythonConfig> pythonConfig)
        {
            if (runtime_initialized)
                return;
            var config = pythonConfig.Value;

            // Set environment variables
            Environment.SetEnvironmentVariable("PYTHONHOME", config.PythonHome, EnvironmentVariableTarget.Process);
            Environment.SetEnvironmentVariable("PYTHONPATH", config.PythonSitePackages, EnvironmentVariableTarget.Process);
            Environment.SetEnvironmentVariable("PYTHONNET_PYDLL", config.PythonDllPath);
            Environment.SetEnvironmentVariable("PYTHONNET_PYVER", config.PythonVersion);

            PythonEngine.Initialize();

            PythonEngine.PythonHome = config.PythonHome ?? Environment.GetEnvironmentVariable("PYTHONHOME", EnvironmentVariableTarget.Process)!;
            PythonEngine.PythonPath = config.PythonDllPath ?? Environment.GetEnvironmentVariable("PYTHONNET_PYDLL", EnvironmentVariableTarget.Process)!;

            PythonEngine.BeginAllowThreads();
            AddSitePackagesToPythonPath(pythonConfig);
            runtime_initialized = true;
        }

        private static void AddSitePackagesToPythonPath(IOptions<PythonConfig> pythonConfig)
        {
            if (!runtime_initialized)
            {
                using (Py.GIL())
                {
                    dynamic sys = Py.Import("sys");
                    sys.path.append(pythonConfig.Value.PythonSitePackages);
                    Console.WriteLine(sys.path);

                    //add folders in solution this too with scripts
                    sys.path.append(@"Data/");
                }
            }
        }

    }
}



The following helper class sets up the site libraries we will use.

PythonHelper.cs



using Python.Runtime;

namespace SeabornBlazorVisualizer.Data
{

    /// <summary>
    /// Helper class to initialize the Python runtime
    /// </summary>
    public static class PythonHelper
    {

        /// <summary>
        /// Imports Python modules. Returned are the following modules:
        /// <para>np (numpy)</para>
        /// <para>os (OS module - standard library)</para>
        /// <para>scipy (scipy)</para>
        /// <para>mpl (matplotlib)</para>
        /// <para>plt (matplotlib.pyplot </para>
        /// </summary>
        /// <returns>Tuple of Python modules</returns>
        public static (dynamic np, dynamic os, dynamic scipy, dynamic mpl, dynamic plt) ImportPythonModules()
        {

            dynamic np = Py.Import("numpy");
            dynamic os = Py.Import("os");
            dynamic mpl = Py.Import("matplotlib");
            dynamic plt = Py.Import("matplotlib.pyplot");
            dynamic scipy = Py.Import("scipy");

            mpl.use("Agg");

            return (np, os, scipy, mpl, plt);
        }

    }
}



The demo is a Blazor server app. The following service will generate the plot of a determinite integral using MatPlotLib. The service saves the plot into a PNG file. This PNG file is saved into the folder wwwroot. The Blazor server app displays the image that was generated and saved.

MatPlotImageService.cs



using Microsoft.Extensions.Options;
using Python.Runtime;

namespace SeabornBlazorVisualizer.Data
{
    public class MatplotPlotImageService
    {

        private IOptions<PythonConfig>? _pythonConfig;

        private static readonly object _lock = new object();

        public MatplotPlotImageService(IOptions<PythonConfig> pythonConfig)
        {
            _pythonConfig = pythonConfig;
            PythonInitializer.InitializePythonRuntime(_pythonConfig);
        }

        public Task<string> GenerateDefiniteIntegral(string functionExpression, int lowerBound, int upperBound)
        {

            string? result = null;

            using (Py.GIL()) // Ensure thread safety for Python calls
            {
                dynamic np = Py.Import("numpy");
                dynamic plt = Py.Import("matplotlib.pyplot");

                dynamic patches = Py.Import("matplotlib.patches"); // Import patches module

                // Create a Python execution scope
                using (var scope = Py.CreateScope())
                {
                    // Define the function inside the scope
                    scope.Exec($@"
import numpy as np
def func(x):
    return {functionExpression}
");

                    // Retrieve function reference from scope
                    dynamic func = scope.Get("func");

                    // Define integration limits
                    double a = lowerBound, b = upperBound;

                    // Generate x-values
                    dynamic x = np.linspace(0, 10, 100); //generate evenly spaced values in range [0, 20], 100 values (per 0.1)
                    dynamic y = func.Invoke(x);

                    // Create plot figure
                    var fig = plt.figure();
                    var ax = fig.add_subplot(111);

                    // set title to function expression
                    plt.title(functionExpression);

                    ax.plot(x, y, "r", linewidth: 2);
                    ax.set_ylim(0, null);

                    // Select range for integral shading
                    dynamic ix = np.linspace(a, b, 100);
                    dynamic iy = func.Invoke(ix);

                    // **Fix: Separate x and y coordinates properly**
                    List<double> xCoords = new List<double> { a }; // Start at (a, 0)
                    List<double> yCoords = new List<double> { 0 };

                    int length = (int)np.size(ix);
                    for (int i = 0; i < length; i++)
                    {
                        xCoords.Add((double)ix[i]);
                        yCoords.Add((double)iy[i]);
                    }

                    xCoords.Add(b); // End at (b, 0)
                    yCoords.Add(0);

                    // Convert x and y lists to NumPy arrays
                    dynamic npVerts = np.column_stack(new object[] { np.array(xCoords), np.array(yCoords) });

                    // **Correctly Instantiate Polygon Using NumPy Array**
                    dynamic poly = patches.Polygon(npVerts, facecolor: "0.6", edgecolor: "0.2");
                    ax.add_patch(poly);

                    // Compute integral area
                    double area = np.trapezoid(iy, ix);
                    ax.text(0.5 * (a + b), 30, "$\\int_a^b f(x)\\mathrm{d}x$", ha: "center", fontsize: 20);
                    ax.text(0.5 * (a + b), 10, $"Area = {area:F2}", ha: "center", fontsize: 12);

                    plt.show();


                    result = SavePlot(plt, dpi: 150);
                }
            }
            return Task.FromResult(result);
        }

        public Task<string> GenerateHistogram(List<double> values, string title = "Provide Plot title", string xlabel = "Provide xlabel title", string ylabel = "Provide ylabel title")
        {
            string? result = null;
            using (Py.GIL()) //Python Global Interpreter Lock (GIL)
            {
                var (np, os, scipy, mpl, plt) = PythonHelper.ImportPythonModules();

                var distribution = np.array(values.ToArray());

                //// Ensure clearing the plot
                //plt.clf();

                var fig = plt.figure(); //create a new figure
                var ax1 = fig.add_subplot(1, 2, 1);
                var ax2 = fig.add_subplot(1, 2, 2);

                // Add style
                plt.style.use("ggplot");

                var counts_bins_patches = ax1.hist(distribution, edgecolor: "black");

                // Normalize counts to get colors 
                var counts = counts_bins_patches[0];
                var patches = counts_bins_patches[2];

                var norm_counts = counts / np.max(counts);

                int norm_counts_size = Convert.ToInt32(norm_counts.size.ToString());

                // Apply colors to patches based on frequency
                for (int i = 0; i < norm_counts_size; i++)
                {
                    plt.setp(patches[i], "facecolor", plt.cm.viridis(norm_counts[i])); //plt.cm is the colormap module in MatPlotlib. viridis creates color maps from normalized value 0 to 1 that is optimized for color-blind people.
                }

                // **** AX1 Histogram first - frequency counts ***** 

                ax1.set_title(title);
                ax1.set_xlabel(xlabel);
                ax1.set_ylabel(ylabel);

                string cwd = os.getcwd();

                // Calculate average and standard deviation
                var average = np.mean(distribution);
                var std_dev = np.std(distribution);
                var total_count = np.size(distribution);

                // Format average and standard deviation to two decimal places
                var average_formatted = np.round(average, 2);
                var std_dev_formatted = np.round(std_dev, 2);

                //Add legend with average and standard deviation
                ax1.legend(new string[] { $"Total count: {total_count}\n Average: {average_formatted} cm\nStd Dev: {std_dev_formatted} cm" }, framealpha: 0.5, fancybox: true);



                //***** AX2 : Set up ax2 = Percentage histogram next *******

                ax2.set_title("Percentage distribution");
                ax2.set_xlabel(xlabel);
                ax2.set_ylabel(ylabel);
                // Fix for CS1977: Cast the lambda expression to a delegate type
                ax2.yaxis.set_major_formatter((PyObject)plt.FuncFormatter(new Func<double, int, string>((y, _) => $"{y:P0}")));

                ax2.hist(distribution, edgecolor: "black", weights: np.ones(distribution.size) / distribution.size);

                // Format y-axis to show percentages
                ax2.yaxis.set_major_formatter(plt.FuncFormatter(new Func<double, int, string>((y, _) => $"{y:P0}")));

                // tight layout to prevent overlap 
                plt.tight_layout();

                // Show the plot with the two subplots at last (render to back buffer 'Agg', see method SavePlot for details)
                plt.show();

                result = SavePlot(plt, theme: "bmh", dpi: 150);
            }

            return Task.FromResult(result);
        }

        public Task<string> GeneratedCumulativeGraphFromValues(List<double> values)
        {
            string? result = null;
            using (Py.GIL()) //Python Global Interpreter Lock (GIL)
            {
                var (np, os, scipy, mpl, plt) = PythonHelper.ImportPythonModules();

                dynamic pythonValues = np.cumsum(np.array(values.ToArray()));

                // Ensure clearing the plot
                plt.clf();

                // Create a figure with increased size
                dynamic fig = plt.figure(figsize: new PyTuple(new PyObject[] { new PyFloat(6), new PyFloat(4) }));

                // Plot data
                plt.plot(values, color: "green");

                string cwd = os.getcwd();

                result = SavePlot(plt, theme: "ggplot", dpi: 200);

            }

            return Task.FromResult(result);
        }

        public Task<string> GenerateRandomizedCumulativeGraph()
        {
            string? result = null;
            using (Py.GIL()) //Python Global Interpreter Lock (GIL)
            {

                dynamic np = Py.Import("numpy");

                //TODO : Remove imports of pandas and scipy and datetime if they are not needed

                Py.Import("pandas");
                Py.Import("scipy");
                Py.Import("datetime");
                dynamic os = Py.Import("os");

                dynamic mpl = Py.Import("matplotlib");
                dynamic plt = Py.Import("matplotlib.pyplot");

                // Set dark theme
                plt.style.use("ggplot");

                mpl.use("Agg");


                // Generate data
                //dynamic x = np.arange(0, 10, 0.1);
                //dynamic y = np.multiply(2, x); // Use NumPy's multiply function

                dynamic values = np.cumsum(np.random.randn(1000, 1));


                // Ensure clearing the plot
                plt.clf();

                // Create a figure with increased size
                dynamic fig = plt.figure(figsize: new PyTuple(new PyObject[] { new PyFloat(6), new PyFloat(4) }));

                // Plot data
                plt.plot(values, color: "blue");

                string cwd = os.getcwd();

                result = SavePlot(plt, theme: "ggplot", dpi: 200);

            }

            return Task.FromResult(result);
        }

        /// <summary>
        /// Saves the plot to a PNG file with a unique name based on the current date and time
        /// </summary>
        /// <param name="plot">Plot, must be a PyPlot plot use Python.net Py.Import("matplotlib.pyplot")</param>
        /// <param name="theme"></param>
        /// <param name="dpi"></param>
        /// <returns></returns>
        public string? SavePlot(dynamic plt, string theme = "ggplot", int dpi = 200)
        {
            string? plotSavedImagePath = null;
            //using (Py.GIL()) //Python Global Interpreter Lock (GIL)
            //{
            dynamic os = Py.Import("os");
            dynamic mpl = Py.Import("matplotlib");
            // Set dark theme
            plt.style.use(theme);
            mpl.use("Agg"); //set up rendering of plot to back-buffer ('headless' mode)

            string cwd = os.getcwd();
            // Save plot to PNG file
            string imageToCreatePath = $@"GeneratedImages\{DateTime.Now.ToString("yyyyMMddHHmmss")}{Guid.NewGuid().ToString("N")}_plotimg.png";
            string imageToCreateWithFolderPath = $@"{cwd}\wwwroot\{imageToCreatePath}";
            plt.savefig(imageToCreateWithFolderPath, dpi: dpi); //save the plot to a file (use full path)
            plotSavedImagePath = imageToCreatePath;

            CleanupOldGeneratedImages(cwd);
            //}
            return plotSavedImagePath;
        }

        private static void CleanupOldGeneratedImages(string cwd)
        {
            lock (_lock)
            {

                Directory.GetFiles(cwd + @"\wwwroot\GeneratedImages", "*.png")
                 .OrderByDescending(File.GetLastWriteTime)
                 .Skip(10)
                 .ToList()
                 .ForEach(File.Delete);
            }
        }

}



The code above shows some additional examples of using MatPlotLib.
  • Histogram example
  • Line graph using cumulative sum by making use of NumPy or a helper method in .NET
These examples demonstrates also that MatPlotLib can be used for statistics, which today for .NET is mostly crunched with the help of Excel or EP Plus library for example. Since Python is considered as the home of data visualization with its vast ecosystem of data science libraries, this article and demos shows how you can get started with using this ecosystem from .NET. Note, using Python.net to create these plots in MatPlotLib is best prepared using Jupyter Notebook. When the plot displayed looks okay, it is time to integrate that Python script into .NET and C# using Python.Net library. Make note that there will be some challenges to get the Python code to work in C# of course. When passing in values to a function, sometimes you must use
for example NumPy to create compatible data types. Also note the usage of the Pystatic class here from Python.net , which offers the GIL Global Interpreter lock and a way to import Python modules.

https://jupyter.org/ A screenshot showing histogram in the demo is shown below. As we can see, MatPlotLib can be used from many different data visualizations and domains.