How to get related items using YesSql.ISession

[tiggertag]

I am very new to Orchard Core and Document database. I am using Orchard Core in Decoupled mode and I am using the concept from saving the Todo items from OchardCore.Demo (where a Content Type is not defined). How do I use YesSql.ISession to get the related documents? Let me explain what I am trying to do: I have 2 models, Issue and Issue Type, which are created without a Content Type definitions. Here are my models for Issue and IssueType:

public class Issue
{
public string IssueId { get; set; }
public string Subject { get; set; }
public DateTime Created { get; set; }
public String Description { get; set; }
public string IssueTypeId { get; set; }
... //other properties
}

public class IssueType
{
public string TypeId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
...//other properties
}

The property IssueTypeId in Issue model class references the TypeId in IssueType class. I have the need for IssueType to be independently accessible rather than embedding it. So, my questions is when I am getting the list of Issues, how do I get the corresponding IssueType for each issue in the list? Is there something similar to EntityFramework's Include method, which loads related data?

[Skrypt]

I know it is possible. I never had to use this method because I generally use CreateMapIndexTable in my Migrations.cs file create method. I would need to dig in the issues to remember how to achieve this. Though, you can use CreateForeignKey method to create the relationship between those tables. After that, I would need to see how you can use YesSQL to insert data in those tables.

[tiggertag]

Thank you @Skrypt! While you are digging into it, can I please ask for a big favor? I am finding it's very difficult to find the information regarding Orchard Core. Where should a newbie like me start to learn about Orchard Core and it's intricacies? Also, I didn't see any documentation on how to use CreateMapIndexTable or CreateForeignKey. Can you please give me a guidance on how do I use both of them for my usecase?

[Skrypt]

It is true that there is no documentation about YesSQL in Orchard Core. And I believe that the documentation should be done in YesSQL repository for that matter. Examples are spread throughout the entire Orchard Core Core modules. Mainly you will need to look at Migrations.cs files and see how we use them. The Workflow module is a good example of CreateMapIndexTable usage.

[tiggertag]

@Skrypt I added the Index and the IndexProvider for IssueType table but it is not creating records in the newly created Index table. I looked at the YesSql documentation, it says that IndexProvider class need to be registered like this:
store.RegisterIndexes< BlogPostIndexProvider>();

Where do I do this for Orchard Core? I don't see any such calls for Workflow module.

Here is my code:
public class TypeIdNameIndex : MapIndex
{
public int DocumentId { get; set; }
public string IssueTypeId { get; set; }
public string Name { get; set; }
}

public class TypeIdNameIndexProvider : IndexProvider< IssueType >
{
public override void Describe(DescribeContext< IssueType > context)
{
context.For< TypeIdNameIndex >().Map(type => new TypeIdNameIndex { Name = type.Name, IssueTypeId= type.IssueTypeId });
}
}

In module's migration, I added in UdateFromx():
SchemaBuilder.CreateMapIndexTable< TypeIdNameIndex >(table =>
table.Column< string>("IssueTypeId")
.Column< string>("Name")
);

SchemaBuilder.AlterIndexTable< TypeIdNameIndex>(table =>
table.CreateIndex("IDX_TypeIdNameIndex_DocumentId", "DocumentId", "IssueTypeId", "Name")
);

I am using MVC module, so in the controller, I am calling:

model = new IssueType();
IssueTypeId = viewModel.IssueTypeId;
model.Name = viewModel.Name;
model.Description = viewModel.Description;
...
_session.Save(model);

The Index table and table index were created fine, but no data is being added to the Index table when an IssueType is created. What am I doing wrong?

[tiggertag]

The Index table and table index were created fine, but no data is being added to the Index table when an IssueType is created. What am I doing wrong?

I figured it out, I was missing this in module's Startup.cs:

services.AddSingleton<IIndexProvider, TypeIdNameIndexProvider>();

[tiggertag]

I created Index table for Issue table, with IssueId and IssueTypeId, now how do I go about getting the related data (IssueType) along with Issue. Am I going about this in the right way? I worked with RDMS but not with Document based database.

[ns8482e]

Keep your document comprehensive as much as possible. If you need to store related documents as reference then store their reference key (id) in document

You can use MapIndex to optimize your queries as @Skrypt suggests

However Each referenced documents require to be loaded via separate query

[tiggertag]

My bad, I was not clear. What I meant is, in addition to having Id of the Type document saved in Issue document, I want to save Name value of the Type too saved in the Issue document, so that, I don't have to query the database twice, one for Issues and another for Types when I display current issues for the user. The display needs information from Issue document and the Name of the IssueType too. If I do that, is there a mechanism in YesSql or OrchardCore, that can help update all the Issue documents with new Type Name when Name value of Type document is changed?

[ns8482e]

I believe You need to update related documents by yourself if you keep name in both

[weirdyang]

@ns8482e sorry to trouble u, do you have any examples of what you described?

[ns8482e]

No worries, Look at the code example here

https://github.com/OrchardCMS/OrchardCore/blob/08bf6c315388a2406deafcfdb3bbc38870b286a3/src/OrchardCore.Modules/OrchardCore.Demo/Controllers/TodoController.cs#L23C12-L31C27

In above example, Lets assume if you have TodoType as an additional related property and you store TodoType records separately in db

you need to load related data by querying separately

await _session.Query<TodoType>().ListAsync()

[weirdyang]

thank you, appreciate it. I managed to figure out how to get the document id thanks to your previous comment here too