'Joining' related data from a multi-map index

[LUS1N]

I will build on top of the cross-collection search example in the docs .

So let's say I wanted to be able to refer to any entity available in the cross-collection search index from MyThingEntity and then display those relationships to users.

enum ReferenceType 
{
    Company, Project, Employee
}

// types stored in the database
class Reference
{
      public Guid ReferenceId { get; set; }
      public ReferenceType Type { get; set; }
}


class MyThingEntity
{
   // < ... >
    public Reference Reference { get; set; }
}

// types returned to users
class ReferenceDto
{
      public Guid ReferenceId { get; set; }
      public ReferenceType Type { get; set; }
      public string Name { get; set; } // coming from Smart_Search.Projection.DisplayName
}

class MyThingDto
{
   // < ... >
    public ReferenceDto Reference { get; set; }
}

My goal is to return MyThing and join Smart_Search.Projection data onto it

I know I could do it in 2 database calls like this

var things = session.Query<MyThingEntity>().ToList();

var referenceIds = things.Select(x => x.ReferenceId).ToList();

var combinedDataSet = session
    .Query<Smart_Search.Result, Smart_Search>()
    .Where(x => x.Id.In(referenceIds))
    .ProjectInto<Smart_Search.Projection>()
    .ToList();

// then map to MyThingDto in memory 

but would it be possible to store that data in an index?

Furthermore, would it be possible to do the same if MyThingEntity had List<Reference> instead?

[ayende]

I'm not sure that I'm following. What does your index looks like?

You want to get just the employees from that search, for example?

[LUS1N]

I didn't make an index for MyThingEntity, that's what I want to find out - can I make an index for it that will allow me to pull related employee / company or product data that is contained in Smart_Search from the linked example.

class MyThingEntity
{
   // < ... >
    public Reference Reference { get; set; } // <- this hold a 'foreign key' to either employee | company | product
}

I want to read MyThingEntity and include the DisplayName of the item referenced by that foreign key, without caring what collection that item is in, since it's already aggregated in the Smart_Search index

does it make sense?

so to say it SQL terms, treat the Smart_Search index data as a table and join it on the MyThingEntity table

[ayende]

You are not providing enough information to answer your question.
And MyThingEntity isn't really helpful to understand context.

Can you provide:

• Existing collection
• What you want to achieve
• What you tried to do?

[LUS1N]

I made a test explaining what I want to achieve, I hope it makes more sense now

using FluentAssertions;
using Raven.Client.Documents;
using Raven.Client.Documents.Indexes;

namespace TestProject1;

public class Product
{
    public string Id { get; set; }
    public string Name { get; set; }
}

public class Employee
{
    public string Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public enum ReferenceType
{
    Product,
    Employee
}

public class Smart_Search : AbstractMultiMapIndexCreationTask<Smart_Search.Result>
{
    public class Result
    {
        public required string Id { get; set; }

        public required string DisplayName { get; set; }
        public ReferenceType ReferenceType { get; set; }
    }

    public Smart_Search()
    {
        AddMap<Product>(products => from p in products
            select new Result
            {
                Id = p.Id,
                DisplayName = p.Name,
                ReferenceType = ReferenceType.Product
            });

        AddMap<Employee>(employees => from e in employees
            select new Result
            {
                Id = e.Id,
                DisplayName = e.FirstName + " " + e.LastName,
                ReferenceType = ReferenceType.Employee
            });

        Store(x => x.Id, FieldStorage.Yes);
        Store(x => x.DisplayName, FieldStorage.Yes);
        Store(x => x.ReferenceType, FieldStorage.Yes);
    }
}

public class Reference
{
    public ReferenceType ReferenceType { get; set; }
    public required string Id { get; set; }
}

public class MyThing
{
    public string Id { get; set; }
    public string Name { get; set; }
    public Reference Reference { get; set; }
}

public class ReferenceDto
{
    public ReferenceType ReferenceType { get; set; }
    public string Id { get; set; }
    public string Name { get; set; }
}

public class MyThingDto
{
    public string Id { get; set; }
    public string Name { get; set; }
    public ReferenceDto Reference { get; set; }
}

[TestClass]
public class UnitTest1
{
    // this just sets up an in-memory ravendb
    private readonly IDocumentStore _store = new TestRavenDb().Store;

    public UnitTest1()
    {
        new Smart_Search().Execute(_store);
    }

    [TestMethod]
    public async Task TestMethod1()
    {
        // arrange 
        // so given the data defined here I want to get the result defined in the assert section
        var product = new Product()
        {
            Id = "products/1",
            Name = "Product 1"
        };

        var employee = new Employee()
        {
            Id = "employees/1",
            FirstName = "John",
            LastName = "Doe"
        };

        var thingWithEmployeeReference = new MyThing()
        {
            Id = "myThings/1",
            Name = "My Thing 1",
            Reference = new Reference()
            {
                Id = employee.Id,
                ReferenceType = ReferenceType.Employee
            }
        };

        var thingWithProductReference = new MyThing()
        {
            Id = "myThings/2",
            Name = "My Thing 2",
            Reference = new Reference()
            {
                Id = product.Id,
                ReferenceType = ReferenceType.Product
            }
        };

        using var session = _store.OpenAsyncSession();

        await session.StoreAsync(product);
        await session.StoreAsync(employee);
        await session.StoreAsync(thingWithEmployeeReference);
        await session.StoreAsync(thingWithProductReference);
        await session.SaveChangesAsync();

        // act
        // to get all the data I need - I have to make 2 database calls
        var projections = await session
            .Query<Smart_Search.Result, Smart_Search>()
            .ProjectInto<Smart_Search.Result>()
            .ToListAsync();

        var myThings = await session
            .Query<MyThing>()
            .ToListAsync();

        var result = myThings
            .Select(x =>
            {
                // then join the data in memory here
                var reference = projections.Find(p => p.Id == x.Reference.Id);
                return new MyThingDto()
                {
                    Id = x.Id,
                    Name = x.Name,
                    Reference = new ReferenceDto()
                    {
                        Id = reference.Id,
                        Name = reference.DisplayName,
                        ReferenceType = reference.ReferenceType
                    }
                };
            })
            .ToList();

        // I would like to make it somehow in a single database call
        // i.e. make the join on some index
        
        // assert
        // this is the result I want
        var expected = new[]
        {
            new MyThingDto()
            {
                Id = "myThings/1",
                Name = "My Thing 1",
                Reference = new ReferenceDto()
                {
                    Id = "employees/1",
                    Name = "John Doe",
                    ReferenceType = ReferenceType.Employee
                }
            },
            new MyThingDto()
            {
                Id = "myThings/2",
                Name = "My Thing 2",
                Reference = new ReferenceDto()
                {
                    Id = "products/1",
                    Name = "Product 1",
                    ReferenceType = ReferenceType.Product
                }
            }
        };

        result.Should().BeEquivalentTo(expected);
    }
}

[ayende]

The index you are using has nothing to do with this.
What you want to do is something like:

  var myThings = await session
            .Query<MyThing>()
            .Select(t => new {
                      t.Id,
                      t.Name,
                      Reference = new { t.Reference.Id, t.Reference.ReferenceType, DisplayName = session.Load<IReference>(t.Reference.Id).DisplayName }
            })
            .ToListAsync();

Basically, your issue is that you have DisplayName being different across collections, you can add a interface type that would manage that for you, and it will work.

[LUS1N]

Oh, nice, thank you. I didn't think about that.

So basically I would add IReference on Product and Employee and then load that IReference on MyThing_Index?

It would be nice to be able to do without touching the original entities and use the aggregated index, but if that's the only way then so be it :)

[LUS1N]

@ayende I realized that the model I'm working with is actually a bit more complex than that. Here's the updated code and the main reason why I want to 'join' on the index - the index holds other joined data

using FluentAssertions;
using Raven.Client.Documents;
using Raven.Client.Documents.Indexes;

namespace TestProject1;

public class Product
{
    public string Id { get; set; }
    public string Name { get; set; }
}

// to give some context in my app this is global data
public class System
{
    public string Id { get; set; }
    public string Name { get; set; }
}

// and this belongs to a specific tenant
public class SystemUsage
{
    public string Id { get; set; }
    public string SystemId { get; set; }
   // string tenantId
}

public enum ReferenceType
{
    Product,
    SystemUsage
}

public class Smart_Search : AbstractMultiMapIndexCreationTask<Smart_Search.Result>
{
    public class Result
    {
        public required string Id { get; set; }

        public required string DisplayName { get; set; }
        public ReferenceType ReferenceType { get; set; }
    }

    public Smart_Search()
    {
        AddMap<Product>(products => from p in products
            select new Result
            {
                Id = p.Id,
                DisplayName = p.Name,
                ReferenceType = ReferenceType.Product
            });

        AddMap<SystemUsage>(usages =>
            from u in usages
            let system = LoadDocument<System>(u.SystemId)
            select new Result
            {
                Id = u.Id,
                DisplayName = system.Name,
                ReferenceType = ReferenceType.SystemUsage
            });

        Store(x => x.Id, FieldStorage.Yes);
        Store(x => x.DisplayName, FieldStorage.Yes);
        Store(x => x.ReferenceType, FieldStorage.Yes);
    }
}

public class Reference
{
    public ReferenceType ReferenceType { get; set; }
    public required string Id { get; set; }
}

public class MyThing
{
    public string Id { get; set; }
    public string Name { get; set; }
    public Reference Reference { get; set; }
}

public class ReferenceDto
{
    public ReferenceType ReferenceType { get; set; }
    public string Id { get; set; }
    public string Name { get; set; }
}

public class MyThingDto
{
    public string Id { get; set; }
    public string Name { get; set; }
    public ReferenceDto Reference { get; set; }
}

[TestClass]
public class UnitTest1
{
    private readonly IDocumentStore _store = new TestRavenDb().Store;

    public UnitTest1()
    {
        new Smart_Search().Execute(_store);
    }

    [TestMethod]
    public async Task TestMethod1()
    {
        // arrange 
        // so given the data defined here I want to get the result defined in the assert section
        var product = new Product()
        {
            Id = "products/1",
            Name = "Product 1"
        };


        var system = new System()
        {
            Id = "systems/1",
            Name = "System 1"
        };

        var usage = new SystemUsage()
        {
            Id = "systemUsages/1",
            SystemId = system.Id
        };

        var thingWithSystemUsage = new MyThing()
        {
            Id = "myThings/1",
            Name = "My Thing 1",
            Reference = new Reference()
            {
                Id = usage.Id,
                ReferenceType = ReferenceType.SystemUsage
            }
        };

        var thingWithProductReference = new MyThing()
        {
            Id = "myThings/2",
            Name = "My Thing 2",
            Reference = new Reference()
            {
                Id = product.Id,
                ReferenceType = ReferenceType.Product
            }
        };

        using var session = _store.OpenAsyncSession();

        await session.StoreAsync(product);
        await session.StoreAsync(system);
        await session.StoreAsync(usage);
        await session.StoreAsync(thingWithSystemUsage);
        await session.StoreAsync(thingWithProductReference);
        await session.SaveChangesAsync();

        // act
        // to get all the data I need - I have to make 2 database calls
        var projections = await session
            .Query<Smart_Search.Result, Smart_Search>()
            .ProjectInto<Smart_Search.Result>()
            .ToListAsync();

        var myThings = await session
            .Query<MyThing>()
            .ToListAsync();

        var result = myThings
            .Select(x =>
            {
                // then join the data in memory here
                var reference = projections.Find(p => p.Id == x.Reference.Id);
                return new MyThingDto()
                {
                    Id = x.Id,
                    Name = x.Name,
                    Reference = new ReferenceDto()
                    {
                        Id = reference.Id,
                        Name = reference.DisplayName,
                        ReferenceType = reference.ReferenceType
                    }
                };
            })
            .ToList();


        // assert
        // this is the result I want
        var expected = new[]
        {
            new MyThingDto()
            {
                Id = "myThings/1",
                Name = "My Thing 1",
                Reference = new ReferenceDto()
                {
                    Id = "systemUsages/1",
                    Name = "System 1",
                    ReferenceType = ReferenceType.SystemUsage
                }
            },
            new MyThingDto()
            {
                Id = "myThings/2",
                Name = "My Thing 2",
                Reference = new ReferenceDto()
                {
                    Id = "products/1",
                    Name = "Product 1",
                    ReferenceType = ReferenceType.Product
                }
            }
        };

        result.Should().BeEquivalentTo(expected);
    }
}

[ayende]

What are you LoadDocument<System>(u.SystemId) ? Just to get the name, but you aren't querying on that, so you can just do that at query time, no?

Your issue is basically that for Product, you need the name directly from the document, but for SystemUsage, you need it from the assoicated document?

If you run this query, isn't that good enough?

from MyThings as m
load m.Reference.Id as a
select {
	Id: id(m),
	Name: m.Name,
	Reference: {
		Id: id(a),
		Type: a["@metadata"]["@collection"],
		DisplayName: a.Name || load(a.SystemId).Name
	}
}

[LUS1N]

I'm not querying on that in the example, but in my project I use full text search on that string. sorry for not specifying that. I was hoping I could also re-use the same index for this purpose as well, since the data is already stored.

The thing is that many different types can be this Reference (SystemUsage and Product in this example) and many different types can reference it, so not just MyThing.

Also it needs to be easy to extend and add new types. So if I write custom query logic like DisplayName: a.Name || load(a.SystemId).Name this in each place - I will also need to go an update all those places when new types are added.

Could this be done in an extension method or some different approach would be better?

[ayende]

The easiest way is to implement an interface with a read only property, that would be available for RavenDB to use on the server side then.

And in that case, it will also make it easy to operate on the index.
Note that if they belong to different collections, you'll need to do something like:

(IHasName)(LoadDocument<Product>(x.Reference.Id) ??LoadDocument<Company>(x.Reference.Id) ).Name