How to query items from nested collections in Raven DB?

Question

I have following 2 entity models:

      public class Store : IModel
      {
        public string Id { get; set; }
        public string Name { get; set; }
        public string MainPageUrl { get; set; }
        public ICollection<Product> Products { get; set; }

      }

      public class Product : IModel {
          public string Id { get; set; }
          public string Name { get; set; }
          public double Price { get; set; }
        public DateTime Created { get; set; }
}

and of these Store is a document in my Raven Db. I need to create an index where I can query products by Name and the result should be partial Store documents containing only matching products.

So to be specific I need to ask Raven Db this: What stores have products containing this text, and what are those products in each store.

Now I can make an index which gives me Store documents with matching products but it always gives me ALL the products in those documents.

I suppose this is a real easy one to answer but being new to Raven Db and document databases I just couldn't make this work.

There is an almost duplicate question here already but I still could not make the query/index work.

Answer 1

Mule, That is expected, a Store Document in your model contains all its products, and if you are asking for a Store Document, you'll get the full Store Document. If you want to just get a projection back for just the things that you want, you can use the following index:

from store in docs.Stores
from product in store.Products
select new { product.Name, product.Price, product.Created, store.Id }

Mark Name, Price, Created and Id as stored.

Then issue the following query.

session.Query<StoreProduct>()
  .Where(s=>s.Name == name)
  .AsProjection<StoreProduct>()
  .ToList();

That will give you only the matching products.