LoadDocument by query in RavenDB index

user_670860 0
Viewed 78

I have the following structure:

public class Order
{
    public string Id { get; set; }
    public string CustomerId { get; set; }
    public decimal Amount { get; set; }
    public DateTimeOffset CreatedDate { get; set; }
}

public class Customer
{
    public string Id { get; set; }
    public string Name { get; set; }
}

I want to export all customers (RavenDB Stream) with order turnover and last ordered date. I do already have an index (Customers_ByTurnover) which outputs this data (map=Orders, reduce by CustomerId). Although this does only list customers which have already ordered something.

I need an index for all Customers and load these details into each row. Here is the code I want to write (the Query method is pseudo and doesn't really exist):

public class Customers_ByOrders : AbstractIndexCreationTask<Customer, Customers_ByOrders.Result>
{
    public class Result
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public decimal Turnover { get; set; }
        public DateTimeOffset? LastOrderedDate { get; set; }
    }

    public Customers_ByOrders()
    {
        Map = items => items.Select(item => new Result()
        {
            Id = item.Id,
            Name = item.Name,
            Turnover = Query<Order>().Where(x => x.CustomerId == item.Id).Sum(x => x.Amount),
            LastOrderedDate = Query<Order>().Where(x => x.CustomerId == item.Id).Select(x => x.CreatedDate).OrderByDescending(x => x).FirstOrDefault()
        });
    }
}

How can I solve this issue?

ravendb
1 Answers

You cannot create a query inside an index, to get the desired info you will have to create a map-reduce index on the Orders collection, group by on CustomerId and in the reduce function apply Sum() on Amount field and order the LastOrderedDate. To get the Name name field you will have to use LoadDocument extension.

public class Customers_ByOrders : AbstractIndexCreationTask<Orders, Customers_ByOrders.Result>
{
    public class Result
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public decimal Turnover { get; set; }
        public DateTimeOffset? LastOrderedDate { get; set; }
    }

    public Customers_ByOrders()
    {
                Map = orders => from o in orders
                                   select new Result
                                   {
            Id = o.CustomerId,
            Turnover = o.Amount,
            LastOrderedDate = o.CreatedDate
        };
                        Reduce = results => from result in results
                                    group result by result.Id 
                                    into g 
                                    select new Result
                                    {
                                      Id = g.Key,
                                      Turnover = g.Sum(x => x.Turnover),
                                      LastOrderedDate = g.OrderByDescending(x => x.LastOrderedDate).Select(x => x.LastOrderedDate).FirstOrDefault()
                                    };
    }
}
user_113412610
Related