Find documents that DON'T have a string in a collection

Question

I have an entity like the following:

class Person
{
    public ICollection<string> OptOut { get; set; }
    //other fields
}

My application allows the user to create dynamic mailing lists based on a well-known set of fields in Person.

The customers should be able to opt out of the different mailing lists, so OptOut might contain something like [ "Marketing", "Financial" ].

The query for a specific mailing list currently aggregates the filter expressions (using Queryable.Where) and RavenDB creates the needed indexes without problems.

I have been reading, and it seems neither of these constructs are supported:

people.Where(x => !x.OptOut.Contains(mailingListType));
people.Where(x => !x.OptOut.Any(o => o == mailingListType));
people.Where(x => x.OptOut.All(o => o != mailingListType));

How can I create the right query?

Same principle: http://stackoverflow.com/questions/6914859/how-to-query-items-from-nested-collections-in-raven-db — Raciel R., Aug 26 '13 at 15:55
@RacielR. I can't see how to apply that here... In particular because this is a "NOT IN" query. — Diego Mijelshon, Aug 26 '13 at 16:03

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

You need to create an index for this type of query.

In the index you should flatten your OptOut collection so you can create queries on it.

More on this here:

How to query items from nested collections in Raven DB?

EDIT

It seems that this can be answered with a simple LuceneQuery w/o having to explicitly create and index.

var users = session.Advanced
                   .LuceneQuery<Person>()
                   .Where("OptOut:* AND -OptOut:" + newsLetterType)
                   .ToList();

EDIT 2

You will need to create this index:

from doc in docs.People
select new { OptOut = doc.OptOut.Count==0 ? "" : doc.OptOut}

To include person documents having no OptOut values.

It is frustrating how this is not available in the Query (typed client) but we can continue with that discussion on the mailing list.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 26 '13 at 16:01

Raciel R.

2,136
20
27

How can I create the query? It's easy to find out people that HAVE opted out. How do I search for people that HAVE NOT opted out? – Diego Mijelshon Aug 26 '13 at 16:55
Yes, you are right. Trying to get that query but it is tricky. – Raciel R. Aug 26 '13 at 17:06
Trying with dynamic fields: http://ravendb.net/docs/client-api/advanced/dynamic-fields – Raciel R. Aug 26 '13 at 17:22
While this doesn't use LINQ, it seems to be the closest we can get with the current RavenDB client. – Diego Mijelshon Aug 26 '13 at 20:45

Find documents that DON'T have a string in a collection

1 Answers1