search

JasperFx / marten

.NET Transactional Document DB and Event Store on PostgreSQL
https://martendb.io
MIT License
2.83k stars 449 forks source link

Unable to add parameters to MatchesJsonPath feature #3495

Open Richard87 opened 3 hours ago

Richard87 commented 3 hours ago

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

```text System.InvalidCastException: Writing values of 'Weasel.Postgresql.SqlGeneration.CommandParameter' is not supported for parameters having NpgsqlDbType 'Text'. at Npgsql.Internal.AdoSerializerHelpers.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.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[j__TPar] at Weasel.Core.DatabaseProvider`3.ToParameterType(Type type) at Weasel.Postgresql.PostgresqlProvider.determineParameterType(Type type, NpgsqlDbType& dbType) at Weasel.Core.DatabaseProvider`3.TryGetDbType(Type type) at Weasel.Postgresql.SqlGeneration.CommandParameter..ctor(Object value) at Marten.Linq.MatchesSql.MatchesJsonPathParser.<>c.b__2_0(Object x) at System.Linq.Enumerable.SelectArrayIterator`2.Fill(ReadOnlySpan`1 source, Span`1 destination, Func`2 func) at System.Linq.Enumerable.SelectArrayIterator`2.ToArray() at Marten.Linq.MatchesSql.MatchesJsonPathParser.Parse(IQueryableMemberCollection memberCollection, IReadOnlyStoreOptions options, MethodCallExpression expression) at Marten.Linq.Parsing.WhereClauseParser.VisitMethodCall(MethodCallExpression node) at Marten.Linq.Parsing.WhereClauseParser.Visit(Expression node) at Marten.Linq.SqlGeneration.Statement.ParseWhereClause(IReadOnlyList`1 wheres, IMartenSession session, IQueryableMemberCollection collection, IDocumentStorage storage) at Marten.Linq.CollectionUsage.BuildTopStatement(IMartenSession session, IQueryableMemberCollection collection, IDocumentStorage storage, QueryStatistics statistics) at Marten.Linq.Parsing.LinqQueryParser.BuildStatements() at Marten.Linq.Parsing.LinqQueryParser.BuildListHandler[T]() 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 380 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.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) ```

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
Richard87 commented 3 hours ago

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 :/

jeremydmiller commented 1 hour ago

@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.

Richard87 commented 1 hour ago

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?

jeremydmiller commented 1 hour ago

"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.