RavenDB LINQ query - matching index field to at least one value from a collection

Question

I have the following entity in my database:

public class Element
{
    public string Id { get; set; } = string.Empty;

    public IEnumerable<string> ExternalIds { get; set; } = Enumerable.Empty<string>();
    
    ...
}

I have the created the following fanout index in the database, so I can query for Element documents which have at least one matching externalId

public class Elements_ByExternalIds : AbstractIndexCreationTask<Element>
{
    public class Result
    {
        public string? ExternalId { get; set; }

        public string? ElementId { get; set; }
    }

    public Elements_ByExternalIds()
    {
        Map = accounts => from element in elements
                          from externalId in element.ExternalIds
                          select new Result
                          {
                              ExternalId = externalId,
                              ElementId = element.Id
                          };
    }
}

In the repository I want to perform a query, where I pass a list of externalIds and I need to return all Element documents which have in their own ExternalIds collection one of these externalIds, so inputExternalIds INTERSECT dbElementExternalIds IS NOT empty

I have written the query as following:

public async Task<Result<IEnumerable<Element>> GetElementsWhichContainExternalIds(IEnumerable<string> externalIds)
    {
        try
        {
            var elements = await _session
                .Query<Elements_ByExternalIds.Result, Elements_ByExternalIds>()
                .Where(element => externalIds.Contains(element.externalId)) // 1st attempt
                //.Where(element => externalIds.Any(externalId => externalId == element.ExternalId)) // 2nd attempt
                .Select(elements =>
                    new Elements_ByExternalIds.Result()
                    {
                        ExternalIds = elements.ExternalIds,
                        ElementId = elements.ElementId
                    })
                .ToListAsync(cancellationToken);


            return Result.Success(elements);
        }
        catch (Exception ex)
        {
            return Result.Failure<IEnumerable<Element>>($"Failed {ex.Message}");
        }
    }

I have tried two variations of the where clause, however both result in the same issue:

Could not understand expression

Question is, how to perform an intersect query on a RavenDB index, when one of the collections is an input coming from the application and the other one is in the RavenDB document? My approach was to create a fanout index, and then try to match the single value field from the index with the collection which is in closure.

Answer 1

A fanout index may be less recommended as it generates an index-entry for each ExternalID that you have in each and every Element document.

Use this approach instead:

Your element class:

public class Element
{
    public string Id { get; set; } = string.Empty;
    public IEnumerable<string> ExternalIds { get; set; } = Enumerable.Empty<string>();
}

Define this index:

public class Elements_ByExternalId :
    AbstractIndexCreationTask<Element, Elements_ByExternalId.IndexEntry>
{
    // The IndexEntry class defines the index-fields
    public class IndexEntry
    {            
        public string ExternalIds { get; set; }
    }
       
    public Elements_ByExternalId()
    {
        // The 'Map' function defines the content of the index-fields
        Map = elements => from e in elements
            select new IndexEntry
            {
                // Concatenate the list of ExternalIds to one string
                // and configure it for full-text-search below   
                ExternalIds = string.Join(" ", e.ExternalIds)
            };

        // Configure index-field ExternalIds for full-text-search.
        // The terms that will be generated for this index-field will be
        // the external ids. 
        // You can view it in the Studio... 
        Index(x => x.ExternalIds, FieldIndexing.Search);
    }
}

Deploy the index & Store some documents:

new Elements_ByExternalId().Execute(store);
            
using (var s = store.OpenSession())
{
  s.Store(new Element() { ExternalIds = new List<string>() { "E1" , "E2", "E3" } }, "Element1");
  s.Store(new Element() { ExternalIds = new List<string>() { "E4" , "E5", "E6" } }, "Element2");
  s.Store(new Element() { ExternalIds = new List<string>() { "E7" , "E8", "E9" } }, "Element3");
  s.Store(new Element() { ExternalIds = new List<string>() { "E1" , "E3", "E5" } }, "Element4");
  s.Store(new Element() { ExternalIds = new List<string>() { "E2" , "E4", "E6" } }, "Element5");
  s.Store(new Element() { ExternalIds = new List<string>() { "E11" , "E22", "E33" } }, "Element6");
  s.Store(new Element() { ExternalIds = new List<string>() { "E44" , "E55", "E66" } }, "Element7");
  s.SaveChanges();
 }
            
 Indexes.WaitForIndexing(store);

Query the index:

 IEnumerable<string> inputExternalIds = new List<string>() {"E1","E66"};
            
 using (var session = store.OpenSession())
 {                    
      List<Element> elementsWithMatchingExternalIDs = session
          .Query<Elements_ByExternalId.IndexEntry, Elements_ByExternalId>()
          .Search(x => x.ExternalIds, inputExternalIds)
          .OfType<Element>()
          .ToList();

      // Here, the resulting elementsWithMatchingExternalIDs 
      // will contain the following Element docs:
      // "Element4", "Element1", "Element7"
 }