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

select 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.

asked Jul 31 '10 at 18:15

tobi's gravatar image


edited Sep 03 '10 at 15:55

Alex%20Yakunin's gravatar image

Alex Yakunin

Sorry, I just noticed that originally I answered on completely different question.

I.e. I misunderstood it.

(Sep 03 '10 at 16:11) Alex Yakunin Alex%20Yakunin's gravatar image

One Answer:

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.

answered Sep 03 '10 at 16:10

Alex%20Yakunin's gravatar image

Alex Yakunin

edited Sep 03 '10 at 16:10

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA