Querying with Postgresql SQL ​

The Marten project strives to make the Linq provider robust and performant, but if there's ever a time when the Linq support is insufficient, you can drop down to using raw SQL to query documents in Marten.

Here's the simplest possible usage to query for User documents with a WHERE clause:

var millers = session
    .Query<User>("where data ->> 'LastName' = 'Miller'");

^{snippet source | anchor}

Or with parameterized SQL:

var millers = session
    .Query<User>("where data ->> 'LastName' = ?", "Miller");

^{snippet source | anchor}

And finally asynchronously:

var millers = await session
    .QueryAsync<User>("where data ->> 'LastName' = ?", "Miller");

^{snippet source | anchor}

All of the samples so far are selecting the whole User document and merely supplying a SQL WHERE clause, but you can also invoke scalar functions or SQL transforms against a document body, but in that case you will need to supply the full SQL statement like this:

var sumResults = await session
    .QueryAsync<int>("select count(*) from mt_doc_target");

^{snippet source | anchor}

When querying single JSONB properties into a primitive/value type, you'll need to cast the value to the respective postgres type:

var times = await session.QueryAsync<DateTimeOffset>(
    "SELECT (data ->> 'ModifiedAt')::timestamptz from mt_doc_user");

^{snippet source | anchor}

The basic rules for how Marten handles user-supplied queries are:

• The T argument to Query<T>()/QueryAsync<T>() denotes the return value of each item
• If the T is a npgsql mapped type like a .Net int or Guid, the data is handled by reading the first field of the returned data
• If the T is not a mapped type, Marten will try to read the first field with the JSON serializer for the current DocumentStore
• If the SQL starts with the SELECT keyword (and it's not case sensitive), the SQL supplied is used verbatim
• If the supplied SQL does not start with a SELECT keyword, Marten assumes that the T is a document type and queries that document table with select data from [the document table name] [user supplied where clause]
• You can omit the WHERE keyword and Marten will add that automatically if it's missing
• You can also use SQL ORDER BY clauses