How to write the Cosmos DB query with a 'in' keyword using LINQ?

Question

I have this sample document in my azure cosmos db database.

{
    "partitionKey": "SonicTag",
    "label": "SonicTag",
    "name": "Kasi Tournaments",
    "slug": "kasi-tournaments",
    "a2AQuestionsCount": 0,
    "iconUrl": null,
    "id": "1af53736-c492-40bf-8ef7-dba2d17f6c17"
}

when I run the following query on the Data Explorer, I get exactly what I want (3 documents which has the specified IDs).

SELECT * FROM c where c.id in 
   ("dc4201ff-35b8-4eda-b0a6-f9bc2bb93926",
    "3389f2f2-33f3-4b08-8d32-4602071eae30",
    "1af53736-c492-40bf-8ef7-dba2d17f6c17")
and c.partitionKey = "SonicTag"

Just for example, on one my queries, where I'm looking for all the tags, I can use the following code.

    var feedIterator = Container.GetItemLinqQueryable<SonicTag>(true)
        .Where(c => c.PartitionKey == nameof(SonicTag)
                    && c.Label == nameof(SonicTag)).ToFeedIterator();

But I have no idea how can I write my LINQ to get al

Please note, on the method where I have to construct this LINQ, I receive a List of IDs and I must get all the Tags which have those Ids. The parameter is guaranteed that it will only contain a maximum of 5 IDs.

In the meantime, I using what is obviously a bad workaround of

var tags = new List<SonicTags>();
foreach (var id in IDs)
{
    tags.Add( Helper.ConvertStreamToObject( await Container.ReadItemStreamAsync(id, partitionKey)));
}

Robert Mrobo · Accepted Answer · 2020-05-04 01:43:20Z

2

I have found a solution by using the Contains method of List<T>. Below is my method.

public async Task<List<SonicTag>> GetTagsAsync(List<string> ids)
{
    var feedIterator = 
        Container.GetItemLinqQueryable<SonicTag>()
            .Where(x=> ids.Contains(x.Id)).ToFeedIterator();

    var tags = new List<SonicTag>();
    while (feedIterator.HasMoreResults)
    {
        var res = await feedIterator.ReadNextAsync();
        tags.AddRange(res.ToList());
    }

    return tags ;
}

answered May 4, 2020 at 1:43

Robert Mrobo

1381 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Ubezzi · Accepted Answer · 2020-05-01 23:05:57Z

-1

Please take a look at the following LINQ to SQL translation document which will help you construct the correct expression. An example:

Where Operator, example #2"

LINQ lambda expression

input.Where(
    family => family.parents[0].familyName == "Wakefield" &&
    family.children[0].grade < 3);

SQL

SELECT *
FROM Families f
WHERE f.parents[0].familyName = "Wakefield"
AND f.children[0].grade < 3

answered May 1, 2020 at 23:05

Mike Ubezzi

1,0026 silver badges9 bronze badges

2 Comments

Robert Mrobo Over a year ago

Thank you very much Mike for being the first responder... With all due respect, your answer doesn't answer my question, evidenced by the SQL your query produced. Before I posted this question, I went through that document over and over again but I couldn't find what I needed. I want to pass a List of IDs (string), and then query the DB to get all the documents with has those IDs.

Mike Ubezzi Over a year ago

I am glad you found a solution to your issue and for posting your solution, as it helps others facing a similar situation. My goal is to go through all the unanswered posts and offer something. My response should have been a comment. Thank you for the feedback.

Collectives™ on Stack Overflow

How to write the Cosmos DB query with a 'in' keyword using LINQ?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related