Mixing Raw SQL with Linq

Combine your Linq queries with raw SQL using the MatchesSql(sql) method like so:

[Fact]
public void query_with_matches_sql()
{
    using var session = theStore.LightweightSession();
    var u = new User { FirstName = "Eric", LastName = "Smith" };
    session.Store(u);
    session.SaveChanges();

    var user = session.Query<User>().Where(x => x.MatchesSql("data->> 'FirstName' = ?", "Eric")).Single();
    user.LastName.ShouldBe("Smith");
    user.Id.ShouldBe(u.Id);
}

^{snippet source | anchor}

But, if you want to take advantage of the more recent and very powerful JSONPath style querying, use this flavor of the same functionality that behaves exactly the same, but uses the '^' character for parameter placeholders to disambiguate from the '?' character that is widely used in JSONPath expressions:

var results2 = await theSession
    .Query<Target>().Where(x => x.MatchesJsonPath("d.data @? '$ ? (@.Children[*] == null || @.Children[*].size() == 0)'"))
    .ToListAsync();

^{snippet source | anchor}