Querying: Intersection

Let's consider a case where we have a T-Shirt class:

public class TShirt
{
    public string Id { get; set; }

    public int ReleaseYear { get; set; }

    public string Manufacturer { get; set; }

    public List<TShirtType> Types { get; set; }
}

public class TShirtType
{
    public string Color { get; set; }

    public string Size { get; set; }
}

We will fill our database with few records:

session.Store(new TShirt
{
    Id = "tshirts/1",
    Manufacturer = "Raven",
    ReleaseYear = 2010,
    Types = new List<TShirtType>
            {
                new TShirtType {Color = "Blue", Size = "Small"},
                new TShirtType {Color = "Black", Size = "Small"},
                new TShirtType {Color = "Black", Size = "Medium"},
                new TShirtType {Color = "Gray", Size = "Large"}
            }
});

session.Store(new TShirt
{
    Id = "tshirts/2",
    Manufacturer = "Wolf",
    ReleaseYear = 2011,
    Types = new List<TShirtType>
            {
                new TShirtType { Color = "Blue",  Size = "Small" },
                new TShirtType { Color = "Black", Size = "Large" },
                new TShirtType { Color = "Gray",  Size = "Medium" }
            }
});

session.Store(new TShirt
{
    Id = "tshirts/3",
    Manufacturer = "Raven",
    ReleaseYear = 2011,
    Types = new List<TShirtType>
            {
                new TShirtType { Color = "Yellow",  Size = "Small" },
                new TShirtType { Color = "Gray",  Size = "Large" }
            }
});

session.Store(new TShirt
{
    Id = "tshirts/4",
    Manufacturer = "Raven",
    ReleaseYear = 2012,
    Types = new List<TShirtType>
            {
                new TShirtType { Color = "Blue",  Size = "Small" },
                new TShirtType { Color = "Gray",  Size = "Large" }
            }
});

Now we want to return all the T-shirts that are manufactured by Raven and contain both Small Blue and Large Gray types.

To do this, we need to do the following:

• add the Raven.Client.Documents namespace to usings
• use the Intersect query extension:

IList<TShirt> results = session.Query<TShirts_ByManufacturerColorSizeAndReleaseYear.Result, TShirts_ByManufacturerColorSizeAndReleaseYear>()
       .Where(x => x.Manufacturer == "Raven")
       .Intersect()
       .Where(x => x.Color == "Blue" && x.Size == "Small")
       .Intersect()
       .Where(x => x.Color == "Gray" && x.Size == "Large")
       .OfType<TShirt>()
       .ToList();

IList<TShirt> results = session
    .Advanced
    .DocumentQuery<TShirt, TShirts_ByManufacturerColorSizeAndReleaseYear>()
    .WhereEquals("Manufacturer", "Raven")
    .Intersect()
    .WhereEquals("Color", "Blue")
    .AndAlso()
    .WhereEquals("Size", "Small")
    .Intersect()
    .WhereEquals("Color", "Gray")
    .AndAlso()
    .WhereEquals("Size", "Large")
    .ToList();

public class TShirts_ByManufacturerColorSizeAndReleaseYear : AbstractIndexCreationTask<TShirt>
{
    public class Result
    {
        public string Manufacturer { get; set; }

        public string Color { get; set; }

        public string Size { get; set; }

        public int ReleaseYear { get; set; }
    }

    public TShirts_ByManufacturerColorSizeAndReleaseYear()
    {
        Map = tshirts => from tshirt in tshirts
                         from type in tshirt.Types
                         select new
                         {
                             Manufacturer = tshirt.Manufacturer,
                             Color = type.Color,
                             Size = type.Size,
                             ReleaseYear = tshirt.ReleaseYear
                         };
    }
}

from index 'TShirts/ByManufacturerColorSizeAndReleaseYear' 
where intersect(Manufacturer = 'Raven', Color = 'Blue' and Size = 'Small', Color = 'Gray' and Size = 'Large')

The above query will return tshirts/1 and tshirts/4 as a result.
The document tshirts/2 will not be included because it is not manufactured by Raven, and tshirts/3 is not available in Small Blue so it does not match all the sub-queries.