Querying with Postgresql SQL Edit on GitHub

It's not too hard to imagine a scenario where the Linq querying support is either inadequate or you just want full control over the SQL for the purpose of optimizing a query. For that reason, Marten supports the IQuerySession/IDocumentSession.Query<T>(sql) and the MatchesSql(sql) methods that allow you to supply the SQL yourself.

In its easiest form, you just supply the SQL to the right of the FROM clause (select data from [table] [your code]) as in this sample below.

[Fact]
public void query_for_single_document()
{
    using (var session = theStore.OpenSession())
    {
        var u = new User { FirstName = "Jeremy", LastName = "Miller" };
        session.Store(u);
        session.SaveChanges();

        var user = session.Query<User>("where data ->> 'FirstName' = 'Jeremy'").Single();
        user.LastName.ShouldBe("Miller");
        user.Id.ShouldBe(u.Id);
    }
}

The actual JSONB data will always be a field called "data" in the database. If Marten does not spot a "SELECT" in the sql, it will fill in the "select data from mt_doc_type" SELECT and FROM clauses of the sql query for you.

To completely specify the sql, you'll need to know the table name matching your document type. By default, it'll be "mt_doc_[name of the class]."

var user =
    session.Query<User>("select data from mt_doc_user where data ->> 'FirstName' = 'Jeremy'")
           .Single();

The Query<T>(sql) mechanism will also allow you to use parameterized sql like so:

var user =
    session.Query<User>("where data ->> 'FirstName' = ? and data ->> 'LastName' = ?", "Jeremy",
               "Miller")
           .Single();

If you want to combine other Linq operations with your sql, you can use the MatchesSql(sql) method inside of your Linq query like so:

[Fact]
public void query_with_matches_sql()
{
    using (var session = theStore.OpenSession())
    {
        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);
    }
}

The best resource for this topic might just be the unit tests.

Asynchronous Queries

You can also query asynchronously with user supplied SQL:

var users =
    await
        session.QueryAsync<User>(
                   "select data from mt_doc_user where data ->> 'FirstName' = 'Jeremy'")
               .ConfigureAwait(false);
var user = users.Single();

Non-generic Overloads

The SQL queries described above can also be performed through the non-generic IQuerySession extensions, which allow for providing the document type during runtime. The sample below demonstrates this feature together with the C# dynamic type.

dynamic userFromDb = session.Query(user.GetType(), "where id = ?", user.Id).FirstOrDefault();
dynamic companyFromDb = (await session.QueryAsync(typeof(Company), "where id = ?", CancellationToken.None, company.Id)).FirstOrDefault();

Named Parameter Queries

Some of the Postgresql operators include the "?" character that Marten normally uses to denote an input parameter in user supplied queries. To solve that conflict, Marten 1.2 introduces support for named parameters in the user supplied queries:

[Fact]
public void query_by_two_named_parameters()
{
    using (var session = theStore.OpenSession())
    {
        session.Store(new User { FirstName = "Jeremy", LastName = "Miller" });
        session.Store(new User { FirstName = "Lindsey", LastName = "Miller" });
        session.Store(new User { FirstName = "Max", LastName = "Miller" });
        session.Store(new User { FirstName = "Frank", LastName = "Zombo" });
        session.SaveChanges();
        var user =
            session.Query<User>("where data ->> 'FirstName' = :FirstName and data ->> 'LastName' = :LastName", new { FirstName = "Jeremy", LastName = "Miller" })
                   .Single();

        SpecificationExtensions.ShouldNotBeNull(user);
    }
}