LINQ query results in SQL syntax error

[LoicLopesS2H]

With Marten 7.26.4, the following exception is raised :
---> Npgsql.PostgresException (0x80004005): 42601: syntax error on or close to « ) » (translated from french error message)
The (simplified) query is

        bool isTrue = true;
        bool isFalse = false;
        var intList = new List<int>();
        return await session.Query<DLine>().Where(x =>
            (isTrue
                || (isFalse && x.Files.Any(z => z.Name.Contains("TEST")))
                )
            && (intList.Contains((int)x.DStatus)))
            .ToListAsync(ct);

If you want to be able to reproduce it, you can use the following program.cs in a Microsoft.NET.Sdk.Web project:

using JasperFx.CodeGeneration;
using Marten;
using Marten.Events.Projections;
using Microsoft.AspNetCore.Mvc;
using Oakton;
using Weasel.Core;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
builder.Host.ApplyOaktonExtensions();

// This is the absolute, simplest way to integrate Marten into your
// .NET application with Marten's default configuration
builder.Services.AddMarten(options =>
{
    // Establish the connection string to your Marten database
    options.Connection("Host=localhost;Port=5433;Database=testmarten;Username=testmarten;password=testmarten;Include Error Detail=true");

    options.AutoCreateSchemaObjects = AutoCreate.All;
    options.GeneratedCodeMode = TypeLoadMode.Static;

    // Specify that we want to use STJ as our serializer
    options.UseSystemTextJsonForSerialization();

    options.Projections.Snapshot<DLine>(SnapshotLifecycle.Inline);
});

var app = builder.Build();

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.MapGet("/dlines",
    async ([FromServices] IDocumentStore store, CancellationToken ct) =>
    {
        // Open a session for querying documents only
        await using var session = store.QuerySession();

        bool isTrue = true;
        bool isFalse = false;
        var intList = new List<int>();
        return await session.Query<DLine>().Where(x =>
            (isTrue
                || (isFalse && x.Files.Any(z => z.Name.Contains("TEST")))
                )
            && (intList.Contains((int)x.DStatus)))
            .ToListAsync(ct);
    });

await app.RunOaktonCommands(args);

public class DLine
{
    public Guid Id { get; set; }
    public DateTime DateCreation { get; set; }
    public DStatus DStatus { get; set; }
    public IEnumerable<DFile> Files { get; set; } = new List<DFile>();

    public DLine()
    {
    }

    public DLine(DLineCreated dLineCreated)
    {
        DateCreation = dLineCreated.DateCreation;
        DStatus = dLineCreated.DStatus;
        Files = dLineCreated.Files ?? new List<DFile>();
    }
}

public record DLineCreated(DateTime DateCreation, DStatus DStatus, List<DFile> Files);

public enum DStatus
{
    Success = 0,
    Failure = 1,
    PartialSucces = 2,
    Fixed = 3
}

public class DFile
{
    public string Name { get; set; }
    public int? NbOfLines { get; set; }
}

LinqBugMarten8.zip

[jeremydmiller]

@LoicLopesS2H Okay, so this just isn't going to be able to be "fixed" quickly. W/o getting too deep in the weeds, Marten's LINQ provider currently has a somewhat intentional limitation that it can only gracefully handle collection sub queries at one level deep of &&/|| nesting, and your query exceeds that.

I think your immediate workaround might be to query things one thing at a time on only one level of && or || nesting. And it's only the sub-collection querying where this is a problem. You might have to do a little bit of filtering in memory to get going faster

[LoicLopesS2H]

Thank you for the answer. We'll circumvent the issue for now, as you suggest.

[nkosi23]

Keep in mind that for very specific needs you can also bypass marten and define a view directly in the DB that you can then query from marten in a strongly typed manner: https://martendb.io/documents/querying/advanced-sql.html