Question

There is a very nice pattern to prefetch a tree of objects without having a huge join result set of mostly redundant data:

select b.id as id, 0 as tag ... from blogs b where b.ID = 42
union all select p.blogid as id, 1 as tag, ... from posts p where p.BlogID = 42
union all select p.blogid as blogid, 2 as tag, ... from comments c join posts on ... where p.BlogID = 42
order by blogid, postid, commentid, tag

This would select blogs, their posts and comments in one go. It scales to many tables and big row counts easily because duplication is avoided entirely. It avoids any Cartesian products but requires many columns and tends to recompute parts of the query (in this example the join from blogs to posts will be included twice).

Does DO4 support such a thing or do you have any plans for this?

This thread was imported from our support forum. The original discussion may contain more detailed answer.

Answer 1

No, DO doesn't support exactly this feature. But it offers future queries and subquery batching allowing to achieve the same goal - likely, more efficiently:

var blog = Query.ExecuteFutureScalar(() => 
  (from blog in Query.All<Blog>() where blog.Id==blogId select blog).SingleOrDefault());
var posts = Query.ExecuteFuture(() => 
  from post in Query.All<Post>() where post.Blog.Id==blogId select post);
var comments = Query.ExecuteFuture(() => 
  from comment in Query.All<Comment>() where comment.Post.Blog.Id==blogId select comment);
if (blog.Value!=null) {
  // 3 queries were executed as single batch;
  // all 3 results are already available here
}

Alternative with subqueries:

var query = 
  from blog in Query.All<Blog>()
  where blog.Id==blogId
  select new {
    Blog = blog,
    Posts = blog.Posts,
    Comments = Query.All<Comment>().Where(c => c.Post.Blog.Id==blogId)
  };

foreach (var item in query) {
  // 2 batches (1+2 queries) were executed, 
  // all results are already available.
  ...
}

Subscription:

Related questions