RavenDB many to many and indexes

Question

Need a help with RavenDB.

In my web page I want to have such list:

• item1 category1
• item2 category2
• ...

and another one:

• category1, number of items
• category2, number of items
• ...

My data structures:

public class Item
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string CategoryId { get; set; }
}


public class Category
{
    public string Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

Index for the first list:

public class Item_WithCategory : AbstractIndexCreationTask<Item>
{
    public class Result
    {
        public string Name { get; set; }
        public string CategoryName { get; set; }
    }

    public Item_WithCategory()
    {
        Map = items => from item in items
              select new
                  {
                      Name = item.Name,
                      CategoryName = LoadDocument<Category>(item.CategoryId).Name
                  };
    }
}

Is this data structure suitable for my case, or will it be better to have Category instead of CategoryId in item structure?

Should I use my index or is there a better solution to take category name?

If my index is good, how to write a correct query? My current try:

    Item_WithCategory.Result[] all;
    using (var session = DocumentStoreHolder.Store.OpenSession())
    {
        all = session.Query<Item_WithCategory.Result, Item_WithCategory>().ToArray();
    }

but it throws exception stating that return type is item, not result. How to fix it?

Answer 1

You have a couple of options here. You could store both the CategoryId and the CategoryName on the Item entity. This will of course lead to duplicated data (if you still need to store the Category entity), but "storage is cheap" is a popular term these days.The downside of this is that you need to update each Item document of a given category if the category name changes to keep things consistent. A benefit is that you need to do less work to get your desired result.

If you store Category Name on the item as well you don't need a special index to handle the first list, just query on the Items and return what you need. For the second list you need to create a Map/Reduce index on the Item entity that groups on the category.

However, if you need to use the data structure you've given, there are a couple of ways of solving this. First, it's really not recommended to use a LoadDocument inside of an index definition, especially not in a select statement. This might affect indexing performance in a negative way.

Instead, just index the properties you need to query on (or use an auto index) and then use a Result Transformer to fetch information from related documents:

public class ItemCategoryTransformer : AbstractTransformerCreationTask<Item>
{
    public ItemCategoryTransformer()
    {
        TransformResults = results => from item in results
                                      let category = LoadDocument<Category>(item.CategoryId)
                                      select new ItemCategoryViewModel
                                      {
                                          Name = item.Name,
                                          CategoryName = category.Name
                                      };
    }
}

public class ItemCategoryViewModel
{
    public string Name { get; set; }
    public string CategoryName { get; set; }
}

You can use this Transformer with a Query on the Item entity:

using (var session = documentStore.OpenSession())
{
    var items = session.Query<Item>()
                        .TransformWith<ItemCategoryTransformer, ItemCategoryViewModel>()
                        .ToList();
}

As for the second list, still using your data structure, you have to use a couple of things. First, a Map/Reduce index over the Items, grouped by CategoryId:

public class Category_Items_Count : AbstractIndexCreationTask<Item, Category_Items_Count.Result>
{
    public class Result
    {
        public string CategoryId { get; set; }
        public int Count { get; set; }
    }

    public Category_Items_Count()
    {
        Map = items => from item in items

            select new Result
            {
                CategoryId = item.CategoryId,
                Count = 1
            };

        Reduce = results => from result in results
            group result by result.CategoryId
            into c
            select new Result
            {
                CategoryId = c.Key,
                Count = c.Sum(x => x.Count)
            };
    }
}

But as you only have the CategoryId on the Item entity, you have to use a similar transformer as in the first list:

public class CategoryItemsCountTransformer : AbstractTransformerCreationTask<Category_Items_Count.Result>
{
    public CategoryItemsCountTransformer()
    {
        TransformResults = results => from result in results
            let category = LoadDocument<Category>(result.CategoryId)
            select new CategoryItemsCountViewModel
            {
                CategoryName = category.Name,
                NumberOfItems = result.Count
            };
    }
}

public class CategoryItemsCountViewModel
{
    public string CategoryName { get; set; }
    public int NumberOfItems { get; set; }
}

And lastly, query for it like this:

using (var session = documentStore.OpenSession())
{
    var items = session.Query<Category_Items_Count.Result, Category_Items_Count>()
                       .TransformWith<CategoryItemsCountTransformer, CategoryItemsCountViewModel>()
                       .ToList();
}

As you can see, there are quite a difference in work needed depending on what data structure you're using. If you stored the Category Name on the Item entity directly you wouldn't need any Result Transformers to achieve the results you're after (you would only need a Map/Reduce index).

However, Result Transformers are executed server side and are only executed on request, instead of using LoadDocument inside of an index which is executed every time indexing occurs. Also, and maybe why LoadDocuments inside of index definitions isn't recommended, every change to a document that's referenced with a LoadDocument in an index will cause that index to have to be rewritten. This might lead to a lot of work for the index engine.

Lastly, to answer your last question about why you get an exception when querying: As the actual return type of your index is the document that that's being indexed (in this case Item). To use something else you need to project your result to something else. This can be done by using ".As()" in the query:

Item_WithCategory.Result[] all;
using (var session = DocumentStoreHolder.Store.OpenSession())
{
    all = session.Query<Item_WithCategory.Result, Item_WithCategory>()
        .As<Item_WithCategory.Result>()         
        .ToArray();
}

Long post, but hope it helps!