Find all items where child collection doesn't contain an item

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 construct a query that will return all the Orders that don't have a LineItem with a Product called "Apple"

Answer 1

I've been thinking about this for awhile. It's come up a few times. The problem is that Raven doesn't currently handle !.Any() or .All() queries.

This particular example simplified the problem enough that it got me thinking down 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

We were able to work around RavenDB's lack of support for !.Any with an explicit comparison to false, like:

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

Answer 3

you can do that 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 get your orders. Your query 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 return only 128 records(because of the limit set by ravendb) , if your query results have more than 128 records you should use Take(recordsNeeded) function for fetching the data.