Querying Documents with Linq Edit on GitHub

Marten uses the Relinq library to support a subset of the normal Linq operators. Linq queries are done with Marten using the IQuerySession.Query<T>() or IDocumentSession.Query<T>() method to return an IQueryable for the document type T.

IMartenQueryable<T> Query<T>();

To query for all documents of a type - not that you would do this very often outside of testing - use the Query<T>() method like this:

public void get_all_documents_of_a_type(IDocumentSession session)
{
    // Calling ToArray() just forces the query to be executed
    var targets = session.Query<Target>().ToArray();
}

Basic Operators

Since you usually don't want to pull down the entire database at one time, Marten supports these basic operators in Linq searches:

public void basic_operators(IDocumentSession session)
{
    // Field equals a value
    session.Query<Target>().Where(x => x.Number == 5);

    // Field does not equal a value
    session.Query<Target>().Where(x => x.Number != 5);

    // Field compared to values
    session.Query<Target>().Where(x => x.Number > 5);
    session.Query<Target>().Where(x => x.Number >= 5);
    session.Query<Target>().Where(x => x.Number < 5);
    session.Query<Target>().Where(x => x.Number <= 5);
}

And and Or Queries

Right now, Marten supports both and and or queries with Linq:

public void and_or(IDocumentSession session)
{
    // AND queries
    session.Query<Target>().Where(x => x.Number > 0 && x.Number <= 5);

    // OR queries
    session.Query<Target>().Where(x => x.Number == 5 || x.Date == DateTime.Today);
}

Searching within Child Collections

As of v0.7, Marten supports simple Any() queries within child collections, but only for checking equality of members of the child collection elements (this feature uses the Postgresql JSONB containment operator to compose the underlying SQL).

Marten will also allow you to use the Contains method to search within arrays or lists of simple elements like strings.

The following code sample demonstrates the supported Linq patterns for collection searching:

public class ClassWithChildCollections
{
    public Guid Id;

    public IList<User> Users = new List<User>();
    public Company[] Companies = new Company[0];

    public string[] Names;
    public IList<string> NameList;
    public List<string> NameList2;
}

public void searching(IDocumentStore store)
{
    using (var session = store.QuerySession())
    {
        var searchNames = new string[] { "Ben", "Luke" };

        session.Query<ClassWithChildCollections>()
            // Where collections of deep objects
            .Where(x => x.Companies.Any(_ => _.Name == "Jeremy"))

            // Where for Contains() on array of simple types
            .Where(x => x.Names.Contains("Corey"))

            // Where for Contains() on List<T> of simple types
            .Where(x => x.NameList.Contains("Phillip"))

            // Where for Contains() on IList<T> of simple types
            .Where(x => x.NameList2.Contains("Jens"))

            // Where for Any(element == value) on simple types
            .Where(x => x.Names.Any(_ => _ == "Phillip"))

            // The Contains() operator on subqueries within Any() searches
            // only supports constant array of String or Guid expressions.
            // Both the property being searched (Names) and the values
            // being compared (searchNames) need to be arrays.
            .Where(x => x.Names.Any(_ => searchNames.Contains(_)));
    }
}

You can search on equality of multiple fields or properties within the child collection using the && operator:

var results = theSession
    .Query<Target>()
    .Where(x => x.Children.Any(_ => _.Number == 6 && _.Double == -1))
    .ToArray();

Finally, you can query for child collections that do not contain a value:

theSession.Query<DocWithArrays>().Count(x => !x.Strings.Contains("c"))
    .ShouldBe(2);

Searching for NULL Values

Regardless of your feelings about NULL, they do exist in databases and Marten allows you to search for documents that have (or don't have) null values:

public void query_by_nullable_type_nulls(IDocumentSession session)
{
    // You can use Nullable<T>.HasValue in Linq queries
    session.Query<Target>().Where(x => !x.NullableNumber.HasValue).ToArray();
    session.Query<Target>().Where(x => x.NullableNumber.HasValue).ToArray();

    // You can always search by field is NULL
    session.Query<Target>().Where(x => x.Inner == null);
}

Deep Queries

Marten's Linq support will allow you to make "deep" searches on properties of properties (or fields):

public void deep_queries(IDocumentSession session)
{
    session.Query<Target>().Where(x => x.Inner.Number == 3);
}

Searching on String Fields

Marten supports a subset of the common sub/string searches:

public void string_fields(IDocumentSession session)
{
    session.Query<Target>().Where(x => x.String.StartsWith("A"));
    session.Query<Target>().Where(x => x.String.EndsWith("Suffix"));

    session.Query<Target>().Where(x => x.String.Contains("something"));
    session.Query<Target>().Where(x => x.String.Equals("The same thing"));
}

Marten also supports case insensitive substring searches:

public void case_insensitive_string_fields(IDocumentSession session)
{
    session.Query<Target>().Where(x => x.String.StartsWith("A", StringComparison.OrdinalIgnoreCase));
    session.Query<Target>().Where(x => x.String.EndsWith("SuFfiX", StringComparison.OrdinalIgnoreCase));

    // using Marten.Util
    session.Query<Target>().Where(x => x.String.Contains("soMeThiNg", StringComparison.OrdinalIgnoreCase));

    session.Query<Target>().Where(x => x.String.Equals("ThE SaMe ThInG", StringComparison.OrdinalIgnoreCase));
}

A shorthand for case-insensitive string matching is provided through EqualsIgnoreCase (string extension method in Baseline):

query.Query<User>().Single(x => x.UserName.EqualsIgnoreCase("abc")).Id.ShouldBe(user1.Id);
query.Query<User>().Single(x => x.UserName.EqualsIgnoreCase("aBc")).Id.ShouldBe(user1.Id);

This defaults to String.Equals with StringComparison.CurrentCultureIgnoreCase as comparison type.

Count()

Marten supports the IQueryable.Count() method:

public void count_with_a_where_clause()
{
    // theSession is an IDocumentSession in this test
    theSession.Store(new Target { Number = 1 });
    theSession.Store(new Target { Number = 2 });
    theSession.Store(new Target { Number = 3 });
    theSession.Store(new Target { Number = 4 });
    theSession.Store(new Target { Number = 5 });
    theSession.Store(new Target { Number = 6 });
    theSession.SaveChanges();

    theSession.Query<Target>().Count(x => x.Number > 3).ShouldBe(3);
}

Min()

Marten supports the IQueryable.Min() method:

[Fact]
public void get_min()
{
    theSession.Store(new Target { Color = Colors.Blue, Number = 1 });
    theSession.Store(new Target { Color = Colors.Red, Number = 2 });
    theSession.Store(new Target { Color = Colors.Green, Number = -5 });
    theSession.Store(new Target { Color = Colors.Blue, Number = 42 });

    theSession.SaveChanges();
    var minNumber = theSession.Query<Target>().Min(t => t.Number);
    minNumber.ShouldBe(-5);
}

Max()

Marten supports the IQueryable.Max() method:

[Fact]
public void get_max()
{
    theSession.Store(new Target { Color = Colors.Blue, Number = 1 });
    theSession.Store(new Target { Color = Colors.Red, Number = 42 });
    theSession.Store(new Target { Color = Colors.Green, Number = 3 });
    theSession.Store(new Target { Color = Colors.Blue, Number = 4 });

    theSession.SaveChanges();
    var maxNumber = theSession.Query<Target>().Max(t => t.Number);
    maxNumber.ShouldBe(42);
}

Average()

Marten supports the IQueryable.Average() method:

[Fact]
public void get_average()
{
    theSession.Store(new Target { Color = Colors.Blue, Number = 1 });
    theSession.Store(new Target { Color = Colors.Red, Number = 2 });
    theSession.Store(new Target { Color = Colors.Green, Number = -5 });
    theSession.Store(new Target { Color = Colors.Blue, Number = 42 });

    theSession.SaveChanges();
    var average = theSession.Query<Target>().Average(t => t.Number);
    average.ShouldBe(10);
}

Sum()

Marten supports the IQueryable.Sum() method:

[Fact]
public void get_sum_of_integers()
{
    theSession.Store(new Target { Color = Colors.Blue, Number = 1 });
    theSession.Store(new Target { Color = Colors.Red, Number = 2 });
    theSession.Store(new Target { Color = Colors.Green, Number = 3 });
    theSession.Store(new Target { Color = Colors.Blue, Number = 4 });

    theSession.SaveChanges();
    theSession.Query<Target>().Sum(x => x.Number)
        .ShouldBe(10);
}

Ordering Results

Marten contains support for expressing ordering in both ascending and descending order in Linq queries:

public void order_by(IDocumentSession session)
{
    // Sort in ascending order
    session.Query<Target>().OrderBy(x => x.Date);

    // Sort in descending order
    session.Query<Target>().OrderByDescending(x => x.Date);

    // You can use multiple order by's
    session.Query<Target>().OrderBy(x => x.Date).ThenBy(x => x.Number);

    // Sort case-insensitively, Postgres is case-sensitive by default
    session.Query<Target>().OrderBy(x => x.String, StringComparer.OrdinalIgnoreCase);

    // If you want to sort by multiple fields case-insensitive, you need to supply StringComparer for each sort
    session.Query<Target>().OrderBy(x => x.String, StringComparer.OrdinalIgnoreCase).ThenBy(x => x.AnotherString, StringComparer.Ordinal);
}

Take() and Skip() for Paging

For simple paging, Marten supports the IQueryable.Take() and IQueryable.Skip() methods:

public void using_take_and_skip(IDocumentSession session)
{
    // gets records 11-20 from the database
    session.Query<Target>().Skip(10).Take(10).OrderBy(x => x.Number).ToArray();
}

Searching for a Single Document

Marten supports the IQueryable methods for returning only a single document at a time:

public void select_a_single_value(IDocumentSession session)
{
    // Single()/SingleOrDefault() will throw exceptions if more than
    // one result is returned from the database
    session.Query<Target>().Where(x => x.Number == 5).Single();
    session.Query<Target>().Where(x => x.Number == 5).SingleOrDefault();

    session.Query<Target>().Where(x => x.Number == 5).OrderBy(x => x.Date).First();
    session.Query<Target>().Where(x => x.Number == 5).OrderBy(x => x.Date).FirstOrDefault();

    session.Query<Target>().Where(x => x.Number == 5).OrderBy(x => x.Date).Last();
    session.Query<Target>().Where(x => x.Number == 5).OrderBy(x => x.Date).LastOrDefault();

    // Using the query inside of Single/Last/First is supported as well
    session.Query<Target>().Single(x => x.Number == 5);
}

Querying within Value IEnumerables

As of now, Marten allows you to do "contains" searches within Arrays, Lists & ILists of primitive values like string or numbers:

public void query_against_string_array()
{
    var doc1 = new DocWithArrays { Strings = new string[] { "a", "b", "c" } };
    var doc2 = new DocWithArrays { Strings = new string[] { "c", "d", "e" } };
    var doc3 = new DocWithArrays { Strings = new string[] { "d", "e", "f" } };

    theSession.Store(doc1);
    theSession.Store(doc2);
    theSession.Store(doc3);

    theSession.SaveChanges();

    theSession.Query<DocWithArrays>().Where(x => x.Strings.Contains("c")).ToArray()
        .Select(x => x.Id).ShouldHaveTheSameElementsAs(doc1.Id, doc2.Id);
}

Marten also allows you to query over IEnumerables using the Any method for equality (similar to Contains):

[Fact]
public void query_against_number_list_with_any()
{
    var doc1 = new DocWithLists { Numbers = new List<int> { 1, 2, 3 } };
    var doc2 = new DocWithLists { Numbers = new List<int> { 3, 4, 5 } };
    var doc3 = new DocWithLists { Numbers = new List<int> { 5, 6, 7 } };
    var doc4 = new DocWithLists { Numbers = new List<int> { } };

    theSession.Store(doc1, doc2, doc3, doc4);

    theSession.SaveChanges();

    theSession.Query<DocWithLists>().Where(x => x.Numbers.Any(_ => _ == 3)).ToArray()
        .Select(x => x.Id).ShouldHaveTheSameElementsAs(doc1.Id, doc2.Id);

    // Or without any predicate
    theSession.Query<DocWithLists>()
        .Count(x => x.Numbers.Any()).ShouldBe(3);
}

As of 1.2, you can also query against the Count() or Length of a child collection with the normal comparison operators (==, >, >=, etc.):

[Fact]
public void query_against_number_list_with_count_method()
{
    var doc1 = new DocWithLists { Numbers = new List<int> { 1, 2, 3 } };
    var doc2 = new DocWithLists { Numbers = new List<int> { 3, 4, 5 } };
    var doc3 = new DocWithLists { Numbers = new List<int> { 5, 6, 7, 8 } };

    theSession.Store(doc1);
    theSession.Store(doc2);
    theSession.Store(doc3);

    theSession.SaveChanges();

    theSession.Query<DocWithLists>()
        .Single(x => x.Numbers.Count() == 4).Id.ShouldBe(doc3.Id);
}

SelectMany()

Marten 1.2 adds the ability to use the SelectMany() operator to issue queries against child collections. You can use SelectMany() against primitive collections like so:

[Fact]
public void can_do_simple_select_many_against_simple_array()
{
    var product1 = new Product {Tags = new[] {"a", "b", "c"}};
    var product2 = new Product {Tags = new[] {"b", "c", "d"}};
    var product3 = new Product {Tags = new[] {"d", "e", "f"}};

    using (var session = theStore.OpenSession())
    {
        session.Store(product1, product2, product3);
        session.SaveChanges();
    }

    using (var query = theStore.QuerySession())
    {
        var distinct = query.Query<Product>().SelectMany(x => x.Tags).Distinct().ToList();

        distinct.OrderBy(x => x).ShouldHaveTheSameElementsAs("a", "b", "c", "d", "e", "f");

        var names = query.Query<Product>().SelectMany(x => x.Tags).ToList();
        names
            .Count().ShouldBe(9);
    }
}

Or against collections of child documents:

var results = query.Query<Target>()
    .SelectMany(x => x.Children)
    .Where(x => x.Flag)
    .OrderBy(x => x.Id)
    .Skip(20)
    .Take(15)
    .ToList();

A few notes on the SelectMany() usage and limitations:

• As of 1.2, you are only able to use a single SelectMany() operator in a single Linq query. That limitation will be removed in 1.3.
• You can use any other Linq operator that Marten supports after the SelectMany() in a Linq query, including the Stats() and Include() operators
• Take() and Skip() operators in a Linq query that contains a SelectMany() operator will always apply to the child collection database rather than the parent document regardless of the order in which the operators appear in the Linq query
• You cannot use SelectMany() with both a Distinct() and a Count() operator at this point.

Distinct()

New in Marten 1.2 is support for the Linq Distinct() operator:

[Fact]
public void get_distinct_string()
{
    theSession.Store(new Target {String = "one"});
    theSession.Store(new Target {String = "one"});
    theSession.Store(new Target {String = "two"});
    theSession.Store(new Target {String = "two"});
    theSession.Store(new Target {String = "three"});
    theSession.Store(new Target {String = "three"});

    theSession.SaveChanges();

    var queryable = theSession.Query<Target>().Select(x => x.String).Distinct();

    queryable.ToList().Count.ShouldBe(3);
}

Do note that the Distinct() keyword can be used with Select() transforms as well:

[Fact]
public void get_distinct_numbers()
{
    theSession.Store(new Target {Number = 1, Decimal = 1.0M});
    theSession.Store(new Target {Number = 1, Decimal = 2.0M});
    theSession.Store(new Target {Number = 1, Decimal = 2.0M});
    theSession.Store(new Target {Number = 2, Decimal = 1.0M});
    theSession.Store(new Target {Number = 2, Decimal = 2.0M});
    theSession.Store(new Target {Number = 2, Decimal = 1.0M});

    theSession.SaveChanges();

    var queryable = theSession.Query<Target>().Select(x => new
    {
        x.Number,
        x.Decimal
    }).Distinct();

    queryable.ToList().Count.ShouldBe(4);
}

Searching with Boolean Flags

Linq queries against boolean properties can use shorthand mechanisms in Where() clauses like so:

public void query_by_booleans(IDocumentSession session)
{
    // Flag is a boolean property.

    // Where Flag is true
    session.Query<Target>().Where(x => x.Flag).ToArray();
    // or
    session.Query<Target>().Where(x => x.Flag == true).ToArray();

    // Where Flag is false
    session.Query<Target>().Where(x => !x.Flag).ToArray();
    // or
    session.Query<Target>().Where(x => x.Flag == false).ToArray();
}

Use MatchesSql(sql) to search using raw SQL

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.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);
    }
}

IsOneOf

IsOneOf() extension can be used to query for documents having a field or property matching one of many supplied values:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var users = session.Query<SuperUser>()
    .Where(x => x.Role.IsOneOf("Admin", "Supervisor", "Director"));

To find one of for an array you can use this strategy:

// Finds all UserWithNicknames's whose nicknames matches either "Melinder" or "Norrland"

var nickNames = new[] {"Melinder", "Norrland"};

var users = session.Query<UserWithNicknames>()
    .Where(x => x.Nicknames.IsOneOf(nickNames));

To find one of for a list you can use this strategy:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var listOfRoles = new List<string> {"Admin", "Supervisor", "Director"};

var users = session.Query<SuperUser>()
    .Where(x => x.Role.IsOneOf(listOfRoles));

In

In() extension works exactly the same as IsOneOf(). It was introduced as syntactic sugar to ease RavenDB transition:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var users = session.Query<SuperUser>()
    .Where(x => x.Role.In("Admin", "Supervisor", "Director"));

To find one of for an array you can use this strategy:

// Finds all UserWithNicknames's whose nicknames matches either "Melinder" or "Norrland"

var nickNames = new[] {"Melinder", "Norrland"};

var users = session.Query<UserWithNicknames>()
    .Where(x => x.Nicknames.In(nickNames));

To find one of for a list you can use this strategy:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var listOfRoles = new List<string> {"Admin", "Supervisor", "Director"};

var users = session.Query<SuperUser>()
    .Where(x => x.Role.In(listOfRoles));

IsSupersetOf

// Finds all Posts whose Tags is superset of
// c#, json, or postgres
var posts = theSession.Query<Post>()
    .Where(x => x.Tags.IsSupersetOf("c#", "json", "postgres"));

IsSubsetOf

// Finds all Posts whose Tags is subset of
// c#, json, or postgres
var posts = theSession.Query<Post>()
    .Where(x => x.Tags.IsSubsetOf("c#", "json", "postgres"));

Modulo Queries

Marten has the ability to use the modulo operator in Linq queries:

[Fact]
public void use_modulo()
{
    theSession.Store(new Target{Color = Colors.Blue, Number = 1});
    theSession.Store(new Target{Color = Colors.Blue, Number = 2});
    theSession.Store(new Target{Color = Colors.Blue, Number = 3});
    theSession.Store(new Target{Color = Colors.Blue, Number = 4});
    theSession.Store(new Target{Color = Colors.Blue, Number = 5});
    theSession.Store(new Target{Color = Colors.Green, Number = 6});

    theSession.SaveChanges();

    theSession.Query<Target>().Where(x => x.Number % 2 == 0 && x.Color < Colors.Green).ToArray()
        .Select(x => x.Number)
        .ShouldHaveTheSameElementsAs(2, 4);
}

AnyTenant

Query data from all tenants using AnyTenant method.

// query data across all tenants
var actual = query.Query<Target>().Where(x => x.AnyTenant() && x.Flag)
                  .OrderBy(x => x.Id).Select(x => x.Id).ToArray();

TenantIsOneOf

Use TenantIsOneOf to query on a selected list of tenants.

// query data for a selected list of tenants
var actual = query.Query<Target>().Where(x => x.TenantIsOneOf("Green", "Red") && x.Flag)
                  .OrderBy(x => x.Id).Select(x => x.Id).ToArray();

Text Search

Postgres contains built in Text Search functions. They enable the possibility to do more sophisticated searching through text fields. Marten gives possibility to define Full Text Indexes and perform queries on them. Currently three types of full Text Search functions are supported:

• regular Search (to_tsquery)

var posts = session.Query<BlogPost>()
    .Where(x => x.Search("somefilter"))
    .ToList();

• plain text Search (plainto_tsquery)

var posts = session.Query<BlogPost>()
    .Where(x => x.PlainTextSearch("somefilter"))
    .ToList();

• phrase Search (phraseto_tsquery)

var posts = session.Query<BlogPost>()
    .Where(x => x.PhraseSearch("somefilter"))
    .ToList();

• web-style Search (websearch_to_tsquery, supported from Postgres 11+)

var posts = session.Query<BlogPost>()
    .Where(x => x.WebStyleSearch("somefilter"))
    .ToList();

All types of Text Searches can be combined with other Linq queries

var posts = session.Query<BlogPost>()
    .Where(x => x.Category == "LifeStyle")
    .Where(x => x.PhraseSearch("somefilter"))
    .ToList();

They allow also to specify language (regConfig) of the text search query (by default english is being used)

var posts = session.Query<BlogPost>()
    .Where(x => x.PhraseSearch("somefilter", "italian"))
    .ToList();

Supported Types

At this point, Marten's Linq support has been tested against these .Net types:

1. String
2. Int32 & Int64 (int and long)
3. Decimal (float)
4. DateTime and DateTimeOffset
5. Enum values
6. Nullable of all of the above types
7. Booleans