Find all elements in which the children's collection does not contain an element

Question

Given:

public class Order { public string Name {get;set;} public List<LineItem> LineItems {get; set;} } public class LineItem { public string Product {get; set;} public int Quantity {get; set;} } 

I'm trying to figure out how to build a query that will return all Orders that do n't have LineItem with a product called "Apple"

Answer 1

I have been thinking about this for a while. He appeared several times. The problem is that Raven does not currently handle .Any () or .All () requests.

This specific example has simplified the problem so much that I had to think about a different path. I believe there is a solution. It requires a lucene query against a static index:

 public class Orders_ByProduct : AbstractIndexCreationTask<Order> { public Orders_ByProduct() { Map = orders => from order in orders select new { Product = order.LineItems.Select(x => x.Product) }; } } var ordersWithoutApple = session.Advanced .LuceneQuery<Order, Orders_ByProduct>() .Where("*:* AND -Product: Apple") 

Answer 2

you can do this by creating an index for your query

 public class GetOrdersByProductIndex: AbstractIndexCreationTask<Order,GetOrdersByProductIndex.Result> { public class Result { public string Product {get; set;} } public GetOrdersByProductIndex() { Map = orders => from order in orders select new { Product = order.LineItems.Select(x => x.Product) }; } } 

Now you can use this index to receive orders. Your request should look like this:

  using(IDocumentSession session = docStore.OpenSession()) { var orders = session.Query<GetOrdersByProductIndex.Result,GetOrdersByProductIndex> .Where(x=>x.Product != "Apple") .As<Order>() .ToList() } 

Please note that by default it will only return 128 records (due to the restriction set by ravendb), if your query results contain more than 128 records, you should use the Take(recordsNeeded) function to retrieve the data.

Answer 3

We were able to get around RavenDB without support !.Any with an explicit comparison with false , for example:

 orders.Where(x => x.LineItems.Any(y => y.Product == "Apple") == false)