Querying a child collection for multiple values ​​in RavenDB

Question

I am using RavenDB build 371 and I have the following model:

class Product { public string Id { get; set; } public ProductSpec[] Specs { get; set; } } class ProductSpec { public string Name { get; set; } public string Value { get; set; } } 

I would like to be able to request products that have a set of specifications. When asked for one specification:

 session.Query<Product>() .Where(product => product.Specs.Any(spec => spec.Name == "Color" && spec.Value == "Red")) .ToList(); 

Expected results are returned, however, when an additional spec predicate is added:

 session.Query<Product>() .Where(product => product.Specs.Any(spec => spec.Name == "Color" && spec.Value == "Red")) .Where(product => product.Specs.Any(spec => spec.Name == "Country" && spec.Value == "US")) .ToList(); 

the results are not returned, even if the results returned by the first query contain products with the specification "Country" and the value of the specification "USA". The same result is observed when using the LuceneQuery method. This seems like a problem this discussion , however, I could not implement the proposed solution. In particular, after creating the proposed index, I do not know how to request it.

How can I support this type of request in RavenDB?

EDIT

I still cannot request multiple values ​​in a set of composite types. Instead, I changed the model so that the spec / value combination is a concatenated string, so the specs collection is an array of strings. This can be requested by several values:

 class Product { public string Id { get; set; } public int CategoryId { get; set; } public string[] Specs { get; set; } } 

For reference, the source model and query work when using MongoDB with their multikeys . A very surprising problem with MongoDB is that the count () operation is slow for index queries . This type of request is important for pagination, and although the account can be cached, I would like to get a solution that provides this out of the box. In addition, another requirement that I have is the ability to aggregate specification groups for arbitrary product collections (for example, to get a collection of all spec / value combinations for products in a given category). In MongoDB, this can be achieved using their MapReduce functionality, however the results of the MapReduce operation are static and need to be updated manually when the source data changes, while RavenDB automatically updates MapReduce indexes in the background. Thus, while declaring MapReduce indices in RavenDB is more cumbersome than in MongoDB IMO, automatic background updating outweighs the flaws with a long shot. I will look at CouchDB , as their views are also updated automatically, although it seems that they are updated on demand, and not automatically in the background, I'm not sure if this will be a problem.

Answer 1

I changed the model a bit and was able to achieve the desired result using the Project method in AbstractIndexCreationTask. This is a (simplified) data model:

 public class Product { public string Id { get; set; } public int CategoryId { get; set; } public int TotalSold { get; set; } public Dictionary<string, string> Specs { get; set; } } 

This is the definition of the index:

 public class Products_ByCategoryIdAndSpecs_SortByTotalSold : AbstractIndexCreationTask<Product> { public Products_ByCategoryIdAndSpecs_SortByTotalSold() { this.Map = products => from product in products select new { product.CategoryId, _ = Project(product.Specs, spec => new Field("Spec_" + spec.Key, spec.Value, Field.Store.NO, Field.Index.ANALYZED)), product.TotalSold }; } } 

Then I can query like this:

  var results = session.Advanced.LuceneQuery<Product, Products_ByCategoryIdAndSpecs_SortByTotalSold>() .WhereEquals("CategoryId", 15920) .AndAlso().WhereEquals("Spec_Class", "3A") .AndAlso().WhereEquals("Spec_Finish", "Plain") .OrderBy("-TotalSold") .ToList(); 

This will return products in the "15920" category, which have a Class value of specification 3A and a Finish spec spec Plain, sorted in descending order by all units sold.

The key used the Project method, which basically creates fields in the Lucene document for each pair of specification name values.

Answer 2

I tried different things and could not get it to work. The specific request you are trying to execute is allowed for this Lucene RavenDB request (in version 426):

"{(Name: Color and meaning: Red) AND (Name: Country and value: USA)}", which explains why you are not getting the result.

After searching on this topic, I found this post: Lucene Query Syntax

The answers suggest various workarounds. Hope this helps. I am very curious though if this is really impossible.

Answer 3

According to build 717, you can do this using the new .Intersect() function created by Matt Warren. Have a look here: http://issues.hibernatingrhinos.com/issue/RavenDB-51