RavenDB workaround for nested LINQ expression

Question

I have simple set of objects stored in RavenDB:

public class Question
{
    public string Id { get; set; }
    public DateTime CreatedOn { get; set; }
    public ICollection<User> Supporters { get; set; }
    public ICollection<Answer> Answers { get; set; }
}

public class Answer 
{
    public string Id { get; set; }
    public bool IsOfficial { get; set; }
}

Now I want to query RavenDB to give me set of questions, ordered firstly by number of supporters, next by condition - if a question has any official answer, and in the and, by question creation date. So I've written a query:

var questions = DocumentSession.Query<Question>().AsQueryable(); 
questions = questions
                .OrderByDescending(x => x.Supporters.Count)
                .ThenByDescending(x => x.Answers.Any(a => a.IsOfficial)) //EDIT: source of exception
                .ThenByDescending(x => x.CreatedOn)
                .Take(15);                
var result = questions.ToList();

which throws an exception:

System.InvalidCastException: Unable to cast object of type 'System.Linq.Expressions.MethodCallExpressionN' to type 'System.Linq.Expressions.MemberExpression'

The query is logically correct and works, when I use linq-to-objects, and simply add .ToList() to first line:

var questions = DocumentSession.Query<Question>().Tolist().AsQueryable();
// next lines stay unchanged

I don't want to do it because of performance issues (this change forces all questions to be loaded from database into memory before filtering).

How to make this working without performance impact ? Maybe shell I define an index ? How it should looks like then ?

Answer 1

Based on Bear Alexander response, I've done this like that:

public class QuestionByAnyOfficial : AbstractIndexCreationTask<Question, QuestionByAnyOfficial.Result>
{
    public class Result
    {
        public string Id;
        public bool AnyOfficial;
        public int SupportersCount;
        public DateTime CreatedOn;
    }

    public QuestionByAnyOfficial()
    {
        Map = questions => from question in questions                               
                           select new
                              {
                                  Id = question.Id,                                              
                                  AnyOfficial = question.Answers.Any(a => a.IsOfficial),
                                  SupportersCount = question.Supporters.Count,
                                  CreatedOn = question.CreatedOn
                              };            
    }
}

var questionIds = DocumentSession.Query<QuestionByAnyOfficial.Result, QuestionByAnyOfficial>()
                       .OrderByDescending(x => x.SupportersCount)
                       .ThenByDescending(x => x.AnyOfficial)
                       .ThenByDescending(x => x.CreatedOn)
                       .Take(NumberOfQuestions)
                       .Select(x => x.Id);
var questions = DocumentSession.Load<Question>(questionIds);
var result = questions.ToList();

It works and I believe it is more efficient than my original version. If it can be done in any more elegant way, I'd appreciate any ideas. Regards.

Answer 2

A custom index for your purposes is basically going to be a recreation of your class with extra fields in it (and some logic to support it). It seems like you don't want to have to add more fields to your current class, are you okay with adding more classes to your project?

Here's an example:

public class Question_WithAnyOfficial: AbstractIndexCreationTask<Question>
{
    public class Question_WithAnyOfficial()
    {
        Map = questions => from question in questions
                           // New Anonymous Type
                           select new
                           {
                                Id = question.Id,
                                CreatedOn = question.CreatedOn,
                                Supporters = question.Supporters,
                                Answers = question.Answers,
                                AnyOfficial = question.Answers.Where(a => a.IsOfficial).Any()
                           };
    }
}

Then you can query this:

var questions = DocumentSession.Query<Question_WithAnyOfficial>()
                .OrderByDescending(x => x.Supporters.Count)
                .ThenByDescending(x => x.AnyOfficial)
                .ThenByDescending(x => x.CreatedOn)
                .Take(15)
                .ToList();         

Don't forget that you'll have to register the index when your app starts.

Answer 3

Raven can't support calculations like that inside the LINQ query, so this should work (problem clause removed):

var questions = DocumentSession.Query<Question>()
                .OrderByDescending(x => x.Supporters.Count)
                //.ThenByDescending(x => x.Answers.Any(a => a.IsOfficial))
                .ThenByDescending(x => x.CreatedOn)
                .Take(15);                
var result = questions.ToList();

If you want to include that logic, you need a field on your class called AreAllAnswersOfficial (or something similar). Then you can put that inside the clause:

var questions = DocumentSession.Query<Question>()
                .OrderByDescending(x => x.Supporters.Count)
                .ThenByDescending(x => x.AreAllAnswersOfficial)
                .ThenByDescending(x => x.CreatedOn)
                .Take(15);                
var result = questions.ToList();