documentdb join with linq

Question

I am trying to run the following query on DocumentDb with linq

SELECT p.id 
FROM p JOIN filter IN p.Filters
WHERE filter.Id = "686e4c9c-f1ab-40ce-8472-cc5d63597263" 
OR filter.Id = "caa2c2a0-cc5b-42e3-9943-dcda776bdc20"

My json is like this

 {
  "id": "a3dc570b-26e2-40a9-8777-683186965f78",
  "Filters": [
    {
      "Id": "686e4c9c-f1ab-40ce-8472-cc5d63597263"
    },
    {
      "Id": "caa2c2a0-cc5b-42e3-9943-dcda776bdc20"
    }
  ]
}

I found that I'm supposed to use SelectMany to do the join, but I can't get it to work, I've tried different things but always get runtime errors.

edit: Here's the query I've tried, this gives "Specified argument was out of the range of valid values.Parameter name: Unexpected Sql Scalar expression kind Conversion"

    public void TryOutQuery(IEnumerable<string> ids)
    {
        var options = new FeedOptions { MaxItemCount = 10, RequestContinuation = "" };
        var queryable = _client.CreateDocumentQuery<PostModel>(UriFactory.CreateCollectionUri(_collectionConn.DatabaseName, _collectionConn.CollectionName), options)                
            .SelectMany(p => p.Filters
            .Where(f => ids.Contains(f.Id))
            .Select(f => p)
            .Where(post => post.Status == PostStatus.Published)
            )
            ;
        var query = queryable.AsDocumentQuery();
        var asyn = query.ExecuteNextAsync<PostModel>();
        var result = asyn.Result; 
    }

I also tried in another order, which gives "Inconsistent types for query output type: Post.PostModel and Post.Filter"

    public void TryOutQuery(IEnumerable<string> ids)
    {
        var options = new FeedOptions { MaxItemCount = 10, RequestContinuation = "" };
        var queryable = _client.CreateDocumentQuery<PostModel>(UriFactory.CreateCollectionUri(_collectionConn.DatabaseName, _collectionConn.CollectionName), options)
            .Where(p => p.Status == PostStatus.Published)
            .SelectMany(p => p.Filters
            .Where(f => ids.Contains(f.Id))
            .Select(f => p)                
            )
            ;
        var query = queryable.AsDocumentQuery();
        var asyn = query.ExecuteNextAsync<PostModel>();
        var result = asyn.Result; 
    }

Here's my models

public class Filter
{
    public Filter(string id)
    {
        Id = id;
    }       
    public String Id { get; set; }
}

public class PostModel
{    
    public PostModel()
    {
        _filters = new List<Filter>();
    }
    [JsonProperty("id")]
    public String Id { get; set; }
    //Since DocumentDb doesn't work properly with IList
    public IEnumerable<Filter> Filters { get { return _filters; } }
    private IList<Filter> _filters;
    public PostStatus Status { get; set; }

    public void AddFilter(string filterId)
    {
        _filters.Add(new Filter(filterId));
    }

}

public enum PostStatus
{
    Draft,
    Published,
    Deleted
}

Answer 1

I'll use a query w/ two JOINs and a filter as an example:

// SQL
var familiesChildrenAndPets = client.CreateDocumentQuery<dynamic>(collectionLink,
        "SELECT f.id as family, c.FirstName AS child, p.GivenName AS pet " +
        "FROM Families f " +
        "JOIN c IN f.Children " +
        "JOIN p IN c.Pets " +
        "WHERE p.GivenName = 'Fluffy'");

// LINQ
var familiesChildrenAndPets = client.CreateDocumentQuery<Family>(collectionLink)
        .SelectMany(family => family.Children
        .SelectMany(child => child.Pets
        .Where(pet => pet.GivenName == "Fluffy")
        .Select(pet  => new
        {
            family = family.Id,
            child = child.FirstName,
            pet = pet.GivenName
        }
        )));

Check out this DocumentDB Github repo for more examples.