Presentation in Norwegian presenting demo repository with GraphQL and Hotchocolate/Strawberryshake

I have written a presentation about GraphQL demo repository of mine here. It is in Norwegian, so it will not be translated to english here. For Norwegian readers : Jeg har skrevet en presentasjon på norsk om GraphQL som går igjennom et demo repository som benytter GraphQL, med HotChocolate i backend, sammen med Entity Framework Core 6 og .net 6 (C# selvsagt) og som i frontend benytter Blazor og StrawberryShake ! Dere kan lese de 43 slidesene vedlagt OneDrive lenken under. I foredraget går jeg igjennom key giveaways om GraphQL, inkludert case insensitive søk, omtale om paginerte data og projisering og gir et overblikk av hva GraphQL går ut på og hvilke fordeler man kan få ut av det. Sentrale fordeler med GraphQL er : * Fleksibilitet - spesifiser hvilke felter du vil ha for å unngå "overfetching" * -Ytelse - færre API kall og unngår waterfall opphenting hvor man må hente opp stadig flere ressurser som i fra et REST API, men får en aggreggert tilpasset struktur av de data man faktisk vil ha tilbake * Ett felles endepunkt /graphql - man slipper å lage controllere som i REST API - som ofte føles som unødvendig. GraphQL er ikke noe som kan løse alle utfordringer i API design, men det kan gi klienter mye mer fleksibilitet og også unngå at API designere må stadig lage flere metoder og som har både "overfetching" eller enda verre - underfetching - som gir flere API kall og dårligere ytelse. Man utnytter båndbredde og serverressurser bedre ved å kun hente ut informasjon man trenger. Og GraphQL er ikke bare orientert rundt spørringer, men også endringer (mutations), pub sub event pattern (Subscriptions) og en hel del annen funksjonalitet som tilhører API design ! Du kan lese Powerpoint presentasjonen her (43 slides, lesetid ca 1 time om du vil studere det nøye, en 15 minutt om du vil skumlese mer). #blazor #hotchocolate #strawberryshake #chillicream #apidesign #csharp #dotnet #codinggrounds The presentation is here :

Powerpoint presentation (Norwegian, 11th december 2022) : https://1drv.ms/u/s!AhGGDxs-tzqJcFrls6Fue8Xnjx4?e=lWYYwU

Overcoming limitations in Contains in Entity Framework

Entity Framework will hit a performance penalty bottleneck or crash if Contains contains a too large of a list. Here is how you can avoid this, using Marc Gravell's excellent approach in this. I am including some tests of this. I also suggest you consider LinqKit to use expandable queries to make this all work. First off, this class contains the extension methods for Entity Framework for this:

 
 
 public class EntityExtensions {
 
 /// <summary>
        /// This method overcomes a weakness with Entity Framework with Contains where you can partition the values to look for into 
        /// blocks or partitions, it is modeled after Marc Gravell's answer here:
        /// https://stackoverflow.com/a/568771/741368
        /// Entity Framework hits a limit of 2100 parameter limit in the DB but probably comes into trouble before this limit as even
        /// queries with several 100 parameters are slow.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TValue"></typeparam>
        /// <param name="source">Source, for example DbSet (table)</param>
        /// <param name="selector">Selector, key selector</param>
        /// <param name="blockSize">Size of blocks (chunks/partitions)</param>
        /// <param name="values">Values as parameters</param>
        /// 
        /// <example>
        ///   /// <[!CDATA[
        /// /// The following EF query will hit a performance penalty or time out if EF gets a too large list of operationids:
        /// ///
        /// /// var patients = context.Patients.Where(p => operationsIds.Contains(p.OperationId)).Select(p => new {
        /// ///  p.OperationId,
        /// ///  p.
        /// /// });
        /// ///
        /// /// 
        /// /// var patients = context.Patients.AsExpandable().InRange(p => p.OperationId, 1000, operationIds)
        /// //.Select(p => new
        /// //{
        /// //    p.OperationId,
        /// //    p.IsDaytimeSurgery
        /// //}).ToList();
        /// //]]
        /// </example>
        /// <returns></returns>
        public static IEnumerable<T> InRange<T, TValue>(
                this IQueryable<T> source,
                Expression<Func<T, TValue>> selector,
                int blockSize,
                IEnumerable<TValue> values)
        {
            MethodInfo method = null;
          
            foreach (MethodInfo tmp in typeof(Enumerable).GetMethods(
                    BindingFlags.Public | BindingFlags.Static))
            {
                if (tmp.Name == "Contains" && tmp.IsGenericMethodDefinition
                        && tmp.GetParameters().Length == 2)
                {
                    method = tmp.MakeGenericMethod(typeof(TValue));
                    break;
                }
            }

            if (method == null) throw new InvalidOperationException(
                   "Unable to locate Contains");
            foreach (TValue[] block in values.GetBlocks(blockSize))
            {
                var row = Expression.Parameter(typeof(T), "row");
                var member = Expression.Invoke(selector, row);
                var keys = Expression.Constant(block, typeof(TValue[]));
                var predicate = Expression.Call(method, keys, member);
                var lambda = Expression.Lambda<Func<T, bool>>(
                      predicate, row);
                foreach (T record in source.Where(lambda))
                {
                    yield return record;
                }
            }

        }

        /// <summary>
        /// Similar to Chunk, it partitions the IEnumerable source and returns the chunks or blocks by given blocksize. The last block can have variable length
        /// between 0 to blocksize since the IEnumerable can have of course variable size not evenly divided by blocksize. 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="blockSize"></param>
        /// <returns></returns>
        public static IEnumerable<T[]> GetBlocks<T>(
                this IEnumerable<T> source, int blockSize)
        {
            List<T> list = new List<T>(blockSize);
            foreach (T item in source)
            {
                list.Add(item);
                if (list.Count == blockSize)
                {
                    yield return list.ToArray();
                    list.Clear();
                }
            }
            if (list.Count > 0)
            {
                yield return list.ToArray();
            }
        }
        
  }

 
 
 

Linqkit allows us to rewrite queries for EF using expression trees. One class is ExpandableQuery. See the links here for further info about Linqkit and Linq-Expand.

 
  	/// <summary>Refer to http://www.albahari.com/nutshell/linqkit.html and
	/// http://tomasp.net/blog/linq-expand.aspx for more information.</summary>
	public static class Extensions
	{
		public static IQueryable<T> AsExpandable<T> (this IQueryable<T> query)
		{
			if (query is ExpandableQuery<T>) return (ExpandableQuery<T>)query;
			return new ExpandableQuery<T> (query);
		}
 

This all seems to look a bit cryptic, so lets see an integration test of mine instead:

 


        [Test]
        [Category(TestCategories.IntegrationTest)]
        public void GetDataChunkedDoesNotFail()
        {
            using (var context = DbContextManager.ScopedOpPlanDataContext)
            {
                int[] operationalUnitIds = new int[]{ 107455, 105431, 107646, 107846 };
                var reportItems = context.OperationalUnits.AsExpandable().InRange(ou => ou.FreshOrganizationalUnitId, 2, operationalUnitIds).ToList();
                Assert.IsNotNull(reportItems);           
                CollectionAssert.IsNotEmpty(reportItems); 
            }
        }

 
 
 

This shows how to use the InRange method of Marc Gravell. We use the AsExpandable method to allow us to hack into the expression tree of Entity Framework and the InRange method allows us to partition the work for EF. We do not know the siz of operational unit ids (usually it is low and another entity - operation Ids is of variable length and will in production blow up since we in some cases surpass the 2100 limit of Contains). And as I said before, Entity Framework will hit a performance bottleneck before 2100 parameteters are sent into the Contains method. This way of fixing it up will allow you to get stable running code in production again against large data and variable length. This code is tested with Entity Framework 6.2.0. Another article considers performance considerations for Contains and different approaches here: https://www.toptal.com/dot-net/entity-framework-performance-using-contains IMHO this approach has proven stable in a production environment for several years with large data and can be considered a stable workaround for EF slow Contains performance. I have made the LinqKit fork LinqKit.AsyncSupport available on Nuget here now: https://www.nuget.org/packages/ToreAurstadIt.LinqKit.AsyncSupport/1.1.0 This makes it possible to perform Async calls and expandable queries, i.e. queries with inline method calls for example. The nuget package now also sports symbol package for easier debugging experience. The source code for LinqKit.AsyncSupport is available here: https://github.com/toreaurstadboss/LinqKit.AsyncSupport

Outputting runnable SQL from Entity Framework 6.x

This article will describe how you can output runnable SQL from Entity Framework. The output will be sent to the Console and Debug. You can easily modify this to output to other output sources, such as tracing or files for that matter. What is important is that we need to interpolate the parameters from Entity Framework so that we get a runnable SQL. Entity Framework parameterizes the SQL queries such that SQL injection is avoided. Where conditions and similar are inserted into parameters, notably with the p__linq naming convention. We will interpolate these parameters into runnable SQL such that you can paste SQL into SQL Server Management Studio (SMSMS). Or you could save the runnable SQL to a .sql file and let SQLCMD run it from command line. Either way, we must set up the DbContext to do this. I am using Entity Framework 6.2.0. It should be possible to use this technique with all EF 6.x version. In Entity Framework Core and Entity Framework Core 2, the techniques will be similar. First define a DbConfiguration and attribute the DbContext class you are using like this with the DbConfigurationType (we are not considering ObjectContext in this article, but DbContext is a wrapper around this class anyways, so you should be apply to techniques taught here to other scenarios).

SomeAcmeDbContext.cs


 namespace SomeAcme.Data.EntityFramework
{
     
    [DbConfigurationType(typeof(SomeAcmeDataContextConfiguration))]
    public partial class SomeAcmeDataContext : System.Data.Entity.DbContext, ISomeAcmeDataContext
    {
      ..
 


 

Ok, so our DbConfiguration just inherits from DbConfiguration and sets up a custom DatabaseLogFormatter like this:

  
SomeAcmeDataContextConfiguration.cs


  
  using System.Data.Entity;

namespace SomeAcme.Data.EntityFramework.DbContext
{
    public class  SomeAcmeDataContextConfiguration : DbConfiguration
    {
        public SomeAcmeDataContextConfiguration()
        {
            SetDatabaseLogFormatter((context, logAction) => new SomeAcmeDbLogFormatter(context, logAction));
        }
    }
}
   
  

SetDatabaseLogFormatter is a protected method o DbConfiguration. Our DatabaseLogFormatter implementation then looks like this:

 
SomeAcmeDbLogFormatter.cs
 

   using System;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using SomeAcme.Data.EntityFramework.DbContext.Extensions;

namespace SomeAcme.Data.EntityFramework.DbContext
{
    public class SomeAcmeDbLogFormatter : DatabaseLogFormatter
    {
        public SomeAcmeDbLogFormatter(System.Data.Entity.DbContext dbContext, Action<string> loggingAction) :
            base(dbContext, loggingAction)
        {
            
        }

        public override void LogCommand<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
        {
            string cmdText = command.CommandText;
            if (string.IsNullOrEmpty(cmdText))
                return;
            if (cmdText.StartsWith("Openend connection", StringComparison.InvariantCultureIgnoreCase) ||
                cmdText.StartsWith("Closed connection", StringComparison.InvariantCultureIgnoreCase))
                return;
            
            Write($"--DbContext {Context.GetType().Name} is executing command against DB {Context.Database.Connection.Database}: {Environment.NewLine}{command.GetGeneratedQuery().Replace(Environment.NewLine, "")} {Environment.NewLine}");
        }

        public override void LogResult<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
        {
            //empty by intention
        }
    }
 }
  
   
  

We also have a helper extension method called GetGeneratedQuery on DbCommand objects to help us get the cruft of this article - the interpolated runnable query. From my testing we can just interpolate the parameters as is in most use cases. However, some datatypes in the T-SQL world must be quoted (like, strings) and we need to adjust the date and time data types to a runnable format too. In case you find this helper method should be interpolated, please let me know. Our helper method GetGeneratedQuery looks like this:

  
SomeAcmeDbCommandExtensions.cs


  using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SomeAcme.Data.EntityFramework.DbContext.Extensions
{
    public static class DbCommandExtensions
    {

        /// <summary>
        /// Returns the generated sql string where parameters are replaced by value. Generated a runnable
        /// SQL script. Note that this is an approximation anwyays, but gives us a runnable query. The database server query engine optimizer will possible rewrite
        /// even simple queries if it sees it possible to rearrange the query to predictively create a more efficient query.
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <returns></returns>
        public static string GetGeneratedQuery(this DbCommand dbCommand)
        {
            DbType[] quotedParameterTypes = new DbType[] {
                DbType.AnsiString, DbType.Date,
                DbType.DateTime, DbType.DateTime2, DbType.Guid, DbType.String,
                DbType.AnsiStringFixedLength, DbType.StringFixedLength
            };
            var sb = new StringBuilder();
            sb.AppendLine(dbCommand.CommandText);

            var arrParams = new SqlParameter[dbCommand.Parameters.Count];
            dbCommand.Parameters.CopyTo(arrParams, 0); //copy dbCommand parameters into another collection to avoid 
            //mutating the query and be able to run a foreach loop 

            foreach (SqlParameter p in arrParams.OrderByDescending(p => p.ParameterName.Length))
            {
                string value = p.Value.ToString();
                if (p.DbType == DbType.Date || p.DbType == DbType.DateTime || p.DbType == DbType.DateTime2)
                {
                    value = DateTime.Parse(value).ToString("yyyy-MM-dd HH:mm:ss.fff");
                }
                if (quotedParameterTypes.Contains(p.DbType))
                    value = "'" + value + "'";
                sb.Replace("@" + p.ParameterName, value);
            }

            return sb.ToString();

        }
    }
}

 

We also need to activate database logging in the first place. Database logging to the console and debug should be avoided in production in ordinary cases, as they make a performance impact. Instead, it is handy to turn it on or off via an app setting. I have decided to only allow it while debugging so my constructors of my DbContext where I have tested it calls this method:

SomeAcmeDbContext.cs
 (once more need to add some code)
 
  
  
   private void SetupDbContextBehavior()
        {
            Configuration.AutoDetectChangesEnabled = true;
            Configuration.LazyLoadingEnabled = true;         
            ObjectContext.CommandTimeout = 10 * 60;


#if DEBUG
            //To enable outputting database traffic to the console, set the app setting OutputDatabaseTrafficLogging in web.config to true
            //this must not be activated in production. To safe guard this,
            //this block below is wrapped in the debug preprocessor directive.
            bool outputDatabaseTrafficLogging = ConfigurationManagerWrapper.GetAppsetting(SomeAcme.Common.Constants.OutputDatabaseTrafficLogging);
            if (outputDatabaseTrafficLogging)
            {
                Database.Log = s =>
                {
                    if (s.StartsWith("Opened connection", StringComparison.InvariantCultureIgnoreCase)
                    || s.StartsWith("Closed connection", StringComparison.InvariantCultureIgnoreCase))
                        return;
                    Console.WriteLine(s);
                    Debug.WriteLine(s);
                };
            }

#endif
 
 

Never mind the first three lines, they are just added here as tips for additional settings you CAN set if you want to. The important bit is the Database.Log delegate property, which acceps a lambda for example where you set up what to do with the logging. Here we just tell the DbContext that if the app setting OutputDatabaseTrafficLogging is set to true, we output the runnable SQL from Entity Framework to the console. That's all there is to it! You can now activate the app setting and see in the debug output (or in console) runnable SQL. And you can paste the SQL into SMSS for example to check for performance issues such as missing indexes and similar or tune up the size of the result sets and alter the SQL. You should also consider making your DbContext runnable in Linqpad for easier tuning of EF queries, but that is for another article. Happy coding!

Deploying an SQL Express database in Azure Devops pipeline with YAML and generating and updating the database with migrate scripts using EF Core Code First tools

Here a full example of how I achieved running Integration tests using Sql Express in Azure Devops. I had to use the YAML based pipelines so I could use simonauner's approach using Chocolatey to install Sql Express. Make note that I had to install EF Core tools since I use .Net Core 3.1 in this pipeline. Also note that I generate an EF Code First migration SQL file on the fly so that the rigged SQL Express instance is filled with contents. Deploy SQL Express instance in Azure Devops, install and generate and run EF Code first migration sql script to update database with schema and seed data using EF Code First tools.

# ASP.NET Core (.NET Framework)
# Build and test ASP.NET Core projects targeting the full .NET Framework.
# Add steps that publish symbols, save build artifacts, and more:
# https://docs.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- feature/testability

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- script: choco install sql-server-express
- task: NuGetToolInstaller@1

- task: VisualStudioTestPlatformInstaller@1
  displayName: 'Visual Studio Test Platform Installer'
  inputs:
    versionSelector: latestStable

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: DotNetCoreCLI@2
  displayName: Build
  inputs:
    command: build
    projects: '**/*.csproj'
    arguments: '--configuration Debug' # Update this to match your need

- script: 'dotnet tool install --global dotnet-ef'
  displayName: 'Generate EF Code First Migrations SQL Script Update script'

- script: 'dotnet ef migrations script -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql --project .\SomeAcme\SomeAcme.csproj'
  displayName: 'Generate EF Code First Migrations migrate.sql'

- script: 'sqlcmd -S .\SQLEXPRESS -Q "CREATE DATABASE [SomeAcmeDb]"'
  displayName: 'Create database SomeAcmeDb in Azure Devops SQL EXPRESS'

- script: 'sqlcmd -i %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql -S .\SQLEXPRESS -d SomeAcmeDb'
  displayName: ' Run migrate.sql on SQL EXPRESS in Azure Devops'

# PowerShell
# Run a PowerShell script on Linux, macOS, or Windows
- task: PowerShell@2
  inputs:
    targetType: 'inline' # Optional. Options: filePath, inline
    #filePath: # Required when targetType == FilePath
    #arguments: # Optional
    script: 'gci -recurse -filter *.dll' # Required when targetType == Inline
    #errorActionPreference: 'stop' # Optional. Options: stop, continue, silentlyContinue
    #failOnStderr: false # Optional
    #ignoreLASTEXITCODE: false # Optional
    #pwsh: false # Optional
    #workingDirectory: # Optional

- task: VSTest@2
  displayName: 'VsTest - testAssemblies'
  inputs:
    testAssemblyVer2: |
     **\*SomeAcme.Tests.dll
     !**\*TestAdapter.dll
     !**\obj\**
    vsTestVersion: toolsInstaller
    testFiltercriteria: 'Category=IntegrationTest'
    runInParallel: false
    codeCoverageEnabled: false
    testRunTitle: 'XUnit tests SomeAcme solution integration test starting'
    failOnMinTestsNotRun: true
    rerunFailedTests: false