RavenDB index for 2 nested many-to-many relationship entities

Question

I have the following entity collections in RavenDB:

public class Provider
{
    public string ProviderId {get; set;}
    public string ProviderName {get; set;}
    public string[] CategoryIds{get; set;}
}

public class Category
{
    public string CategoryId {get; set;}
    public string CategoryName {get; set;}
    public string[] ServiceIds {get; set;}
}

public class Service
{
    public string ServiceId {get; set;}
    public string ServiceName {get; set;}
    public string ServiceCode {get; set;}
}

This is a 2 level many-to-many relationships. Now I need to create index(es) to flat the structure so i search the results.

1. Search providers that can provider a certain service
2. Search categories that contain a certain service
3. Search services that a provider can provide

My Index result model should like this:

public class ProviderCategoryService
{
    public string ProviderId {get; set;}
    public string ProviderName {get; set;}
    public string CategoryId {get; set;}
    public string CategoryName {get; set;}
    public string ServiceId {get; set;}
    public string ServiceName {get; set;}
    public string ServiceCode {get; set;}
}

Can I fulfill the above functions just in one index or do i need to create multiple indexes?

*EDIT: use group by multiple field then transformer. *

I come up this index.

public class ProviderCategoryServiceSearch:AbstractMultiMapIndexCreationTask<IndexResult>
{
    public ProviderCategoryServiceSearch()
    {
        AddMap<Provider>(providers => from p in providers from c in p.CategoryIds
            select new {
                ProviderId = p.ProviderId,
                ProviderName = p.ProviderName,
                CategoryId = c,           
                CategoryName = (string)null,
                ServiceId = (string)null,
                ServiceName = (string)null,
                ServiceCode = (string)null,
        });

        AddMap<Category>(categories => from c in categories from s in ServiceIds
            select new {
                ProviderId = (string)null,
                ProviderName = (string)null,
                CategoryId = c.CategoryId,           
                CategoryName = c.CategoryName,
                ServiceId = s,
                ServiceName = (string)null,
                ServiceCode = (string)null,
        });

        Reduce = results => from r in results
            group r by new {r.CategoryId, r.ServiceId} into g
            from record in g
            select new {
                ProviderId = g.Select(x => x.ProviderId).FirstOrDefault(x => x != null),
                ProviderName = g.Select(x => x.ProviderName).FirstOrDefault(x => x != null),
                CategoryId = g.Key.CategoryId,           
                CategoryName = g.Select(x => x.CategoryName).FirstOrDefault(x => x != null),
                ServiceId = g.Key.ServiceId,
                ServiceName = (string)null,
                ServiceCode = (string)null,
             };
    }


    public class MyTransformer : AbstractTransformerCreationTask<IndexResult>
    {
        public MyTransformer()
        {
             TransformResults = results => from r in results
                 let s= LoadDocument<Service>(r.ServiceId)
                 select new {
                     ProviderId = r.ProviderId,
                    ProviderName = r.ProviderName,
                    CategoryId = r.CategoryId,           
                    CategoryName = r.CategoryName,
                    ServiceId = r.ServiceId,
                    ServiceName = s.ServiceName,
                    ServiceCode = s.ServiceCode,
            };
        }
    }
}

Any problem with this implementation?

Answer 1

That approach is ok. It was the way things were done in Raven 1.0 . However, since the introduction of LoadDocument in 2.0 and 2.5, it has become mostly unnecessary. Read more here. You will find that you don't need multimap or reduce when you can load a related document into your index map.