linqravendb

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


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.


Solution

  • 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"
     }