The compiled query feature in Marten and why it rocks.

The “compiled query” feature is a brand new addition to Marten (as of v0.8.9), and one we think will have some very positive impact on the performance of our systems. I’m also hopeful that using this feature will make some of our internal code easier to read and understand. Down the line, the combination of compiled queries with the batch querying support should be the foundation of a decent dynamic, aggregated query mechanism to support our React/Redux based client architectures (with a server side implementation of Falcor maybe?). Big thanks and a shoutout to Corey Kaylor for adding this feature.

Linq is easily one of the most popular features in .Net and arguably the one thing that other platforms strive to copy. We generally like being able to express document queries in compiler-safe manner, but there is a non-trivial cost in parsing the resulting Expression trees and then using plenty of string concatenation to build up the matching SQL query. Fortunately, as of v0.8.10, Marten supports the concept of a Compiled Query that you can use to reuse the SQL template for a given Linq query and bypass the performance cost of continuously parsing Linq expressions.

All compiled queries are classes that implement the ICompiledQuery<TDoc, TResult> interface shown below:

    public interface ICompiledQuery<TDoc, TOut>
        Expression<Func<IQueryable<TDoc>, TOut>> QueryIs();

In its simplest usage, let’s say that we want to find the first user document with a certain first name. That class would look like this:

public class FindByFirstName : ICompiledQuery<User, User>
    public string FirstName { get; set; }

    public Expression<Func<IQueryable<User>, User>> QueryIs()
        return q => q.FirstOrDefault(x => x.FirstName == FirstName);

So a couple things to note in the class above:

  1. The QueryIs() method returns an Expression representing a Linq query
  2. FindByFirstName has a property (it could also be just a public field) called FirstName that is used to express the filter of the query

To use the FindByFirstName query, just use the code below:

            var justin = theSession.Query(new FindByFirstName {FirstName = "Justin"});

            var tamba = await theSession.QueryAsync(new FindByFirstName {FirstName = "Tamba"});

Or to use it as part of a batched query, this syntax:

var batch = theSession.CreateBatchQuery();

var justin = batch.Query(new FindByFirstName {FirstName = "Justin"});
var tamba = batch.Query(new FindByFirstName {FirstName = "Tamba"});

await batch.Execute();

(await justin).Id.ShouldBe(user1.Id);
(await tamba).Id.ShouldBe(user2.Id);

How does it work?

The first time that Marten encounters a new type of ICompiledQuery, it executes the QueryIs() method and:

  1. Parses the Expression just to find which property getters or fields are used within the expression as input parameters
  2. Parses the Expression with our standard Linq support and to create a template database command and the internal query handler
  3. Builds up an object with compiled Func’s that “knows” how to read a query model object and set the command parameters for the query
  4. Caches the resulting “plan” for how to execute a compiled query

On subsequent usages, Marten will just reuse the existing SQL command and remembered handlers to execute the query.

What is supported?

To the best of our knowledge and testing, you may use any Linq feature that Marten supports within a compiled query. So any combination of:

  • Select() transforms
  • First/FirstOrDefault()
  • Single/SingleOrDefault()
  • Where()
  • OrderBy/OrderByDescending etc.
  • Count()
  • Any()

At this point (v0.9), the only limitations are:

  1. You cannot yet incorporate the Include’s feature with compiled queries, but there is an open GitHub issue you can use to track progress on adding this feature.
  2. You cannot use the Linq ToArray() or ToList() operators. See the next section for an explanation of how to query for multiple results

Querying for multiple results

To query for multiple results, you need to just return the raw IQueryable<T> as IEnumerable<T> as the result type. You cannot use the ToArray() or ToList() operators (it’ll throw exceptions from the Relinq library if you try). As a convenience mechanism, Marten supplies these helper interfaces:

If you are selecting the whole document without any kind of Select() transform, you can use this interface:

    public interface ICompiledListQuery<TDoc> : ICompiledListQuery<TDoc, TDoc>

A sample usage of this type of query is shown below:

    public class UsersByFirstName : ICompiledListQuery<User>
        public static int Count;
        public string FirstName { get; set; }

        public Expression<Func<IQueryable<User>, IEnumerable<User>>> QueryIs()
            // Ignore this line, it's from a unit test;)
            return query => query.Where(x => x.FirstName == FirstName);

If you do want to use a Select() transform, use this interface:

    public interface ICompiledListQuery<TDoc, TOut> : ICompiledQuery<TDoc, IEnumerable<TOut>>

A sample usage of this type of query is shown below:

    public class UserNamesForFirstName : ICompiledListQuery<User, string>
        public Expression<Func<IQueryable<User>, IEnumerable<string>>> QueryIs()
            return q => q
                .Where(x => x.FirstName == FirstName)
                .Select(x => x.UserName);

        public string FirstName { get; set; }

Querying for a single document

Finally, if you are querying for a single document with no transformation, you can use this interface as a convenience:

    public interface ICompiledQuery<TDoc> : ICompiledQuery<TDoc, TDoc>

And an example:

    public class FindUserByAllTheThings : ICompiledQuery<User>
        public string Username { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public Expression<Func<IQueryable<User>, User>> QueryIs()
            return query =>
                    query.Where(x => x.FirstName == FirstName && Username == x.UserName)
                        .Where(x => x.LastName == LastName)







9 thoughts on “The compiled query feature in Marten and why it rocks.

  1. Are there any plans to make compiled queries “embeddable” within larger queries (I’m not sure about the right term here)?

    For example, let’s say I needed to execute a query that could return the User given a FirstName, but the FirstName is actually found by looking at the FirstName column from some other table like Account for which I have an ID. So I want to go from Account.Id -> Account.FirstName -> User.

    Now let’s say I also have a table, Purchases, and I have a Purchase.ID and need to do a similar two-stage lookup. I don’t want to re-implement the FirstName -> User logic, but I also don’t want to have to submit two queries (one to get the FirstName from the Purchase table or Account table given an ID, and the next to lookup the User given the FirstName). What I want to do is “embed” the FirstName -> User lookup logic directly within my larger query so I’m only submitting one query to the DB.

    This is something I’ve struggled with in EF and have used a variety of methods to overcome, but it’s never very elegant and usually involves some sort of Expression Tree trickery. If Marten had a built-in mechanism for something like this it would be huge…

    (I should probably check the issues list, but figured I’d just ask here since it’s related to the post and someone else might be interested in the same thing while reading it).

    1. @Dave,

      So we’ve already got the “Include()” functionality:, but that retrieves a whole other document. I hadn’t thought about letting you do transformations on the included document as part of the fetch, but it’s technically feasible. Worst case scenario, you can also use batched querying too:

      – Jeremy

      1. Thanks! The batched queries looks pretty close. Even if it’s not a single query, getting all the data in one request may typically be good enough. Guess it’s time to give Marten a spin 🙂

  2. The syntax you’re setting up for this is really really strange. I mean i get it, but I feel like there has to be a better way to define them. I guess some of it comes down to how much you want to pay in expression tree hell to make it prettier.

    What happens if people put logic inside the QueryIs method? Like if(fullmoon) return expr1 else return expr2. At the least if QueryIs is a property or field that it might help reduce people doing weird things

    1. I’m not sure that I’m buying any of this line of thinking. It’s just a Linq expression with the same abilities and restrictions as always, and if you can use Linq normally, you can use it in this context too.

      “What happens if people put logic inside the QueryIs method?” — if people do complicated or hurtful things with the tool, it’ll be hard? Yeah, sure. It’s a sharp tool.

      I dare you to go look at EF’s version of compiled queries and come back and tell me you still don’t like this;-)

      1. I don’t like ORMs at all anymore so that bar is already set very low.

        Is the code full invoked at run time that if a dev did put logic inside it that would actually behave properly? Or would it result in a run time exception along the lines of “can’t parse YourCustomMethod()”?

        With your direct example where the fields include FirstName and LastName, how would you handle the search behaving differently for whether first or last is specified vs first and last are specified? Would you expect

        if(first != null)
        query = query.where(FirstName)
        if((last != null)
        query = query.where(LastName)

        Or would you expect multiple isolated compiled queries?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s