Sql Server Storage and LINQ expression translation Issue

[miljan012]

What happened:
I have a .NET 6 app and HealthChecks UI configured to use InMemoryStorage. It works perfectly.

I wanted to try out the SqlServer storage but after switching from InMemory to MsSQL storage, I started getting errors.

The database migrations are run and the database is created successfully. However, the health check ui no longer displays any of the health checks and the error I'm getting is the following:

System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(h => string.Equals(
a: h.HealthCheckName,
b: __healthCheckName_0,
comparisonType: OrdinalIgnoreCase))' could not be translated

The full stack trace is here:

HealthChecks.UI.Core.HostedService.HealthCheckCollectorHostedService[0]
HealthCheck collector HostedService threw an error: The LINQ expression 'DbSet()
.Where(h => string.Equals(
a: h.HealthCheckName,
b: __healthCheckName_0,
comparisonType: OrdinalIgnoreCase))' could not be translated. Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(h => string.Equals(
a: h.HealthCheckName,
b: __healthCheckName_0,
comparisonType: OrdinalIgnoreCase))' could not be translated. Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at HealthChecks.UI.Core.HostedService.HealthCheckReportCollector.ShouldNotifyAsync(String healthCheckName) in //src/HealthChecks.UI/Core/HostedService/HealthCheckReportCollector.cs:line 201
at HealthChecks.UI.Core.HostedService.HealthCheckReportCollector.Collect(CancellationToken cancellationToken) in //src/HealthChecks.UI/Core/HostedService/HealthCheckReportCollector.cs:line 76
at HealthChecks.UI.Core.HostedService.HealthCheckCollectorHostedService.CollectAsync(CancellationToken cancellationToken) in /_/src/HealthChecks.UI/Core/HostedService/HealthCheckReportCollectorHostedService.cs:line 84

What you expected to happen:
I expect the Health Checks UI to work with Sql Server Storage. As mentioned above, no issues whatsoever with the InMemory Storage.

How to reproduce it (as minimally and precisely as possible):
The code sample and the app settings are below. This is the basic configuration, so nothing special, it's the code from the examples basically.

Source code sample:

builder.Services
    .AddHealthChecksUI() 
    .AddSqlServerStorage("...");
  
//...

app.UseHealthChecksUI();

//...
 
app.MapHealthChecksUI(o =>
{
    o.UIPath = "/dashboard";
    o.AddCustomStylesheet("dashboard.css");
});

appsettings.json:

"HealthChecksUI": {
"EvaluationTimeInSeconds": 10,
"MinimumSecondsBetweenFailureNotifications": 30,
"MaximumHistoryEntriesPerEndpoint": 50,
"HealthChecks": [
{
"Name": "Some API health check",
"Uri": "https://someApiUrl/hc"
},
{
"Name": "Some other API health check",
"Uri": "https://someOtherApiUrl/hc"
}
]
}

Anything else we need to know?:
I tried last 2 or 3 versions of HealthChecks.UI libraries and the same error appears in all of them.
I also tried switching back to InMemory storage and it works again with no issues, as expected.

Environment:

• .NET Core version 6
• Healthchecks.UI 7.0.2
• Healthchecks.UI.Client 7.1.0
• Healthchecks.UI.SqlServer.Storage 7.0.0
• Operative system: Win10

[Xor-el]

Hello, @miljan012 this issue has been fixed in the latest codebase via PR1995.
However, I am not sure if a new Nuget package that incorporates the fix has been released.
I think you have 2 options as of now.

1. nudge the author to release a new Nuget package incorporating the fix (not sure about his release cycle)
2. add the latest code as a project reference to your project to work around the issue until the author comes around and releases a new package.

[Genide]

This issue affects the Sqlite storage provider as well.
This is a blocking problem for anyone using any of the storage providers.
Can we get a new release that includes the fix?

This the stack trace of the error I got with sqlite.

[11:34:35 ERR] HealthCheck collector HostedService threw an error: The LINQ expression 'DbSet<HealthCheckFailureNotification>()
    .Where(h => string.Equals(
        a: h.HealthCheckName,
        b: __healthCheckName_0,
        comparisonType: OrdinalIgnoreCase))' could not be translated. Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
System.InvalidOperationException: The LINQ expression 'DbSet<HealthCheckFailureNotification>()
    .Where(h => string.Equals(
        a: h.HealthCheckName,
        b: __healthCheckName_0,
        comparisonType: OrdinalIgnoreCase))' could not be translated. Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at HealthChecks.UI.Core.HostedService.HealthCheckReportCollector.ShouldNotifyAsync(String healthCheckName) in /_/src/HealthChecks.UI/Core/HostedService/HealthCheckReportCollector.cs:line 201
   at HealthChecks.UI.Core.HostedService.HealthCheckReportCollector.Collect(CancellationToken cancellationToken) in /_/src/HealthChecks.UI/Core/HostedService/HealthCheckReportCollector.cs:line 76
   at HealthChecks.UI.Core.HostedService.HealthCheckCollectorHostedService.CollectAsync(CancellationToken cancellationToken) in /_/src/HealthChecks.UI/Core/HostedService/HealthCheckReportCollectorHostedService.cs:line 84