RavenDB query in Datetime with value at collection offset

Question

I am trying to query RavenDB in Datetime, which is offset by an entry in the collection. As shown below, I have an AppointmentReminder object that contains a lot of AppointmentReminderJobs. I would like to ask for the AppointmentReminders where the AppointmentReminderJob should 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 try to get a list of AppointmentReminders for which current tasks are being performed (I know that the Where clause is not complete, but I tried to simplify it without any 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); } 

The call above gives the answer:

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

I am trying to tune the index as follows:

 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 query and get the 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 will be that my map / index can definitely lead to multiple entries of the same document identifier (destination reminder), but my result list only contains 1 instance of the document. I am happy with the way this works, I’m just not sure if I have to cut back or do something else in my code or just let Raven handle it, as if it looks like what it does?

Answer 1

You cannot create such a request. This will require RavenDB to perform calculations during the request, and this is not valid. RavenDB allows you to query only the data in the index.

What you can do is set up a calculation in the index and then query about it.