Including Related Documents
Include a Single Document
TIP
If you're interested, this functionality does not use SQL JOIN
clauses, and has not since the V4 release.
Marten supports the ability to run include queries that make a single database call in order to fetch a referenced document as well as the queried document. Suppose you are querying for a github Issue
that contains a property AssigneeId
, which references the Id of the User
assigned to the Issue. If you wish to fetch the User
as well in one trip to the database, you can use the .Include()
method like so:
[Fact]
public void simple_include_for_a_single_document()
{
var user = new User();
var issue = new Issue { AssigneeId = user.Id, Title = "Garage Door is busted" };
using var session = theStore.IdentitySession();
session.Store<object>(user, issue);
session.SaveChanges();
using var query = theStore.QuerySession();
query.Logger = new TestOutputMartenLogger(_output);
User included = null;
var issue2 = query
.Query<Issue>()
.Include<User>(x => included = x).On(x => x.AssigneeId)
.Single(x => x.Title == issue.Title);
included.ShouldNotBeNull();
included.Id.ShouldBe(user.Id);
issue2.ShouldNotBeNull();
}
The Include()
method takes an expression that will assign the fetched related document to a previously declared variable (included
in our case). The Include()
method should then be followed by the On()
method (named after sql LEFT JOIN ... ON ...
). The first parameter of On()
method takes an expression that specifies the document properties on which the join will be done (AssigneeId
in this case).
Marten will use the equivalent of a left join. This means that any Issue
with no corresponding User
(or no AssigneeId
) will still be fetched, just with no matching user.
Include Many Documents
If you wish to fetch a list of related documents, you can declare a List<User>
variable and pass it as the second parameter. The Include()
method should be appended with ToList()
or ToArray()
.
Instead of a List, you could also use a Dictionary with a key type corresponding to the Id type and a value type corresponding to the Document type:
[Fact]
public void include_to_dictionary()
{
var user1 = new User();
var user2 = new User();
var issue1 = new Issue { AssigneeId = user1.Id, Title = "Garage Door is busted" };
var issue2 = new Issue { AssigneeId = user2.Id, Title = "Garage Door is busted" };
var issue3 = new Issue { AssigneeId = user2.Id, Title = "Garage Door is busted" };
using var session = theStore.IdentitySession();
session.Store(user1, user2);
session.Store(issue1, issue2, issue3);
session.SaveChanges();
using var query = theStore.QuerySession();
var dict = new Dictionary<Guid, User>();
query.Query<Issue>().Include(dict).On(x => x.AssigneeId).ToArray();
dict.Count.ShouldBe(2);
dict.ContainsKey(user1.Id).ShouldBeTrue();
dict.ContainsKey(user2.Id).ShouldBeTrue();
}
Filtering included documents
As of Marten V7, you can also filter the included documents in case of large data sets by supplying an extra filter argument on the included document type (essentially a Where()
clause on just the included documents) like so:
[Fact]
public async Task filter_included_documents_to_lambda()
{
var list = new List<Target>();
var holders = await theSession.Query<TargetHolder>()
.Include(list).On(x => x.TargetId, t => t.Color == Colors.Blue)
.ToListAsync();
list.Select(x => x.Color).Distinct()
.Single().ShouldBe(Colors.Blue);
list.Count.ShouldBe(Data.Count(x => x.Color == Colors.Blue));
}
Include Multiple Document Types
WARNING
Marten can only filter the included documents, not sort them. You would have to apply ordering in memory if so desired.
Marten also allows you to chain multiple Include()
calls:
[Fact]
public void multiple_includes()
{
var assignee = new User{FirstName = "Assignee"};
var reporter = new User{FirstName = "Reporter"};
var issue1 = new Issue { AssigneeId = assignee.Id, ReporterId = reporter.Id, Title = "Garage Door is busted" };
using var session = theStore.IdentitySession();
session.Store(assignee, reporter);
session.Store(issue1);
session.SaveChanges();
using var query = theStore.QuerySession();
User assignee2 = null;
User reporter2 = null;
query.Logger = new TestOutputMartenLogger(_output);
query
.Query<Issue>()
.Include<User>(x => assignee2 = x).On(x => x.AssigneeId)
.Include<User>(x => reporter2 = x).On(x => x.ReporterId)
.Single()
.ShouldNotBeNull();
assignee2.Id.ShouldBe(assignee.Id);
reporter2.Id.ShouldBe(reporter.Id);
}
Mapping to documents on any property
By default, documents are included based on a value that maps to the related document's Id
/[Identity]
property. It is also possible to map related documents on any property of that document which allows for much more flexible joins.
[Fact]
public void include_using_custom_map()
{
var classroom = new Classroom(Id: Guid.NewGuid(), RoomCode: "Classroom-1A");
var user = new SchoolUser(Id: Guid.NewGuid(), Name: "Student #1", HomeRoom: "Classroom-1A");
using var session = theStore.IdentitySession();
session.Store<object>(classroom, user);
session.SaveChanges();
using var query = theStore.QuerySession();
Classroom? included = null;
var user2 = query
.Query<SchoolUser>()
.Include<Classroom>(c => included = c).On(u => u.HomeRoom, c => c.RoomCode)
.Single(u => u.Name == "Student #1");
included.ShouldNotBeNull();
included.Id.ShouldBe(classroom.Id);
user2.ShouldNotBeNull();
}
By joining on a value other than the document id, this opens up the possibility of one-to-many joins, with potentially many related documents matching the queried document. Using a list as described above will allow for all matching records to be returned. Alternatively you can also use a dictionary of lists, where the key is the Id type and the value is an IList
of a type corresponding to the Document type:
[Fact]
public void include_to_dictionary_list()
{
var class1 = new Classroom(Id: Guid.NewGuid(), RoomCode: "Classroom-1A");
var class2 = new Classroom(Id: Guid.NewGuid(), RoomCode: "Classroom-2B");
var user1 = new SchoolUser(Id: Guid.NewGuid(), Name: "Student #1", HomeRoom: "Classroom-1A");
var user2 = new SchoolUser(Id: Guid.NewGuid(), Name: "Student #2", HomeRoom: "Classroom-2B");
var user3 = new SchoolUser(Id: Guid.NewGuid(), Name: "Student #3", HomeRoom: "Classroom-2B");
using var session = theStore.IdentitySession();
session.Store(class1, class2);
session.Store(user1, user2, user3);
session.SaveChanges();
using var query = theStore.QuerySession();
var dict = new Dictionary<string, IList<SchoolUser>>();
var classes = query
.Query<Classroom>()
.Include(dict).On(c => c.RoomCode, u => u.HomeRoom)
.ToArray();
classes.Length.ShouldBe(2);
dict.Count.ShouldBe(2);
dict.ContainsKey(class1.RoomCode).ShouldBeTrue();
dict.ContainsKey(class2.RoomCode).ShouldBeTrue();
dict[class1.RoomCode].Count.ShouldBe(1);
dict[class2.RoomCode].Count.ShouldBe(2);
}
Asynchronous Support
Marten supports Include within an asynchronous context. The query will be run asynchronously when you append your query with the corresponding Async method, like:
ToListAsync()
SingleAsync()
And so on...
Marten also supports running an Include query within batched queries:
var batch = query.CreateBatchQuery();
var found = batch.Query<Issue>()
.Include<User>(x => included = x).On(x => x.AssigneeId)
.Where(x => x.Title == issue1.Title)
.Single();