RavenDB Query on Datetime with value in collection offset

Question

I am trying to query RavenDB on a Datetime which is being offset by a entry in a collection. As shown below, I have an AppointmentReminder object which contains many AppointmentReminderJobs. I'd like to query for AppointmentReminders where the AppointmentReminderJob is due to run.

My models are as follows:

public class AppointmentReminder
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public DateTime AppointmentDateTime { get; set; }
    public ReminderStatus ReminderStatus { get; set; }
    public List<AppointmentReminderJob> AppointmentReminderJobs { get; set; }
 }

public class AppointmentReminderJob
{
    public JobStatus JobStatus { get; set; }
    public int DaysPrior { get; set; }
}

My controller and attempt to retrieve a list of AppointmentReminders which have current jobs to run (I know this Where clause isn't complete, but I've tried to simplify it with no luck):

public ActionResult GetJobsQueuedListCurrent()
    {
        var jobsqueuedlist = RavenSession.Query<AppointmentReminder>()
            .Where(appointmentreminder => appointmentreminder.AppointmentReminderJobs.Any(x => appointmentreminder.AppointmentDateTime < DateTime.Now.AddDays(x.DaysPrior)))
            .OrderBy(appointmentreminder => appointmentreminder.AppointmentDateTime)
            .Take(20)
            .ToList();

        return View("List", jobsqueuedlist);

    }

Calling the above yields a response of:

variable 'x' of type 'ProjectName.Models.AppointmentReminderJob' referenced from scope '', but it is not defined

I am trying to set up an index like so:

public class JobsQueuedListCurrent : AbstractIndexCreationTask<AppointmentReminder, JobsQueuedListCurrent.IndexResult>
{
    public class IndexResult
    {
        public int Id { get; set; }
        public DateTime JobDateTime { get; set; }
    }

    public JobsQueuedListCurrent()
    {


        Map = appointmentreminders => from appointmentreminder in appointmentreminders
                                      from job in appointmentreminder.AppointmentReminderJobs
                                      select new 
                                      { 
                                          Id = appointmentreminder.Id, 
                                          JobDateTime = appointmentreminder.AppointmentDateTime.AddDays(job.DaysPrior)
                                      };
        Store(x => x.Id, FieldStorage.Yes);
        Store(x => x.JobDateTime, FieldStorage.Yes);
    }
}

Now, I'm querying and getting expected results using:

var jobsqueuedlist = RavenSession.Query<JobsQueuedListCurrent.IndexResult, JobsQueuedListCurrent>()
            .Where(x=>x.JobDateTime >= DateTime.Now)
            .As<AppointmentReminder>()
            .Take(20)
            .ToList();

        return View("List", jobsqueuedlist);

My last question regarding this would be, my map/index can definitely result in multiple entries of the same document id (appointmentreminder), but my resulting list contains only 1 instance of the document. I'm happy with the way that works, I'm just not sure if I should be performing a reduce or doing something else in my code or just let Raven handle it like it seems like it is doing?

Answer 1

You cannot create such a query. This would require RavenDB to perform computation during query, and that is not allowed. RavenDB only allows queries on the data in the index.

What you can do it setup the computation in the index, and then query on that.