How to query a child collection to a nested collection using NHibernate?

0
votes

Given the following structure:

public class Contract
{
  virtual int Id {get;set;}
  virtual IList<Course> Courses {get;set;}
}

public class Course
{
  virtual int Id {get;set;}
  virtual IList<Schedule> Schedules {get;set;}  
}

public class Schedule
{
  virtual int Id {get;set;}
  virtual DateTime Start {get;set;}
  virtual DateTime End {get;set;}
}

I need to find if a given Contract has any Schedule (note how this goes through the Course relation) matching any of my new collection of Schedule objects from all the contracts in the database.

Edit:

My main problem is figuring it out a way (if possible and plausible) of doing the query against a collection of schedules, not just a scalar DateTime. This way, I figure I'd avoid doing an individual query for each Schedule instance. E.g., the structure would be something like: 

Contract contract = new  Contract 
{ 
  Courses = new List<Course>() 
  { 
    { 
      new List<Schedule>() 
      { 
        {new Schedule { Start = new DateTime(2011,01,01), End = new 
DateTime(2011,01,31) } }, 
        {new Schedule { Start = new DateTime(2011,02,01), End = new 
DateTime(2011,02,27) } }, 
        {new Schedule { Start = new DateTime(2011,03,01), End = new 
DateTime(2011,03,15) } } 
      } 
    }, 
    { 
      new List<Schedule>() 
      { 
        {new Schedule { Start = new DateTime(2010,12,12), End = new 
DateTime(2010,12,31) } } 
      } 
    } 
  } 
};

Do you think there's a way to query them all at once? Is it better to just do a .NET foreach loop and query individually?

Thanks in advance.

2
.netnhibernate
One more question: when you get the results, do you need to differentiate the list of contracts based on what schedule matched with them, or is a flat list enough? The latter is relatively easy, the former probably requires individual queries. - Rytmis
In the end, I'm only interested to know if there are conflicting schedules (i.e. a boolean return value). When the user creates a new schedule for a given course, I just need to know if there are preexisting schedules which conflict in date. - rebelliard
I'm sorry if I'm dense, but that sounds like the user is creating a single schedule which provides the criteria -- start and end date -- so scalar parameters should still do the trick. - Rytmis
That said, if you want to check multiple schedules at once, you can probably do that too, if you're willing to allow for persisting the schedules before they're validated. - Rytmis
See my own answer. Thanks. :) - rebelliard

2 Answers

0
votes

I'm not sure how this maps to LINQ to NHibernate, but the HQL version would be something to the tune of:

FROM Contract cn WHERE EXISTS 
    (FROM cn.Courses cc WHERE EXISTS 
        (FROM cc.Schedules s WHERE s.Start < '2011/01/01'))

Assuming a "Contracts" collection, the LINQ-y solution would probably look something like:

Contracts.Where(cn => cn.Courses.Any(cc => cc.Schedules.Any(s => s.Start < ...)));

But I have no idea if the LINQ provider translates that correctly.

0
votes

I solved it by going through each one recursively. I don't mind much the potential for almost N+1 queries because my data will never query more than 5 times (tops). I still give the answer to Rytmis because his HQL gave me the idea of using Any.

foreach (Course course in contract.Courses)
{
    foreach (Schedule entry in course.Schedules)
    {
        Schedule schedule = entry;
        var query = from  c in Session.Query<Course>()
                    where c.Schedules.Any(x =>
                                              x.Day == schedule.Day &&
                                              (
                                                (x.EndDate < schedule.EndDate && x.StartDate > schedule.StartDate) ||
                                                (x.EndDate == schedule.EndDate && x.StartDate > schedule.StartDate)
                                                // about 10 more of these
                                              )
                                          )
                     select c;

        var conflicting = query.FirstOrDefault();
        if (conflicting != null)
        {
            DoStuff()
        }
    }
}