Linq statement instead of SQL with join and array

Question

Is there a way to formulate the following SQL statement for DocumentDB with Linq (for DocumentDB)?

SELECT docs
FROM docs
JOIN tags IN docs.tags
WHERE tags IN ("B", "C")

This is based on my question and it's answer from DocumentDB queries with arrays.

Answer 1

In my (very generic) repository I build dynamically some complex expressions for the WHERE predicates. Thus creating a SQL statement manually wasn't really an option.

So I ended up using a user defined function.

UDF defined at Collection with ID: CONTAINSANY

function containsAny (source, target) {
    if (source == null || target == null) return false;
    return target.some(function(item) { return source.indexOf(item) >= 0; } );
}

Clientside call:

var udfName = "CONTAINSANY";    
var tags = new[] { "B", "C" };
var query = client.CreateDocumentQuery(collLink)
     .Where(item => (bool)UserDefinedFunctionProvider.Invoke(udfName, item.tags, tags);

Answer 2

If you have a class like this

public class Doc
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }
    public string[] Tags{ get; set; }
}

You can do something like this:

private string EndpointUrl = "<your endpoint URI>";
private string AuthorizationKey = "<your key>";
private string database = "<DB Id>";
private string CollectionID= "<Collection Id>";

//prepare document db client
DocumentClient pClient = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey);

//prepare document db database
Database pDatabase = pClient.CreateDatabaseQuery().Where(db => db.Id == database).AsEnumerable().FirstOrDefault();

//prepare document db collection
DocumentCollection documentCollection= pClient.CreateDocumentCollectionQuery(pDatabase.SelfLink).Where(c => c.Id == CollectionID).ToArray().FirstOrDefault();

var tags = new[] { "B", "C" };
var families =  client.CreateDocumentQuery<Doc>(documentCollection.DocumentsLink)
               .SelectMany(doc=>doc.Tags.Where(t=> tags.Contains(t));

Right now I'm not really sure that Contains method is already supported by the Linq provider (I din't find any doc. about that), but if not, maybe you can finish your where condition this way:

.Where(t=>t=="B" || t=="C") 

I know this is not ideal but it should work. All these things are pretty new, we have to wait a little bit to see more documentation around there and more features that we can use.

Meanwhile, always there is the option of executing directly your query:

var items = client.CreateDocumentQuery<dynamic>(documentCollection.DocumentsLink,
    "SELECT docs" +
    "FROM docs" +
    "JOIN tags IN docs.tags" +
    "WHERE tags IN (\"B\", \"C\")");

You can find more info about how to creates queries in DocumentDb in this link

Answer 3

Ever since v 1.4.0 of the .NET SDK the following is now supported -

string[] ids = new string[] { "1", "2", "3", "4", "5"};
var query = client.CreateDocumentQuery(collLink).Where(d => ids.Contains(d.Id);