Getting Min/Max date within RavenDB reduce result

Question

I have a multi map index and need to get the maximum date in the Reduce result.

When I try:

Reduce = results => from result in results
                    group result by result.Id into g
                    select new
                    {
                        Id = g.Key,
                        AccountId = g.Select(x => x.AccountId).Where(x => x != null).FirstOrDefault(),
                        Hostnames = g.SelectMany(x => x.Hostnames),
                        NextRenewalDate = g.Select(x => x.NextRenewalDate).Max(),
                        SubscriptionCount = g.Sum(x => x.SubscriptionCount)
                    };

The NextRenewalDate is always null.

However, I can do:

NextRenewalDate = g.Select(x => x.NextRenewalDate).FirstOrDefault,

And I do get a result.

I wonder if this is due to how RavenDB is interpreting the dates within the index. My Reduce Result class is below:

public class ReduceResult
{
    public string Id { get; set; }
    public string AccountId { get; set; }
    public string[] Hostnames { get; set; }
    public DateTime NextRenewalDate { get; set; }
    public int SubscriptionCount { get; set; }
}

Update

I've since been able to get the index working but only if I cast the DateTime to an ISO8601 string. My full index is below:

public class Account_Sites : AbstractMultiMapIndexCreationTask<Account_Sites.ReduceResult>
{
    public Account_Sites()
    {
        AddMap<CMS.Domain.Site>(sites => from site in sites
                                         from url in site.Urls
                                         select new
                                         {
                                             Id = site.Id,
                                             AccountId = (string)null,
                                             Hostnames = new[] { url.Hostname },
                                             SubscriptionDueDate = DateTime.MinValue,
                                             SubscriptionStartDate = DateTime.MinValue
                                         });

        AddMap<Domain.Account>(accounts => from account in accounts
                                           from siteId in account.Sites
                                           from subscription in account.Subscriptions
                                           where subscription != null
                                           select new
                                           {
                                               Id = siteId,
                                               AccountId = account.Id,
                                               Hostnames = new string[0],
                                               SubscriptionDueDate = subscription.NextRenewalDate,
                                               SubscriptionStartDate = subscription.StartDate
                                           });

        Reduce = results => from result in results
                            group result by result.Id into g
                            select new
                            {
                                Id = g.Key,
                                AccountId = g.Select(x => x.AccountId).Where(x => x != null).FirstOrDefault(),
                                Hostnames = g.SelectMany(x => x.Hostnames),
                                SubscriptionDueDate = g.Max(x => x.SubscriptionDueDate.ToString("o")), // ISO 8601 format e.g. 2012-10-22T12:51:03.0263843+00:00
                                SubscriptionStartDate = g.Max(x => x.SubscriptionStartDate.ToString("o"))
                            };
    }

    public class ReduceResult
    {
        public string Id { get; set; }
        public string AccountId { get; set; }
        public string[] Hostnames { get; set; }
        public DateTime SubscriptionDueDate { get; set; }
        public DateTime SubscriptionStartDate { get; set; }
    }
}

Update 2

I've posted a complete failing test below. Daniel's test does work but the following fails. Changing the TestSubscription.EndDate to DateTime.Now (instead of UtcNow) however does work. This is using build 1.2.2103-Unstable.

public class TestAccount
{
    public string Id { get; set; }
    public List<string> Sites { get; set; }
    public List<TestSubscription> Subscriptions { get; set; }

    public TestAccount()
    {
        Sites = new List<string>();
        Subscriptions = new List<TestSubscription>();
    }
}

public class TestSubscription
{
    public DateTime EndDate { get; set; }
}

public class TestSite
{
    public string Id { get; set; }
    public string Hostname { get; set; }
}

public class SitesWithSubscriptions : AbstractMultiMapIndexCreationTask<SitesWithSubscriptions.Result>
{
    public SitesWithSubscriptions()
    {
        AddMap<TestSite>(sites => from site in sites
                                  select new
                                  {
                                      SiteId = site.Id,
                                      Hostname = site.Hostname,
                                      SubscriptionEndDate = DateTime.MinValue
                                  });

        AddMap<TestAccount>(accounts => from account in accounts
                                        from siteId in account.Sites
                                        from subscription in account.Subscriptions
                                        select new
                                        {
                                            SiteId = siteId,
                                            Hostname = (string)null,
                                            SubscriptionEndDate = subscription.EndDate
                                        });

        Reduce = results => from result in results
                            group result by result.SiteId into g
                            select new
                            {
                                SiteId = g.Key,
                                Hostname = g.Select(x => x.Hostname).Where(x => x != null).FirstOrDefault(),
                                SubscriptionEndDate = g.Max(x => x.SubscriptionEndDate)
                            };
    }

    public class Result
    {
        public string SiteId { get; set; }
        public string Hostname { get; set; }
        public DateTime SubscriptionEndDate { get; set; }
    }
}

    public class SitesSpecs : RavenSpecs
{
    static IEnumerable<SitesWithSubscriptions.Result> results;

    Establish ctx = () =>
    {
        using (var session = Store.OpenSession())
        {
            var site = new TestSite { Hostname = "www.dev.com" };
            session.Store(site);

            var account = new TestAccount();
            account.Subscriptions.Add(new TestSubscription { EndDate = DateTime.UtcNow });
            account.Sites.Add(site.Id);
            session.Store(account);

            session.SaveChanges();
        }           
    };

    Because of = () =>
    {
        using (var session = Store.OpenSession())
        {
            results = session.Query<SitesWithSubscriptions.Result, SitesWithSubscriptions>()
                .Customize(x => x.WaitForNonStaleResults())
                .ToList();
        }
    };

    It Should_return_sites_with_subscription_end_dates = ()
        =>
    {
        results.Count().ShouldEqual(1);
        results.First().SubscriptionEndDate.Date.ShouldEqual(DateTime.UtcNow.Date);
    };
}

Answer 1

This turned out to be a bug in RavenDB and was fixed in build 2145. Full details at http://issues.hibernatingrhinos.com/issue/RavenDB-718

Answer 2

Here is a quick'n'dirty sample that shows how to do it:

Update: I've written this code to verify that RavenDB can handle DateTime as you requested. You don't need to convert it to a string in ISO,... I tried it in Build 960. If it doesn't work for you, please provide a failing test and let us know which build you are using.

public class Student
{
    public string Id { get; set; }
    public string Classroom { get; set; }
    public DateTime Birthday { get; set; }
}

public class LatestBirthdayInClassroom : AbstractIndexCreationTask<Student, LatestBirthdayInClassroom.Result>
{
    public class Result
    {
        public string Classroom { get; set; }
        public DateTime LatestBirthday { get; set; }
    }

    public LatestBirthdayInClassroom()
    {
        Map = students => from student in students
                       select new
                       {
                           Classroom = student.Classroom,
                           LatestBirthday = student.Birthday
                       };

        Reduce = results => from result in results
                            group result by result.Classroom into g
                            select new
                            {
                                Classroom = g.Key,
                                LatestBirthday = g.Select(x => x.LatestBirthday).Max()
                            };
    }
}

class Program
{
    static void Main(string[] args)
    {
        using (var store = new EmbeddableDocumentStore
        {
            RunInMemory = true
        }.Initialize())
        {
            using (var session = store.OpenSession())
            {
                session.Store(new Student { Classroom = "A", Birthday = new DateTime(2012, 2, 1) });
                session.Store(new Student { Classroom = "A", Birthday = new DateTime(2012, 5, 1) });
                session.Store(new Student { Classroom = "A", Birthday = new DateTime(2012, 3, 1) });
                session.Store(new Student { Classroom = "B", Birthday = new DateTime(2012, 8, 1) });
                session.Store(new Student { Classroom = "B", Birthday = new DateTime(2012, 11, 1) });
                session.Store(new Student { Classroom = "B", Birthday = new DateTime(2012, 9, 1) });
                session.SaveChanges();
            }

            new LatestBirthdayInClassroom().Execute(store);

            // wait for indexing
            while (store.DatabaseCommands.GetStatistics().StaleIndexes.Length > 0)
            {
                Thread.Sleep(100);
            }

            using (var session = store.OpenSession())
            {
                var results = session.Query<LatestBirthdayInClassroom.Result, LatestBirthdayInClassroom>()
                    .ToList();
            }
        }
    }
}