Querying index on nested properties returns no results 6.0.100 about ravendb HOT 15 OPEN

Hi,

Using your current index, you can change it:
Index(x => x.OwningContact, FieldIndexing.Search);

And query it like this:
from index 'OrganizationDtoIndex'
where OwningContact = '00000000-0000-0000-0000-000000000002'

Note that this query is going to search the entire OwningContact object which includes the Id, Name, AvatarUrl...
If the Name, for example, has the value that you are searching for, than equals to '00000000-0000-0000-0000-000000000002' than it will be returned.

I believe that what you are looking for is something like this:

from p in organizations
let owningContact = LoadDocument(p.OwningContact.Id)
select new OrganizationDto {
     FiscalYearEnd = p.FiscalYearEnd,
     AvatarUrl = p.AvatarUrl,
     Id = p.Id,
     Name = p.Name,
     LanguageCode = p.LanguageCode,
     OwningContactId = owningContact.Id,
OwningContactIName = owningContact.Name,
OwningContactAvatarUrl = owningContact.AvatarUrl,
OwningContactContactType = owningContact.ContactType
};

from index 'OrganizationDtoIndex'
where OwningContactId = '00000000-0000-0000-0000-000000000002'

from ravendb.

I would have done that, but the issue is that we have adhoc queries by customers that come from a structured DTO. We have to map the queries from the customer into the dataobject in the database so they have to roughly match or we have to complete change our DTO structure which we prefer to structure things like the OwningContact.

Hence why we need to be able to index the individual fields and have the mapper map the where clause from the DTO to the object in the database which will result in the right fields being generated in the where per the issue.

The index appears to be created properly from the definition in DB Studio, but doesn't query correctly. Is there not a way to index sub properties on an object property as I outlined?

I would have thought this was a bug because it both didn't error, created the index and the index is what I would have expected, generates the where query correctly from Linq and then returns no results as if the index data didn't generate for those fields despite being mapped.

from ravendb.

The problem is that this definition creates an object:

  OwningContact = new {
        Id = p.OwningContact.Id,
        Name = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, ""People"")).Name : (this.LoadDocument(p.OwningContact.Id, ""Organizations"")).Name,
        AvatarUrl = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, ""People"")).AvatarUrl : (this.LoadDocument(p.OwningContact.Id, ""Organizations"")).AvatarUrl,
        ContactType = p.OwningContact.ContactType
    }

When we see that, we are going to index that as its JSON content.
If you want to index individual values, you need to use those directly, we aren't going to try to index into your objects and generate dynamic fields.

Note that you can use:

   OwningContact_Id = owningContact.Id,
OwningContactI_Name = owningContact.Name,
OwningContact_AvatarUrl = owningContact.AvatarUrl,
OwningContact_ContactType = owningContact.ContactType

In which case the original query you used will work as expected:

from index 'PersonDtoIndex'
where OwningContact_Id = "08dc49e4-3043-2c2b-30d0-42ebaadf0000"

from ravendb.

So there's a catch 22 out of this that I would have thought would be fixed by creating the explicit indexs for the fields.

If I do it flat, then Linq won't do the mapping properly because I want The actual document back, and then mapped to the DTO. Thus if I flatten this, then it doesn't map.

If I keep it the way that I have it, the indexing doesn't work, because even though I explicitly defined the Indexes on the fields, it ignores it despite creating it, and despite the Linq translations directly referencing OwningContact_Id.

I MUST have the DTO as is.
I MUST be able to map to the actual document after indexing so I can do the projection, and thus Linq has to be p => p.OwningContact.Id == "1234".

The workflow is:

User filters a grid and that filter is on the DTO fields.
That filter is translated (using Automapper but doesn't have to be) from the DTO expression to the Document structure.
That queries against the index, which returns the Document.
Which is then Projected back into the DTO.

I.e: from session.Query<Person, PersonIndex>().Where(translatedFromPersonDtoProjection).ProjectTo()

But this can't work because the Index(x => x.OwningContact.Id ...) doesn't properly index just that field and map the above per exactly what I defined in the index.

The first suggestion can't work because I then have to take every single possible field and manually translate the DTO projection to the Document Where Projection.

The second suggestion won't work because the Linq Query won't map either because it thinks it's querying against a Person so that I can map it, because ProjectInto won't work with the DTO because it isn't a literal translation. (and it would add a ton of work creating new mappings to the index for everything)

This is a very common model which is why AutoMapper directly supports this with UseAsDataSource() (which doesn't work with RavenDb directly but can be used if you go to what it really does under the covers so that it isn't hard coded to EF, which is use the profiles and create an ExpressionVisitor.)

If there is another way where I don't have to run endless boilerplate code for mapping this stuff and have to know it ahead of time, I'm all ears, but I would expect the index definition above to work as is, because It's explicitly what I asked for, and generates exactly what I would expect, and the Linq even generates exactly the right query against the indexed field of OwningContact_Id that was created in the index.

The best possible solution I could see right now is to create a Profile for the index as well as all of the rest creating additional work, then mapping the DTO to the index for where etc, then ProjectInto() then .ProjectTo() creating a double projection. (if that even works) And even that adds a ton of extra work and overhead.

from ravendb.

Can we go back two steps? You showed your index code, but you haven't shown what the query code looks like.

Note that using _ by default as the separator in the index should ensure that your query code will just work.
Note that you can customize this (see: https://ravendb.net/docs/article-page/5.4/csharp/client-api/configuration/conventions#findpropertynameforindex) but the default is to use _ as the separator.

So a query as:

var q  = session.Query<Person, Index_PersonDtoIndex >()
   .Where(x=>x.OwningContact.Id == ownerId)
   .ToList();

Would work and translate the OwningContract.Id to OwningContract_Id

from ravendb.

That's exactly what I'm doing and it gives no results.

return session.Query<Person, PersonIndex>()
		.Where(p => p.OwningContact.Id == "08dc49e4-3043-2c2b-30d0-42ebaadf0000").ToArrayAsync();

if I do this:

return session.Query<Person>()
		.Where(p => p.OwningContact.Id == "08dc49e4-3043-2c2b-30d0-42ebaadf0000").ToArrayAsync();

It works fine.

Hence the issue.

from ravendb.

Did you modify the index as shown earlier?
Can you show a full failing code sample? https://ravendb.net/docs/article-page/5.3/csharp/start/test-driver

from ravendb.

I can't modify the index as shown earlier to use a flat structure. It breaks the business pattern.

As far as I can tell, no index on a sub property of a complex type in a document works. I'll build a sample when I can.
I have recreated the database and rebuilt the index.

I.e. with the following record:

{
    "UserName": "redacted",
    "NormalizedUserName": "redacted",
    "PasswordHash": "redacted",
    "TwoFactorEnabled": false,
    "CurrentOrganizationId": "00000000-0000-0000-0000-000000000001",
    "LockedOutOn": null,
    "DeactivatedOn": null,
    "SecurityStamp": "2BB2C020-7C18-497E-B02B-B1F09C344C11",
    "AccessFailedCount": 0,
    "LockoutEndOn": null,
    "TermsAcceptedOn": null,
    "ExternalLogins": [],
    "CanLogin": true,
    "FirstName": "James",
    "MiddleName": null,
    "LastName": "Hancock",
    "Name": "James Hancock",
    "ContactType": 1,
    "Type": "People",
    "Resumes": [],
    "IsDeleted": false,
    "AvatarUrl": null,
    "LanguageCode": "EN",
    "OwningContact": {
        "Id": "08dc4e59-bd2a-e84e-30d0-42eb04e10000",
        "ContactType": 1
    },
    "SocialMedia": [],
    "Messages": [],
    "Documents": [],
    "ContactMetadata": [],
    "ApiKeys": [],
    "@metadata": {
        "@collection": "People",
        "Raven-Clr-Type": "Data.RavenDb.Contacts.User, Data.RavenDb",
        "@flags": "HasRevisions"
    }
}

And the following PeopleDto from the index:

		{
			"firstName": "James",
			"middleName": null,
			"lastName": "Hancock",
			"name": "James Hancock",
			"avatarUrl": null,
			"languageCode": "EN",
			"owningContact": {
				"id": "08dc4e59-bd2a-e84e-30d0-42eb04e10000",
				"contactType": 1,
				"avatarUrl": null,
				"name": "James Hancock"
			},
			"id": "08dc4e59-bd2a-e84e-30d0-42eb04e10000"
		},

Using the following index:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Linq;
using Raven.Client.Documents.Indexes;

public class Index_PersonIndex : AbstractIndexCreationTask
{
    public override string IndexName => "PersonIndex";

    public override IndexDefinition CreateIndexDefinition()
    {
        return new IndexDefinition
        {
            Maps =
            {
            @"docs.People.Select(p => new {
    AvatarUrl = p.AvatarUrl,
    Id = Id(p),
    LastName = p.LastName,
    FirstName = p.FirstName,
    MiddleName = p.MiddleName,
    LanguageCode = p.LanguageCode,
    OwningContact = new {
        Id = p.OwningContact.Id,
        Name = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, ""People"")).Name : (this.LoadDocument(p.OwningContact.Id, ""Organizations"")).Name,
        AvatarUrl = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, ""People"")).AvatarUrl : (this.LoadDocument(p.OwningContact.Id, ""Organizations"")).AvatarUrl,
        ContactType = p.OwningContact.ContactType
    }
})"
            },
            Fields =
            {
                { "Id", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Exact } } ,
                { "FirstName", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Search } } ,
                { "LastName", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Search } } ,
                { "LanguageCode", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Exact } } ,
                { "AvatarUrl", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Search } } ,
                { "OwningContact_Id", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Exact } } ,
                { "OwningContact_ContactType", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Exact } } ,
                { "OwningContact_Name", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Search,
                    Storage = FieldStorage.Yes } } ,
                { "OwningContact_AvatarUrl", new IndexFieldOptions
                {
                    Indexing = FieldIndexing.Search,
                    Storage = FieldStorage.Yes } }
            }
        };
    }
}

The following query:

from index 'PersonIndex'
where OwningContact_Name = 'James Hancock'

Or even:

from index 'PersonIndex'
where OwningContact_Id = '08dc4e59-bd2a-e84e-30d0-42eb04e10000'

Returns Nothing but should obviously return the record I gave.

And using that same index if I do the following:

dbSession.Query<PersonDto, PersonIndex>().ToArray()

the OwningContact.Name field is empty in the results. Despite being told to store which from my reading of the documentation should mean that it should include it in projections. (and using AutoMapper to do it from dbSession.Query<Person, PersonIndex>().ProjectTo(_mapper.Configuration).ToArray() does work)

But I'm caught in a trap: I have filtering data based on a PersonDto, I have a security filter based on Person that knows nothing of the Dto, that needs to be applied to the query. But as soon as I do that, I have no way to map the filter from PersonDto to Person and get the fields that are in the index.

So I'm searching for a generic scenario where:

1. I have a filter, sort, and group by and aggregates against PersonDto.
2. I need to be able to filter, sort and group by fields that are not in Person but are in the index.
3. I need to further filter by security requirements to only return the results that the user has access to.
4. I need to return a PersonDto.

While this is the case for PersonDto it could happen on any collection that we put a grid on, so I don't want to have to special case this every single time.

I have code that creates a Expression<Func<PersonDto, true>>. I have AutoMapper which can make that into Expression<Func<Person, true>>, but I can't use that because it just drops the fields that are in PersonDto but not in Person. Person can't know about PersonDto AND I need to apply a query on Person and don't want to have to write the Security query for every Dto we have. (not the least of why is because people will forget to do one and cause security issues)

So it appears that there is a bug. And even without the bug, I still can't handle my needs. Suggestions welcome.

from ravendb.

I think the confusing part is that it's possible to query for nested properties on documents but not on projections from indexes.

var docByName = session
	.Query<Person>()
	.Where(p => p.Name == "Alice") // works
	.SingleOrDefault();
var docByAddress = session
	.Query<Person>()
	.Where(p => p.Address.PostCode == "1234") // works
	.SingleOrDefault();

var idxByName = session
	.Query<PersonDto, Index_PersonDtoIndex>()
	.ProjectInto<PersonDto>()
	.Where(p => p.Name == "Alice") // works
	.SingleOrDefault();
var idxByAddress = session
	.Query<PersonDto, Index_PersonDtoIndex>()
	.ProjectInto<PersonDto>()
	.Where(p => p.Address.PostCode == "1234") // does not work
	.SingleOrDefault();
var idxByOrgName = session
	.Query<PersonDto, Index_PersonDtoIndex>()
	.ProjectInto<PersonDto>()
	.Where(p => p.Organization.Name == "Umbrella") // does not work
	.SingleOrDefault();

Full code sample (using linqpad):

void Main()
{
	EmbeddedServer.Instance.StartServer();

	try
	{
		using var store = EmbeddedServer.Instance.GetDocumentStore("Embedded" + Guid.NewGuid());

		new Index_PersonDtoIndex().Execute(store);

		store.Initialize();
		using var session = store.OpenSession();

		var org = new Organization { Name = "Umbrella" };
		session.Store(org);
		session.Store(new Person
		{
			OrganizationId = org.Id,
			Name = "Alice",
			Address = new() { PostCode = "1234" }
		});
		session.SaveChanges();
		session.Advanced.WaitForIndexesAfterSaveChanges();

		var docByName = session
			.Query<Person>()
			.Where(p => p.Name == "Alice") // works
			.SingleOrDefault();
		var docByAddress = session
			.Query<Person>()
			.Where(p => p.Address.PostCode == "1234") // works
			.SingleOrDefault();

		var idxByName = session
			.Query<PersonDto, Index_PersonDtoIndex>()
			.ProjectInto<PersonDto>()
			.Where(p => p.Name == "Alice") // works
			.SingleOrDefault();
		var idxByAddress = session
			.Query<PersonDto, Index_PersonDtoIndex>()
			.ProjectInto<PersonDto>()
			.Where(p => p.Address.PostCode == "1234") // does not work
			.SingleOrDefault();
		var idxByOrgName = session
			.Query<PersonDto, Index_PersonDtoIndex>()
			.ProjectInto<PersonDto>()
			.Where(p => p.Organization.Name == "Umbrella") // does not work
			.SingleOrDefault();

		new
		{
			docByName,
			docByAddress,
			idxByName,
			idxByAddress,
			idxByOrgName,
		}.Dump();

		//EmbeddedServer.Instance.OpenStudioInBrowser();

	}
	finally
	{
		EmbeddedServer.Instance.Dispose();
	}
}

// You can define other methods, fields, classes and namespaces here

public class Index_PersonDtoIndex : AbstractIndexCreationTask<Person, PersonDto>
{
	public Index_PersonDtoIndex()
	{
		Map = persons =>
			from p in persons
			let org = LoadDocument<Organization>(p.OrganizationId)
			select new PersonDto
			{
				Id = p.Id,
				Organization = org,
				Address = p.Address,
				Name = p.Name,
			};

		StoreAllFields(FieldStorage.Yes);
		Index(p => p.Address.PostCode, FieldIndexing.Exact);
		Index(p => p.Organization.Name, FieldIndexing.Exact);
		Index(p => p.Organization.Id, FieldIndexing.Exact);
	}
}

public class PersonDto
{
	public string Id { get; set; }
	public string Name { get; set; }
	public Organization Organization { get; set; }
	public Address Address { get; set; }
}

public class Person
{
	public string Id { get; set; }
	public string Name { get; set; }
	public string OrganizationId { get; set; }
	public Address Address { get; set; }
}

public class Address
{
	public string PostCode { get; set; }
}

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

from ravendb.

So how does one do what would be a basic in EF Core here and allow users to find all Persons that have an OwningContact with a name of "James Hancock" that itself is a join from another table?

Also, it's not even returning on the field that isn't a projection: OwningContact.Id is in the Person object and the PersonDto object. (and as noted, it gets correctly converted to OwningContact_Id in the query that is generated)

This is just odata style stuff. The user has a DTO and they want to have a grid with filters, sorts, groups and aggregates and the server shouldn't be dumping the entire dataset, and it should also only return the original records that they have access to.

i.e. in this case:

from p in db.People.Where(securityProjection)
join c in db.People on p.OwningContact.Id equals c.Id
where <further conditions mapped from the DTO using AutoMapper because it can do a reverse and do the join lookup too>
order by <sort conditions mapped from the DTO using  AutoMapper>
select <DTO>

What's the equivalent? I'm fine with storing all of this stuff in the actual document, but then I have to update every document if the owning person ever changes their Avatar or Name. And because RavenDb no longer has triggers and only client events that can be called (and thus can't be assumed to be reliable especially when there are multiple instances of the client running), how do I model anything that works with every grid control out there from Telerik, DevExpress, and countless others?

I would have assumed that the way to get around this would be to create an index with the projection in it, index all of the fields that the user could ever filter on, and StoreAllField(StoreFields.Yes)... (making a potentially huge index...) but this still doesn't solve the security application issue that is on Person not PersonDto either AND doesn't work. This would have at least had the virtue of working like a trigger that forces an update on changed Person Documents to the index thus solving the name/avatarUrl field problem.

I can likely solve the Security issue with a shared interface that the security knows about and apply it that way, but I can't solve the rest.

from ravendb.

Let's start from the baseline, this will never work:

    OwningContact = new {
        Id = p.OwningContact.Id,
        Name = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, ""People"")).Name : (this.LoadDocument(p.OwningContact.Id, ""Organizations"")).Name,
        AvatarUrl = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, ""People"")).AvatarUrl : (this.LoadDocument(p.OwningContact.Id, ""Organizations"")).AvatarUrl,
        ContactType = p.OwningContact.ContactType
    }

You are trying to index an object. If you'll look at the terms, you'll see that what is indexed is the json contents of the object:

Your index should look like this:

docs.People.Select(p => new {
    AvatarUrl = p.AvatarUrl,
    Id = Id(p),
    LastName = p.LastName,
    FirstName = p.FirstName,
    MiddleName = p.MiddleName,
    LanguageCode = p.LanguageCode,
    OwningContact_Id = p.OwningContact.Id,
    OwningContact_Name = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, "People")).Name : (this.LoadDocument(p.OwningContact.Id, "Organizations")).Name,
    OwningContact_AvatarUrl = p.OwningContact.ContactType == 1 ? (this.LoadDocument(p.OwningContact.Id, "People")).AvatarUrl : (this.LoadDocument(p.OwningContact.Id, "Organizations")).AvatarUrl,
    OwningContact_ContactType = p.OwningContact.ContactType
})

With:

In which case the indexed entry will be:

from index 'People/Search'
 where search(OwningContact_Name,  'hancock')

Then you have the more complex issue of how you load the related data, here: dbSession.Query<PersonDto, PersonIndex>().ToArray()

You aren't actually doing a projection here, for that, you need to use:

dbSession.Query<PersonDto, PersonIndex>().SelectFields<PersonDto>().ToArray()

But note that you run into an issue with the complex objects in the middle in your own index.

Note that a query like this:

var docByAddress = session
	.Query<Person>()
	.Where(p => p.Address.PostCode == "1234") // works
	.SingleOrDefault();

Is translated in the exact same way inside of RavenDB into:

from index 'Auto/People/ByAddressPostCode' 
where Address_PostCode = '1234'

It's just that it is done for you automatically.

from ravendb.

First part:

So this looks a lot like hacking things to make it do what it should be doing automatically.

If I tell it to Index(x => x.OwningContact.Name, Search) it should do exactly the same thing as the hacking that you did to accomplish this.

This is exactly how you'd assume it would work logically. Especially since this doesn't error at index creation time and appears to do exactly that but then doesn't in reality. (I mean my index looks identical in Studio as what you screen shotted!)

Second part:

This doesn't really address the issue:

I have a projection on PersonDto.

I have a security filter on Person.

I need to (in order)

1. Apply the security filter to Persons.
2. Apply the PersonDto projection where which can include things that are in the index.
3. Apply Sorts, groups etc.
4. Return an array of PersonDtos.

I.e. the use case is so common that every mapping tool does this for you. In the case of AutoMapper it's UseAsDataSource().For() like this:

dbContext.People.Security(_user).UseAsDataSource().For<PersonDto>()
.Where(PersonDtoProjection)
.Orderby(PersonDtoSort)
.ProjectTo<PersonDto>()
.ToArray();

There appears to be no analog with RavenDb which creates a major and common use case issue.

The only thing I can think of is to change the Security filter to not be an IQueryable response method, but instead be an Expression<Func> response, which then can be used on any Person IQueryable and compiled for IEnumerable and then stand on my head to have AutoMapper mape the expression of Person to PersonDto and then apply that that to the index on PersonDto. Does that even make sense?

from ravendb.

You are operating under the assumption that RavenDB will index into a nested object. That isn't how it works, I'm afraid. If you are passing an object to RavenDB, it will be indexed as a single JSON object.

Your index does not look the same. You are looking at the field configuration, not the output of the index, (the map function). The index I provide flattens the values. This is because we cannot tell from the map definition what you are trying to do.

And there is no way to tell between: Foo = new { d.State, d.City} and Foo = d.Address - both generate the same output as far as RavenDB is concerned, a complex object that is indexed as it's json content.

I'm not sure that I follow the limitation on the query, to be honest. I would like to point you to this section of the book, which discuss the various models that apply during query time:

https://ravendb.net/learn/inside-ravendb-book/reader/4.0/12-working-with-indexes#working-with-complex-indexes-using-strongly-typed-code

In short, you have:

1. The documents to be indexed.
2. The index entry that was outputted from the index.
3. The actual queryable fields in the index.
4. The result of the query.

In general, you can do this:

session.Query<Person, PersonIndex>()
  .Where(p => /* security rules on the index content */) // equivalent to Security(user), basically
  .SelectFields<PersonDto>() // equivalent to ProjectTo

from ravendb.

Problem statement to resolve:

Create a complex DTO that requires information from many documents, hook up a Telerik or similar data grid, enable server side filtering, sorting and grouping, and make it so that, without custom code for every field, RavenDB can support it understanding that users rarely want data just from a single document and the more code you create, the more brittle your application. This can be with odata, graphql or anything else you desire to use that does this.

Attempts to solve:

1. Triggers that push/update/delete data in a aggregate document. Triggers don't exist.
2. Indexes that use LoadDocument to pull in fields and index them. Doesn't work.
3. Use Client Events to generate an aggregate document or parquet file with the same. Ugly, time consuming and huge amounts of code while also being brittle, and risky because of outbox and exactly once.

Commentary:

So I'm looking for a viable way to solve the problem. I'm not married to a solution other than said solution shouldn't require me to write hundreds of lines of code just to be able to map a DTO filter/sort/groupby to a root data source. (even in so much as it's even viable since I can't do joins, and I can't query on OwningContact.Name when it isn't in the document reference and I can't put the name in the document reference because I don't have triggers so I can't update the Name when the contact changes their name as a small example)

This use case is in effectively 100% of all applications, and from what you're telling me, the documentation, and the book, it isn't possible in current versions. (it used to be possible when there were triggers)

Suggestions welcome, but "your document structure is wrong" isn't a suggestion and while often that might be the case, it isn't the case in the real world when you have references to other documents in a document given that RavenDb has no way to update those references as a result of a CUD operation on the source document.

Improving this and making this easy and highly useful would go an enormous way to improving adoption of RavenDb and thus increasing sales. Your product is otherwise excellent, but this road block is what causes abandonment both myself and every single developer associate that has ever tried to use RavenDb for any sort of complex application in the real world. (including dozens in the FAANGSM)

Ideal Solution:

To me, the real opportunity for RavenDb is a form of report server built in that automatically generates fully indexed data (that has the structure of the DTOs) and allows the developer to create the map as a sort of index that really is instructing Raven's engine to watch for changes to documents in the donor tables and when those tables experience CUD, automatically either required with ACID or eventual depending on the setting in the "index" generates the DTO structured table.

Give us full text search automatically on all text fields, exact and ranges on all other fields (i.e. essentially column store on these), and Vector indexing on text fields as well. Optimize this for read only, big queries that handle the Telerik Grid/odata/graphql problem simply by completely eliminating the need for runtime mapping, and you'd have a massive win. Disk space is cheap, and I'm sure that Raven can create an awesome quasi-in-memory caching system that would make this insanely fast, and with sharding and clustering I'm sure that this would absolutely rock.

Incidentally, SurrealDB does effectively this, although still half baked.

from ravendb.

If you want to merge data from multiple documents into an aggregate document, we have Map/Reduce for that.
The Output Collection feature will also generate documents from those and RavenDB will ensure that they are kept up to date as the source document change:

https://ravendb.net/articles/ravendb-and-output-collection-pattern

Note that I'm being very explicit here about what is wrong. Your index structure.
You are attempting to index into a nested field, which requires you to flatten the fields in the index. Then the rest of the behaviors would follow as usual and the client code doesn't need to be aware of anything else.

Note that all the features that you are asking for in the Ideal Solution are already there.

Given that we seem to be talking past each other, I would like to ask if you can create a failing test that would showcase what you are trying to do.

See: https://ravendb.net/docs/article-page/5.3/csharp/start/test-driver

We provided some code samples above, but I think that we aren't clear enough on how to apply them for the scenario you have.

from ravendb.