c# - RavenDB many to many and indexes -

need ravendb.

in web page want have such list:

• item1 category1
• item2 category2
• ...

and 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 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 => item in items               select new                   {                       name = item.name,                       categoryname = loaddocument<category>(item.categoryid).name                   };     } } 

is data structure suitable case, or better have category instead of categoryid in item structure?

should use index or there better solution take category name?

if index good, how write correct query? current try:

    item_withcategory.result[] all;     using (var session = documentstoreholder.store.opensession())     {         = session.query<item_withcategory.result, item_withcategory>().toarray();     } 

but throws exception stating return type item, not result. how fix it?

you have couple of options here. store both categoryid , categoryname on item entity. of course lead duplicated data (if still need store category entity), "storage cheap" popular term these days.the downside of need update each item document of given category if category name changes keep things consistent. benefit need less work desired result.

if store category name on item don't need special index handle first list, query on items , return need. second list need create map/reduce index on item entity groups on category.

however, if need use data structure you've given, there couple of ways of solving this. first, it's not recommended use loaddocument inside of index definition, not in select statement. might affect indexing performance in negative way.

instead, index properties need query on (or use auto index) , use result transformer fetch information related documents:

public class itemcategorytransformer : abstracttransformercreationtask<item> {     public itemcategorytransformer()     {         transformresults = results => 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 transformer query on item entity:

using (var session = documentstore.opensession()) {     var items = session.query<item>()                         .transformwith<itemcategorytransformer, itemcategoryviewmodel>()                         .tolist(); } 

as second list, still using data structure, have use couple of things. first, map/reduce index on items, grouped 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 => item in items              select new result             {                 categoryid = item.categoryid,                 count = 1             };          reduce = results => result in results             group result result.categoryid             c             select new result             {                 categoryid = c.key,                 count = c.sum(x => x.count)             };     } } 

but have categoryid on item entity, have use similar transformer in first list:

public class categoryitemscounttransformer : abstracttransformercreationtask<category_items_count.result> {     public categoryitemscounttransformer()     {         transformresults = results => 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 this:

using (var session = documentstore.opensession()) {     var items = session.query<category_items_count.result, category_items_count>()                        .transformwith<categoryitemscounttransformer, categoryitemscountviewmodel>()                        .tolist(); } 

as can see, there quite difference in work needed depending on data structure you're using. if stored category name on item entity directly wouldn't need result transformers achieve results you're after (you need map/reduce index).

however, result transformers executed server side , executed on request, instead of using loaddocument inside of index executed every time indexing occurs. also, , maybe why loaddocuments inside of index definitions isn't recommended, every change document that's referenced loaddocument in index cause index have rewritten. might lead lot of work index engine.

lastly, answer last question why exception when querying: actual return type of index document that's being indexed (in case item). use else need project result else. can done using ".as()" in query:

item_withcategory.result[] all; using (var session = documentstoreholder.store.opensession()) {     = session.query<item_withcategory.result, item_withcategory>()         .as<item_withcategory.result>()                  .toarray(); } 

long post, hope helps!