RavenDb: Find movies that Actor X is NOT acting in

Question

I'm new to RavenDb and I've encountered the following problem, which is pretty easy to solve in SQL databases, but not so easy in RavenDb (it seems).

Given my classes:

//document collection
public class Movie
{
    public string Id { get; set; }
    public string Title { get; set; }
    public List<MovieActor> Actors { get; set; }
}

public class MovieActor
{
    public string ActorId { get; set; }
    public string CharacterName { get; set; }
    public DateTime FirstAppearance { get; set; }
}

//document collection
public class Actor
{
    public string Id { get; set; }
    public string Name { get; set; }
}

Finding every movie that Leonardo DiCaprio is acting in is very easy and efficient with the following Map index:

public class Movies_ByActor : AbstractIndexCreationTask<Movie>
{

    public Movies_ByActor()
    {
        Map = movies => from movie in movies
                        from actor in movie.Actors
                        select new
                        {
                            MovieId = movie.Id,
                            ActorId = actor.ActorId
                        };
    }
}

But this is not what I want to achieve, I want the opposite... to find all the movies where Leonardo DiCaprio is not acting.

I have also tried the following query:

 var leonardoActorId = "actor/1";
 var movies = from movie in RavenSession.Query<Movie>()
              where !movie.Actors.Any(a => a.ActorId.Equals(leonardoActorId))
              select movie;

But this will only give me an exception:

System.InvalidOperationException: Cannot process negated Any(), see RavenDB-732 http://issues.hibernatingrhinos.com/issue/RavenDB-732

Anyone know how to achieve this the proper way in RavenDb ?

Answer 1

Using the method described in my blog post here:

http://www.philliphaydon.com/2012/01/18/ravendb-searching-across-multiple-properties/

You can create an index with an array of ActorIds:

public class Movies_ByActor : AbstractIndexCreationTask<Movie>
{
    public Movies_ByActor()
    {
        Map = movies => from s in movies
                        select new
                        {
                            Actors = s.Actors.Select(x => x.ActorId)
                        };
    }

    public class ActorsInMovie
    {
        public object[] Actors { get; set; }
    }
}

Then you can search where the movie doesn't contain the actor you want:

var result = session.Query<Movies_ByActor.ActorsInMovie, Movies_ByActor>()
                    .Where(x => x.Actors != (object)"actors/1")
                    .As<Movie>();

Since the object we're querying against is different to the result, we need to specify As<T> to tell RavenDB what the type of the object actually returned is.

Working sample: http://pastie.org/7092908