JasperFx
Unable to add parameters to MatchesJsonPath feature
Possibly related to #3045
I'm struggling to add parameters to a MatchesJSONPath call... I have tried this;
string key = entry.Key;
qb = qb.Where(x => x.MatchesJsonPath($"data['VerifiedHealthResults'][^]", key));
// System.InvalidCastException: Writing values of 'Weasel.Postgresql.SqlGeneration.CommandParameter' is not supported for parameters having NpgsqlDbType 'Text'.
Details
System.InvalidCastException: Writing values of 'Weasel.Postgresql.SqlGeneration.CommandParameter' is not supported for parameters having NpgsqlDbType 'Text'.
at Npgsql.Internal.AdoSerializerHelpers.<GetTypeInfoForWriting>g__ThrowWritingNotSupported|1_0(Type type, PgSerializerOptions options, Nullable`1 pgTypeId, Nullable`1 npgsqlDbType, Exception inner)
at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForWriting(Type type, Nullable`1 pgTypeId, PgSerializerOptions options, Nullable`1 npgsqlDbType)
at Npgsql.NpgsqlParameter.ResolveTypeInfo(PgSerializerOptions options)
at Npgsql.NpgsqlParameterCollection.ProcessParameters(PgSerializerOptions options, Boolean validateValues, CommandType commandType)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
at JasperFx.Core.Exceptions.ExceptionTransformExtensions.TransformAndThrow(IEnumerable`1 transforms, Exception ex)
at JasperFx.Core.Exceptions.ExceptionTransforms.TransformAndThrow(Exception ex)
at Marten.Exceptions.MartenExceptionTransformer.WrapAndThrow(Exception exception)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
at Marten.Linq.MartenLinqQueryable`1.ToListAsync[TResult](CancellationToken token)
at Happydogs.ReadModels.SearchV2Endpoint.Get(IQuerySession session, CancellationToken ct, ILogger`1 logger, String query, String locations, String breeds, Boolean includeDogs, Boolean includeBreeders, Boolean includeLitters, Nullable`1 dogIsMale, Nullable`1 dogIsAvailableForBreeder, String healthResults, Nullable`1 litterFollowsNkkGuidelines, Nullable`1 litterFollowsClubGuidelines, Nullable`1 litterIsAvailable, Nullable`1 breederPlannedLitters, Nullable`1 breederLittersAvailable) in C:\Users\Richard\Projects\happydogs\backend\Happydogs\ReadModels\SearchV2Projection.cs:line 378
at Internal.Generated.WolverineHandlers.GET_api_search_v2.Handle(HttpContext httpContext) in C:\Users\Richard\Projects\happydogs\backend\Web\Internal\Generated\WolverineHandlers\GET_api_search_v2.cs:line 64
at Internal.Generated.WolverineHandlers.GET_api_search_v2.Handle(HttpContext httpContext) in C:\Users\Richard\Projects\happydogs\backend\Web\Internal\Generated\WolverineHandlers\GET_api_search_v2.cs:line 67
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
And this:
qb = qb.Where(x => x.MatchesJsonPath($"data['VerifiedHealthResults'][^]", new
{
Id = entry.Key
}));
// System.NotSupportedException: Can't infer NpgsqlDbType for type <>f__AnonymousType1`1[<Id>j__TPar]
Details
```text
System.NotSupportedException: Can't infer NpgsqlDbType for type f__AnonymousType1`1[
Version:
Searching 'JasperFx.CodeGeneration.Commands, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' for commands
Searching 'Weasel.CommandLine, Version=7.5.0.0, Culture=neutral, PublicKeyToken=null' for commands
Searching 'Marten.CommandLine, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' for commands
Searching 'Wolverine, Version=2.17.0.0, Culture=neutral, PublicKeyToken=null' for commands
Searching 'Wolverine.Http.FluentValidation, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' for commands
Current solution 🧸
var conditions = entry.Select(x => $"'{x.Escaped()}'").Join(",");
qb = qb.Where(x => x.MatchesJsonPath($"data['VerifiedHealthResults']['{entry.Key.Escaped()}'] ?| array[{conditions}]"));
and
public static class PostgresqlStringEscape
{
public static string Escaped(this string s) => s.Replace("'", "''");
}
not great :/
@Richard87 What is it you expect to happen here? I think this is an "I take pull requests" kind of issue. I feel like I gave you workarounds that would have been easier than what you ended up doing.
I expect that parameters in a json Path works...
I don't think reorganizing the data in a different way would help me in this case (this is already v2 of the projection to allow all the queries we need).
I would love to look at submitting a PR to fix it, but was struggling to understand the code while debugging it, could you give some pointers on where the key elements are in the code?
"I don't think reorganizing the data in a different way would help me in this case (this is already v2 of the projection to allow all the queries we need)." -- all you had to do was make a calculated property with the array of values within the one slot on the dictionary. That would have been so much easier than what you did.
It's open source code man, first thing I would do is trace the code and the Weasel helpers. You can do the exact same thing. The LINQ support isn't approachable in the slightest and I'm not personally willing to spend the time that would take to do what your model was forcing you to do, but taking on something to make the straight up SQL approach easier isn't that bad.
I have gotten back and rewritten my read-model, I know have a list of HealthTests("
So basically searching for a list of strings, in a list of strings:
select data['DogRegistryName'], data['HealthTest']
from app.public.mt_doc_searchv2view_2
where data['HealthTest'] ?| ARRAY ['7.Bærer', '7.Fri']
"""Heyhey""" "[""1.1"", ""1.1"", ""7.Bærer"", ""7.Bærer""]"
"""Testhund""" "[""56.Fri"", ""7.Fri"", ""3.2"", ""6.Bærer"", ""10.Fri""]"
// All dogs with health data:
"""Heyhey""" "[""1.1"", ""1.1"", ""7.Bærer"", ""7.Bærer""]"
"""Sable Shade's Yoshi""" "[""26.Fri""]"
"""Testhund""" "[""56.Fri"", ""7.Fri"", ""3.2"", ""6.Bærer"", ""10.Fri""]"
"""yoshi""" "[""9.Delvis""]"
But running this code:
foreach(IGrouping<string, string> entry in parsedHealthResults)
{
List<string> conditions = [];
foreach (var condition in entry)
{
conditions.Add($"{entry.Key}.{condition}");
}
qb = qb.Where(x => conditions.IsOneOf(x.HealthTest)); //System.InvalidOperationException: variable 'x' of type 'Happydogs.ReadModels.SearchV2View' referenced from scope '', but it is not defined
qb = qb.Where(x => x.HealthTest.IsOneOf(conditions)); // System.InvalidCastException: Writing values of 'System.Collections.Generic.List`1[[System.String, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]][]' is not supported for parameters having NpgsqlDbType '-2147483629'.
// qb = qb.Where(x => x.HealthTest.Intersect(conditions).Any()); // System.ArgumentException: Static field requires null instance, non-static field requires non-null instance. (Parameter 'field')
// qb = qb.Where(x => x.HealthTest.IsSubsetOf(conditions)); // no failure, but returned all dogs
// qb = qb.Where(x => x.HealthTest.IsSupersetOf(conditions)); // no failure, but returned no dogs
}
Is there any other options I'm missing?
Don't know, I would have expected any or all of that to work out of the box. Do you wanna make a more details reproduction? Failing test please? There's something you're doing that I'm not seeing.
Alright, reading the tests, i found this that works great:
qb = qb.Where(x => x.HealthTest.Any(s => conditions.Contains(s)));
I also se the LINQ test for Supersets works great, so I'm not sure its worth doing anything with Intersects, especially also with the above alternative... So I'm closing this issue