How to write query using c# Linq

[thomas-tran]

I got the following data in cosmos database. This is how I wrote the query using data explorer

select * from m where ARRAY_CONTAINS(m.tags,' sci-fi') or ARRAY_CONTAINS(m.tags,' action')

[
  {
    "title": "Starwars",
    "tags": [
      "sci-fi",
      "action"
    ],
    "year": 2019,
  },
  {
    "title": "Gone with the wind",
    "tags": [
      "classic",
      "romance"
    ],
    "year": 2019,
  },
  {
    "title": "Fast and Furious",
    "tags": [
      "action",
      "sitcom"
    ],
   "year": 2019,
  }
]

How can I write the query in C# using LINQ to translate get the list of movies where the tags property contains either 'action' or 'sci-fi'?

I tried the following but it did not work

public class Movie {
   public Movie() {
      tags = new List<string>();
   }
   public string title {get;set;}
   public List<string> tags {get;set;}
}

var  _container = dbClient.GetContainer(databaseName, containerName);
var queryable = _container.GetItemLinqQueryable<Movie>();
var tagList = new List<string> {"sci-fi", "action"};
queryable.Where(c => c.Tags.Any(t => tagList .Contains(t)));

Instead of return the movie Starwars and Fast and Furious, it returns all of the movies

[j82w]

@timsander1 can you please take a look?

[timsander1]

Hi @thomas-tran, could you share the LINQ query that this gets translated to? Here is an example you can use: https://github.com/Azure/azure-cosmos-dotnet-v3/blob/efade5cfb75fd4b2738d00abdca8da9014c7c34a/Microsoft.Azure.Cosmos/tests/Microsoft.Azure.Cosmos.EmulatorTests/CosmosItemLinqTests.cs#L407

Did ARRAY_CONTAINS (when used in the Portal) produce the query result you wanted?

[thomas-tran]

@timsander1

I am unable to get the query text whenever I used the Linq with Any(). It throws the exception as below

select * from m where ARRAY_CONTAINS(m.tags,' sci-fi') or ARRAY_CONTAINS(m.tags,' action') is producing the result what I want.

Basically, my input is the string array ['sci-fi', 'action'] and I want to get all the movies which contain those tags using or condition. My LINQ may not be correct so you can suggest the better one.

Thanks

[timsander1]

Were you able to get the query text when you use LINQ without Any()?

[thomas-tran]

No I am unable to get the query text, I tried to remove the filter and it still showing the same exception

I used this version of cosmos

[timsander1]

Could you assign your lLINQ query to queryable? Then grab the query string from that. In the first screenshot above, the result of the filter is never set back to the queryable.

Here is code snippet you can use:

IOrderedQueryable linqQueryable = this.Container.GetItemLinqQueryable(true); IQueryable queriable = linqQueryable.Where(item => item.CamelCase == "camelCase"); // assign back here string queryText = queriable.ToQueryDefinition().QueryText; //grab query text

[thomas-tran]

var _container = dbClient.GetContainer(databaseName, containerName); IOrderedQueryable linqQueryable = _container.GetItemLinqQueryable(); IQueryable queriable = linqQueryable.Where(c => c.Year == 2019); var tagList = new List {"sci-fi", "action"}; queriable .Where(c => c.Tags.Any(t => tagList .Contains(t)));

Here is the query text result, the part of the where clause for tags is missing

SELECT VALUE root FROM root WHERE (root["year"] = 2019)

[timsander1]

Your code looks like it is getting the query text from queriable on Line 3, not Line 5 (I'm assuming Line 5 is intended).

Can you try running this instead:

var tagList = new List {"sci-fi", "action"}; IOrderedQueryable linqQueryable = _container.GetItemLinqQueryable(true); IQueryable queriable = linqQueryable.Where(c => c.Year == 2019); queriable = queriable .Where(c => c.Tags.Any(t => tagList .Contains(t))); string queryText = queriable.ToQueryDefinition().QueryText; Console.WriteLine(queryText);

[thomas-tran]

@timsander1, It worked now.

Just curious why we need to assign back queriable = queriable .Where(c => c.Tags.Any(t => tagList .Contains(t)));

Thanks.

[timsander1]

Great, happy to hear that it worked. Without assigning the new value to queriable, the updated queriable value was never actually used.

[thomas-tran]

All goods. Thanks

[ghost]

Closing due to in-activity, pease feel free to re-open.